Долгие SELECT rot_gallery_stats1

[burn]

День добрый.
Часто вижу запросы выполняющиеся больше 10 сек, могут и по 20 и по 60 сек выполняться. Когда есть нагрузка на сервере, они могут и уронить его.
Запросы вот такого типа:

Code: Select all

SELECT gs.gallery_id, gs.thumb_id, gi.sponsor_id, gi.content_count, gi.content_type, gs.total_shows, gs.total_clicks, gs.total_ctr, gt.thumb_url, gd.*, gi.content_count, gi.crop_profile_id, gs.activation_date, gi.added_date, gi.duration, gi.url, gi.gallery_total_shows, gi.sponsor_id, gi.source_url, gi.custom_gallery  FROM rot_gallery_stats1 as gs  FORCE INDEX (group_id)  STRAIGHT_JOIN rot_gallery_info as gi on gi.gallery_id = gs.gallery_id STRAIGHT_JOIN rot_gallery_data1 as gd on gd.gallery_id = gs.gallery_id  JOIN rot_gal2tag on rot_gal2tag.gallery_id = gs.gallery_id    JOIN rot_thumbs as gt on gt.thumb_id = gs.thumb_id                          WHERE 1 = 1  AND gi.gallery_status = 'active'  AND gi.gallery_type = 0  and gs.best_thumb = 'yes'  and gs.group_id = 0  AND rot_gal2tag.tag_id IN ('35551','35625','35724','35716','35564','35565','35749','35621','35843','35566','35588','35764','35671','35589','4755','35666','35695','35691','35692','35696' ) and rot_gal2tag.site_id = '1'  group by gs.gallery_id, gs.thumb_id  ORDER BY  tested_thumb DESC,  gs.total_ctr DESC  LIMIT 0, 51;

EXPLAIN

Code: Select all

mysql> EXPLAIN SELECT gs.gallery_id, gs.thumb_id, gi.sponsor_id, gi.content_count, gi.content_type, gs.total_shows, gs.total_clicks, gs.total_ctr, gt.thumb_url, gd.*, gi.content_count, gi.crop_profile_id, gs.activation_date, gi.added_date, gi.duration, gi.url, gi.gallery_total_shows, gi.sponsor_id, gi.source_url, gi.custom_gallery  FROM rot_gallery_stats1 as gs  FORCE INDEX (group_id)  STRAIGHT_JOIN rot_gallery_info as gi on gi.gallery_id = gs.gallery_id STRAIGHT_JOIN rot_gallery_data1 as gd on gd.gallery_id = gs.gallery_id  JOIN rot_gal2tag on rot_gal2tag.gallery_id = gs.gallery_id    JOIN rot_thumbs as gt on gt.thumb_id = gs.thumb_id                          WHERE 1 = 1  AND gi.gallery_status = 'active'  AND gi.gallery_type = 0  and gs.best_thumb = 'yes'  and gs.group_id = 0  AND rot_gal2tag.tag_id IN ('35551','35625','35724','35716','35564','35565','35749','35621','35843','35566','35588','35764','35671','35589','4755','35666','35695','35691','35692','35696' ) and rot_gal2tag.site_id = '1'  group by gs.gallery_id, gs.thumb_id  ORDER BY  tested_thumb DESC,  gs.total_ctr DESC  LIMIT 0, 51;
+----+-------------+-------------+--------+--------------------------------------------------------------------------------------------------------------+------------+---------+--------------------------------+--------+---------------------------------------------------------------------+
| id | select_type | table       | type   | possible_keys                                                                                                | key        | key_len | ref                            | rows   | Extra                                                               |
+----+-------------+-------------+--------+--------------------------------------------------------------------------------------------------------------+------------+---------+--------------------------------+--------+---------------------------------------------------------------------+
|  1 | SIMPLE      | gs          | ref    | unique_ids,best_thumb,gallery_id,thumb_id,activation_date,duration,test_priority,tested_thumb_index,group_id | group_id   | 3       | const,const                    | 225644 | Using index condition; Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | gt          | eq_ref | PRIMARY                                                                                                      | PRIMARY    | 4       | fuckclipssex_com.gs.thumb_id   |      1 | NULL                                                                |
|  1 | SIMPLE      | gi          | eq_ref | PRIMARY,sponsor_id,gallery_type,gallery_status                                                               | PRIMARY    | 4       | fuckclipssex_com.gs.gallery_id |      1 | Using where                                                         |
|  1 | SIMPLE      | gd          | eq_ref | PRIMARY                                                                                                      | PRIMARY    | 4       | fuckclipssex_com.gs.gallery_id |      1 | NULL                                                                |
|  1 | SIMPLE      | rot_gal2tag | ref    | tag_id,gallery_id                                                                                            | gallery_id | 4       | fuckclipssex_com.gs.gallery_id |      4 | Using where                                                         |
+----+-------------+-------------+--------+--------------------------------------------------------------------------------------------------------------+------------+---------+--------------------------------+--------+---------------------------------------------------------------------+
5 rows in set (0.01 sec)

