Медленный запрос при сортировке по популярности

[anton.sivanov2]

Code: Select all

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 
JOIN rot_gallery_info AS gi ON gi.gallery_id = gs.gallery_id
JOIN rot_gallery_data1 AS gd ON gd.gallery_id = gs.gallery_id
JOIN rot_thumbs AS gt ON gt.thumb_id = gs.thumb_id
WHERE  1 = 1
       AND gs.group_id = 0
       AND gs.tested_thumb = 'no'
       AND gi.gallery_status = 'active'
       AND gi.gallery_type = 0
       AND gs.gallery_id NOT IN ( '140', '49682', '501', '6313',
                                  '1675', '35085', '91973', '7000',
                                  '1479', '5595', '2953', '9965',
                                  '57759', '2669', '203', '46149',
                                  '8416', '3485', '28556', '62385',
                                  '1772', '5967', '5172', '63600',
                                  '12926', '65656', '15395', '21302',
                                  '1326', '10011', '148999', '429',
                                  '65775', '1588', '65832', '148353',
                                  '124728', '6073', '25697', '10283',
                                  '24132', '29185', '170889', '66095',
                                  '52826', '1490', '21393', '69353',
                                  '7825', '86', '154211', '1744',
                                  '1331', '74', '123354', '124435',
                                  '15162', '45136', '194202', '81492' )
ORDER  BY gs.total_ctr DESC
LIMIT  0, 300

+----+-------------+-------+------------+--------+--------------------------------------------------------------------------------------------+------------+---------+------------------------------+--------+----------+----------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+--------+--------------------------------------------------------------------------------------------+------------+---------+------------------------------+--------+----------+----------------------------------------------+
| 1 | SIMPLE | gd | NULL | ALL | PRIMARY | NULL | NULL | NULL | 652930 | 99.99 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | gi | NULL | eq_ref | PRIMARY,sponsor_id,gallery_status | PRIMARY | 4 | xnxxwank.gd.gallery_id | 1 | 10.00 | Using where |
| 1 | SIMPLE | gs | NULL | ref | unique_ids,best_thumb,gallery_id,thumb_id,tested_thumb_index,group_id,group_activationdate | unique_ids | 6 | xnxxwank.gd.gallery_id,const | 1 | 50.00 | Using where |
| 1 | SIMPLE | gt | NULL | eq_ref | PRIMARY | PRIMARY | 4 | xnxxwank.gs.thumb_id | 1 | 100.00 | NULL |
+----+-------------+-------+------------+--------+--------------------------------------------------------------------------------------------+------------+---------+------------------------------+--------+----------+----------------------------------------------+

запрос выше работает около 20 секунд

работает быстрее (0,01) при:

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 [b]USE INDEX (`tested_thumb_index`)[/b]
JOIN rot_gallery_info AS gi ON gi.gallery_id = gs.gallery_id
JOIN rot_gallery_data1 AS gd ON gd.gallery_id = gs.gallery_id
JOIN rot_thumbs AS gt ON gt.thumb_id = gs.thumb_id
WHERE  1 = 1
       AND gs.group_id = 0
       AND gs.tested_thumb = 'no'
       AND gi.gallery_status = 'active'
       AND gi.gallery_type = 0
       AND gs.gallery_id NOT IN ( '140', '49682', '501', '6313',
                                  '1675', '35085', '91973', '7000',
                                  '1479', '5595', '2953', '9965',
                                  '57759', '2669', '203', '46149',
                                  '8416', '3485', '28556', '62385',
                                  '1772', '5967', '5172', '63600',
                                  '12926', '65656', '15395', '21302',
                                  '1326', '10011', '148999', '429',
                                  '65775', '1588', '65832', '148353',
                                  '124728', '6073', '25697', '10283',
                                  '24132', '29185', '170889', '66095',
                                  '52826', '1490', '21393', '69353',
                                  '7825', '86', '154211', '1744',
                                  '1331', '74', '123354', '124435',
                                  '15162', '45136', '194202', '81492' )
ORDER  BY gs.total_ctr DESC
LIMIT  0, 300

Версия мускуля 5.7.33
Какие есть варианты решения проблемы?

[admin]

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

[anton.sivanov2]

Последняя доступная версия, что есть в портах - 5.7.34 (была 5.7.33).
Обновление не дало никаких результатов.

[admin]

я хз что за система но уже 3 года как вышла 8я версия

https://dev.mysql.com/downloads/mysql/

https://en.wikipedia.org/wiki/MySQL

[anton.sivanov2]

обновления мускуля до последней версии ничего не меняет

[admin]

тогда не знаю, скидывайте шел , буду смотреть почему мускл там так строит завтрос и как лучше сделать

[anton.sivanov2]

Я могу дамп скинуть. Только напиши куда. Если запрос в твоей среде будет работать норм, то скину доступы (на почту же высылать?)

[admin]

не вопрос, давай линк в личку

[admin]

залил дамп себе
у меня правда Maria DB 10

1 SIMPLE gs ref unique_ids,best_thumb,gallery_id,thumb_id,tested_t... tested_thumb_index 3 const,const 569762 Using where

1 SIMPLE gi eq_ref PRIMARY,sponsor_id,gallery_type,gallery_status PRIMARY 4 tt_test.gs.gallery_id 1 Using where
1 SIMPLE gt eq_ref PRIMARY PRIMARY 4 tt_test.gs.thumb_id 1
1 SIMPLE gd eq_ref PRIMARY PRIMARY 4 tt_test.gs.gallery_id 1


те первым юзается именно указанный индекс сам .. посмотрю сервак

[admin]

обновления мускуля до последней версии ничего не меняет

открыл шел

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 992931
Server version: 5.7.34-log Source distribution


никакого обновления нет