Оптимизация запроса к 3-м таблицам без использования WHERE

[sickboy]

Оптимизация запроса к 3-м таблицам без использования WHERE

Таблицы:

mysql> desc moviez; (описание фильмов) 214 строк.

+----------------+----------------------+------+-----+---------+-------+
| Field          | Type                 | Null | Key | Default | Extra |
+----------------+----------------------+------+-----+---------+-------+
| id_moviez      | smallint(5) unsigned | NO   | PRI | NULL    |       | 
| orig_name      | varchar(255)         | NO   | MUL | NULL    |       |
...
+----------------+----------------------+------+-----+---------+-------+

mysql> desc moviez_dl_count; (в таблицу заносится id фильма, который скачали) 3112 строк.

+-----------+----------------------+------+-----+---------+----------------+
| Field     | Type                 | Null | Key | Default | Extra          |
+-----------+----------------------+------+-----+---------+----------------+
| id_mdl    | int(10) unsigned     | NO   | PRI | NULL    | auto_increment | 
| id_moviez | smallint(5) unsigned | NO   | MUL | NULL    |                | 
...
+-----------+----------------------+------+-----+---------+----------------+

mysql> desc moviez_rating; (в таблицу заносится id фильма и балл, поставленный пользователем) 6 строк.

+-----------+----------------------+------+-----+---------------------+----------------+
| Field     | Type                 | Null | Key | Default             | Extra          |
+-----------+----------------------+------+-----+---------------------+----------------+
| id_mr     | smallint(5) unsigned | NO   | PRI | NULL                | auto_increment | 
| id_moviez | smallint(5) unsigned | NO   | MUL | 0                   |                | 
| vote      | char(2)              | YES  |     | 0                   |                |
...
+-----------+----------------------+------+-----+---------------------+----------------+

mysql> desc moviez_partname; (таблица соответсвия id фильма (сериала) названию серии (части)) 116 строк.

+---------------+-----------------------+------+-----+---------+----------------+
| Field         | Type                  | Null | Key | Default | Extra          |
+---------------+-----------------------+------+-----+---------+----------------+
| id_mvfn       | mediumint(8) unsigned | NO   | PRI | NULL    | auto_increment | 
| id_mv         | smallint(5) unsigned  | NO   | MUL | NULL    |                | 
| partname_orig | varchar(255)          | NO   |     | NULL    |                | 
| partname_rus  | varchar(255)          | NO   |     | NULL    |                | 
| part          | tinyint(3) unsigned   | NO   |     | NULL    |                | 
+---------------+-----------------------+------+-----+---------+----------------+

Сам запрос (выводит список фильмов отсортированных по количеству скачиваний):
[SQL]SELECT
moviez.id_moviez,
...,
COUNT(DISTINCT(moviez_dl_count.id_mdl)) AS dlc
FROM
moviez
LEFT JOIN
moviez_dl_count
ON
moviez.id_moviez=moviez_dl_count.id_moviez
LEFT JOIN
moviez_rating
ON
moviez.id_moviez=moviez_rating.id_moviez
LEFT JOIN
moviez_partname
ON
moviez.id_moviez=moviez_partname.id_mv
GROUP BY
moviez_dl_count.id_moviez
ORDER BY
dlc DESC
LIMIT 50;[/SQL]

его EXPLAIN:

+----+-------------+-----------------+------+---------------+-----------+---------+------------------------+------+---------------------------------+
| id | select_type | table           | type | possible_keys | key       | key_len | ref                    | rows | Extra                           |
+----+-------------+-----------------+------+---------------+-----------+---------+------------------------+------+---------------------------------+
|  1 | SIMPLE      | moviez          | ALL  | NULL          | NULL      | NULL    | NULL                   |  214 | Using temporary; Using filesort | 
|  1 | SIMPLE      | moviez_dl_count | ref  | id_moviez     | id_moviez | 2       | w_pht.moviez.id_moviez |    7 |                                 | 
|  1 | SIMPLE      | moviez_rating   | ref  | id_moviez     | id_moviez | 2       | w_pht.moviez.id_moviez |    1 |                                 | 
|  1 | SIMPLE      | moviez_partname | ref  | id_mv         | id_mv     | 2       | w_pht.moviez.id_moviez |   19 |                                 | 
+----+-------------+-----------------+------+---------------+-----------+---------+------------------------+------+---------------------------------+

В итоге он исполняется 7 секунд.

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

 

[zerkms]

для начала добавь индексы на те поля, которые у тебя в ON в присоединямых таблицах

ps: чот чересчур как то долго выполняется - записей то всего ничего

-~{}~ 09.01.07 21:44:

и ещё - конечно же мануал, в раздел по оптимизации GROUP BY

 

[sickboy]

Индексы везде (в ON) есть и используются.
Смущает первая строка в EXPLAIN'e почему там не используется индекс, пока не соображу, именно из-за нее получается перебор 28462 строк.

 

[akd]

sickboy, а я правильно понимаю, при каждом скачивании фильма в таблицу moviez_dl_count заносится одна запись?

зачем тогда вообще нужна эта таблица? ведь можно update field = field + 1 ..

 

[sickboy]

akd
Да, правильно.

Спасибо за подсказу, сейчас переделаю запрос.

 

[chira]

тормозит потому, что декартовое произведение получается ...
убери GROUP BY и посмотри , что он группирует ...

 

[sickboy]

Сделал группировку по первичному ключу таблицы moviez, помогло.
Но в случае использования с ORDER BY опять получаю тормоза.

 

[Serg Karpenko]

особо не вчитывался, но что если 3 и 4 таблицы джойнить после группировки?

что- то вроде такого:
SELECT * FROM (
SELECT moviez.id_moviez, COUNT( DISTINCT (moviez_dl_count.id_mdl) ) AS dlc
FROM moviez
LEFT JOIN moviez_dl_count ON moviez.id_moviez = moviez_dl_count.id_moviez
GROUP BY moviez_dl_count.id_moviez
ORDER BY dlc DESC
LIMIT 50
) as t1
LEFT JOIN moviez_rating ON t1.id_moviez = moviez_rating.id_moviez
LEFT JOIN moviez_partname ON t1.id_moviez = moviez_partname.id_mv

 

[sickboy]

Serg Karpenko
работает быстро, но просмотренных строк очень много, и логика запроса немного нарушилась, но это поправимо.

пока использую совет akd.

Спасибо всем за ответы и участие.