Если добавить к условию например " and tested_thumb = 'no' and total_ctr > 0", то rows значительно сокращается

Code: Select all

mysql> EXPLAIN SELECT gs.gallery_id, gs.thumb_id, gi.sponsor_id, gi.content_count, gi.content_type, gs.total_shows, gs.total_clicks, gs.total_ctr, gt.thumb_url, gd.*, gi.content_count, gi.crop_profile_id, gs.activation_date, gi.added_date, gi.duration, gi.url, gi.gallery_total_shows, gi.sponsor_id, gi.source_url, gi.custom_gallery  FROM rot_gallery_stats1 as gs  FORCE INDEX (group_id)  STRAIGHT_JOIN rot_gallery_info as gi on gi.gallery_id = gs.gallery_id STRAIGHT_JOIN rot_gallery_data1 as gd on gd.gallery_id = gs.gallery_id  JOIN rot_gal2tag on rot_gal2tag.gallery_id = gs.gallery_id    JOIN rot_thumbs as gt on gt.thumb_id = gs.thumb_id                          WHERE 1 = 1  AND gi.gallery_status = 'active'  AND gi.gallery_type = 0  and gs.best_thumb = 'yes'  and gs.group_id = 0 and tested_thumb = 'no' and total_ctr > 0 AND rot_gal2tag.tag_id IN ('35551','35625','35724','35716','35564','35565','35749','35621','35843','35566','35588','35764','35671','35589','4755','35666','35695','35691','35692','35696' ) and rot_gal2tag.site_id = '1' group by gs.gallery_id, gs.thumb_id  ORDER BY  tested_thumb DESC,  gs.total_ctr DESC  LIMIT 0, 51;
+----+-------------+-------------+--------+--------------------------------------------------------------------------------------------------------------+------------+---------+--------------------------------+------+--------------------------------------------------------+
| id | select_type | table       | type   | possible_keys                                                                                                | key        | key_len | ref                            | rows | Extra                                                  |
+----+-------------+-------------+--------+--------------------------------------------------------------------------------------------------------------+------------+---------+--------------------------------+------+--------------------------------------------------------+
|  1 | SIMPLE      | gs          | range  | unique_ids,best_thumb,gallery_id,thumb_id,activation_date,duration,test_priority,tested_thumb_index,group_id | group_id   | 8       | NULL                           |    3 | Using index condition; Using temporary; Using filesort |
|  1 | SIMPLE      | gi          | eq_ref | PRIMARY,sponsor_id,gallery_type,gallery_status                                                               | PRIMARY    | 4       | fuckclipssex_com.gs.gallery_id |    1 | Using where                                            |
|  1 | SIMPLE      | gt          | eq_ref | PRIMARY                                                                                                      | PRIMARY    | 4       | fuckclipssex_com.gs.thumb_id   |    1 | NULL                                                   |
|  1 | SIMPLE      | gd          | eq_ref | PRIMARY                                                                                                      | PRIMARY    | 4       | fuckclipssex_com.gs.gallery_id |    1 | NULL                                                   |
|  1 | SIMPLE      | rot_gal2tag | ref    | tag_id,gallery_id                                                                                            | gallery_id | 4       | fuckclipssex_com.gs.gallery_id |    4 | Using where                                            |
+----+-------------+-------------+--------+--------------------------------------------------------------------------------------------------------------+------------+---------+--------------------------------+------+--------------------------------------------------------+
5 rows in set (0.01 sec)

[admin]

если добавить and gallery_id = 1 то вообще быстро будет )

" total_ctr > 0", то rows значительно сокращается " например уберет все тумбы без цтр и на старте там вообще ничего не будет

в чем смысл этого добавления?

[burn]

С "and gallery_id = 1" поиск будет по индексу gallery_id и конечно быстро будет.
Тут же как вижу проблема с индексом group_id, в него входят поля: group_id, best_thumb, tested_thumb и total_ctr, но последних двух нет в условии поиска, из-за чего по индексу он найти не может. А если добавим эти поля, то поиск идет по индексу, и вместо 225к строк проходит только 3

[admin]

эм.. а если нам надо с любым цтр в этом запросе то что делать ?
я просто пытаюсь понять логику
у нас задача выбрать то что надо или сделать запрос быстрым?

[admin]

те может надо индекс другой или запрос иначе а не условиями изменить запрос?

[burn]

Тут Вам, как разработчику данного софта, виднее как лучше поступить.
Я только показал что индекс тут не работает и надо бы это поправить.

[admin]

как вы понимаете я думаю (учитывая потому что знаете как сделать explain) сделать конкртеный универсальный индекс под все - нереально (или я не знаю как)
поэтому дефолтные индксы делаются под примерно средний сайт
у вас идет выборка из, полагаю, огромного большого кол-ва тагов где ищется совпадение по тагам
вероятно под именно ваш вариант запросов надо как-то поменять индексы либо может быть вы знаете как поменять этот запрос что б получить тот же результат
в обоих случаях - буду благодарен за совет
не тестируя на конкретной базе я не могу сказать как лучше это поменять