Форум пользователей MySQL

warl555 · 30.08.2009 22:55:19

Добрый день.

Дана следующая таблица:

CREATE TABLE IF NOT EXISTS `t_video` (

  `N_ID` int(30) NOT NULL auto_increment,

  `N_STATUS` smallint(3) NOT NULL default '2',

  `ID_USER` int(11) NOT NULL,

  `S_TITLE` varchar(20) NOT NULL,

  `DATE_ADD` datetime default '0000-00-00 00:00:00',

  `FILE_NAME` varchar(50) default NULL,

  `N_LENGTH` int(11) NOT NULL,

  `N_SIZE` int(11) NOT NULL,

  `S_DESC` varchar(255) NOT NULL,

  `N_ID_MAIN_SCREEN` int(30) default '0',

  `COUNT_COMM` int(30) default '0',

  `GLOB_RATING` int(30) default '0',

  `TOTAL_VIEWS` int(30) default '0',

  PRIMARY KEY  (`N_ID`),

  KEY `ID_USER` (`ID_USER`),

  KEY `DATE_ADD` (`DATE_ADD`),

  KEY `GLOB_RATING` (`GLOB_RATING`),

  KEY `N_ID_MAIN_SCREEN` (`N_ID_MAIN_SCREEN`)

) ENGINE=MyISAM  DEFAULT CHARSET=cp1251 AUTO_INCREMENT=99728 ;

Из название полей, думаю, будет понятны их роли.

Есть запрос вида:

SELECT t1.N_ID, t1.S_DESC, t1.ID_USER, t1.COUNT_COMM, t1.N_ID_MAIN_SCREEN, t1.N_LENGTH, t1.N_SIZE, t1.GLOB_RATING,

                                        UNIX_TIMESTAMP(t1.date_add) TIMEADD, (to_days(now())-to_days(t1.date_add)) DIVDATE

                                    FROM t_video t1

 WHERE N_STATUS = 1

                                ORDER BY N_ID DESC LIMIT 99999, 10

Исходная таблица содержит порядка 100-200тыс. записей. Запрос выполняется порядка 2+ секунд.
Как можно оптимизировать?
Из-за ордера+лимит сервер просто загибается.

P.S. Ордер еще происходит по полю GLOB_RATING

vasya · 31.08.2009 01:45:51

На вскидку. У вас не используются индексы. В этом вы можете убедиться посмотрев explain вашего запроса. Для исправления этой ситуации нужно добавить составной индекс KEY(N_STATUS,N_ID).

Рекомендую прочитать тему http://sqlinfo.ru/forum/viewtopic.php?id=151

warl555 · 31.08.2009 10:07:01

vasya написал:
На вскидку. У вас не используются индексы. В этом вы можете убедиться посмотрев explain вашего запроса. Для исправления этой ситуации нужно добавить составной индекс KEY(N_STATUS,N_ID).

Рекомендую прочитать тему http://sqlinfo.ru/forum/viewtopic.php?id=151

Как показывала практика, идекс KEY(N_STATUS,N_ID) не сильно помогал.
Для этого счас разбиение на 2 таблицы идет, t_video (статусы все кроме = 1) и t_video_activ (статус = 1)
Но сейчас из-за этого разбиения возникается ряд проблем, как миниумм с N_ID.

Есть какие то еще идеи на этот счет?

vasya · 31.08.2009 10:52:19

Не нравится конструкция LIMIT 99999, 10
В этом случае даже при наличии индекса MySQL придется прочитать 100009 строк и потом откинуть ненужные. Единственный способ избежать этого - переписать запрос, изменив условие выборки, чтобы сразу перейти к нужной записи, т.е. WHERE N_STATUS = 1 AND N_ID < 99999 ORDER BY N_ID DESC LIMIT 10

warl555 · 31.08.2009 11:42:30

Да! Хорошая идея, спасибо!

Еще есть идея разбить на партиции эту таблицу, как лучше всего разбить (и есть ли смысл?) ? по статусам? Там записей с id_status=1 будет на много больше чем с другими статусами и выборка по ним будет на много чаще.

warl555 · 01.09.2009 11:07:58

Идей, как я понимаю нет ни у кого? :-)

paulus · 01.09.2009 12:48:55

Partitioning имеет смысл делать тогда, когда Вы ожидаете, что будет происходить prunung
на каких-то запросах (ну или для увеличения скорости вставки в таблицу). В Вашем случае
с неимоверным LIMIT, это никак не скажется на производительности, единственное, что
окажется полезным, — научиться создавать партицированные таблички

warl555 · 01.09.2009 21:41:58

Большое спасибо за ответы, буду эксперементировать

warl555 · 06.09.2009 09:16:00

Еще вопрос по оптимизации одной выборки:
Нужно выбрать 10 видео-файлов отсортированных по рейтингу (GLOB_RATING) с заданной категорией (+ у которых статус = 1).

Для этого существует описанная в первом посте таблица t_video и таблица t_video_cat:

CREATE TABLE IF NOT EXISTS `t_video_cat` (

  `ID_VIDEO` int(30) NOT NULL,

  `ID_CAT` int(11) NOT NULL,

  PRIMARY KEY (`ID_VIDEO`,`ID_CAT`),

  KEY `vcat_idx` (`ID_CAT`),

  KEY `ID_VIDEO` (`ID_VIDEO`)

) ENGINE=MyISAM DEFAULT CHARSET=cp1251;

Для решения задачи делаю запрос примерно такого вида:

SELECT *

                             FROM t_video_cat t3

                             LEFT JOIN t_video t1 on t3.ID_VIDEO = t1.N_ID

                         WHERE t3.ID_CAT = 2 AND N_STATUS = 1 

                   ORDER BY t1.GLOB_RATING DESC LIMIT 10

В среднем запрос выполняется порядка 1.4секунды.

Как можно оптимизировать запрос?

Отредактированно warl555 (06.09.2009 09:17:29)

paulus · 06.09.2009 16:25:40

А зачем Вам тут левое объединение? Кажется, обычное тут по логике лучше.

Ну и explain покажите

warl555 · 06.09.2009 19:24:42

paulus написал:
А зачем Вам тут левое объединение? Кажется, обычное тут по логике лучше.

Ну и explain покажите

Да, тут, действительно left join не нужен:

SELECT *

                             FROM t_video_cat t3

                             JOIN t_video t1 ON t3.ID_VIDEO = t1.N_ID

                         WHERE t3.ID_CAT = 2 AND N_STATUS = 1 

                   ORDER BY t1.GLOB_RATING DESC LIMIT 10

explain:

 id     select_type     table     type     possible_keys     key     key_len     ref     rows     Extra 

1    SIMPLE    t3    ref    PRIMARY,vcat_idx,ID_VIDEO    vcat_idx    4    const    25125    Using temporary; Using filesort

1    SIMPLE    t1    eq_ref    PRIMARY,N_STATUS,N_STATUS_2,N_STATUS_3,N_ID    PRIMARY    6    video.t3.ID_VIDEO,const    1    

paulus · 06.09.2009 21:42:10

По логике все делается правильно, единственное, я бы сортировал не все данные, а только
id, потом ограничивал уже количество строк и вытаскивал данные:

SELECT *
FROM t_video_cat
JOIN (SELECT N_ID FROM ... ) tt USING N_ID

Если Вы уверены, что нужные строки будут вверху t1, то можно попробовать указать MySQL
в явном виде, как искать и как отсеивать данные (но если не уверены — можете сделать
медленнее):

SELECT STRAIGHT_JOIN *
FROM t_video t1 JOIN t_video_cat t3 ON ..

(другой порядок, чтобы искало по t_video и выбирало сразу в нужном порядке, но, возможно,
выбирало лишние данные, которые потом будут выброшены).

Форум пользователей MySQL

#1 30.08.2009 22:55:19

Оптимизация выборки, проект видео-хостинга

#2 31.08.2009 01:45:51

Re: Оптимизация выборки, проект видео-хостинга

#3 31.08.2009 10:07:01

Re: Оптимизация выборки, проект видео-хостинга

vasya написал:

#4 31.08.2009 10:52:19

Re: Оптимизация выборки, проект видео-хостинга

#5 31.08.2009 11:42:30

Re: Оптимизация выборки, проект видео-хостинга

#6 01.09.2009 11:07:58

Re: Оптимизация выборки, проект видео-хостинга

#7 01.09.2009 12:48:55

Re: Оптимизация выборки, проект видео-хостинга

#8 01.09.2009 21:41:58

Re: Оптимизация выборки, проект видео-хостинга

#9 06.09.2009 09:16:00

Re: Оптимизация выборки, проект видео-хостинга

#10 06.09.2009 16:25:40

Re: Оптимизация выборки, проект видео-хостинга

#11 06.09.2009 19:24:42

Re: Оптимизация выборки, проект видео-хостинга

paulus написал:

#12 06.09.2009 21:42:10

Re: Оптимизация выборки, проект видео-хостинга

Board footer