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

[admin]

кстати чем забавно выбор в 5.7 ( надо смотреть как там выбирает оптимизатор то он зачем то взял индекс куда входит 99% галер) а индекс по которому сортировать - почему то пропустил)

поствил дефолтный инсталл 8й версии

1 SIMPLE gs NULL ref unique_ids,best_thumb,gallery_id,thumb_id,tested_t... tested_thumb_index 3 const,const 569762 100.00 Using where; Backward index scan
1 SIMPLE gd NULL eq_ref PRIMARY PRIMARY 4 scj.gs.gallery_id 1 100.00 NULL
1 SIMPLE gt NULL eq_ref PRIMARY PRIMARY 4 scj.gs.thumb_id 1 100.00 NULL

[anton.sivanov2]

На самом сервере -да, стоит MySQL 5.7.34.
Тесты же проводятся на локальном стенде.
Так вот, ставил и mariadb 10.3 и mariadb 10.5 (все локально). Результат всегда один и тот же - запрос отрабатывает медленно, потому что не использует нужный индекс.

Code: Select all

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 23
Server version: 10.5.11-MariaDB-1:10.5.11+maria~focal mariadb.org binary distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

explain:

Code: Select all

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: gi
         type: index_merge
possible_keys: PRIMARY,sponsor_id,gallery_type,gallery_status
          key: gallery_type,gallery_status
      key_len: 1,1
          ref: NULL
         rows: 148630
        Extra: Using intersect(gallery_type,gallery_status); Using where; Using temporary; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: gd
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: xnxxwank.gi.gallery_id
         rows: 1
        Extra: 
*************************** 3. row ***************************
           id: 1
  select_type: SIMPLE
        table: gs
         type: ref
possible_keys: unique_ids,best_thumb,gallery_id,thumb_id,tested_thumb_index,group_id,group_activationdate
          key: unique_ids
      key_len: 6
          ref: xnxxwank.gi.gallery_id,const
         rows: 1
        Extra: Using where
*************************** 4. row ***************************
           id: 1
  select_type: SIMPLE
        table: gt
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: xnxxwank.gs.thumb_id
         rows: 1
        Extra:

запрос:

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
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 gi.gallery_status = 'active'
       AND gi.gallery_type = 0
       AND gs.tested_thumb = 'no'
       AND gs.group_id = 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

То есть минимум на 2-ух разных машинах один и тот же запрос работает медленно и не использует нужный индекс.
Тесть проводился на:

Code: Select all

Distributor ID:	Ubuntu
Description:	Ubuntu 20.04.2 LTS
Release:	20.04
Codename:	focal

[anton.sivanov2]

кстати чем забавно выбор в 5.7 ( надо смотреть как там выбирает оптимизатор то он зачем то взял индекс куда входит 99% галер) а индекс по которому сортировать - почему то пропустил)

поствил дефолтный инсталл 8й версии

1 SIMPLE gs NULL ref unique_ids,best_thumb,gallery_id,thumb_id,tested_t... tested_thumb_index 3 const,const 569762 100.00 Using where; Backward index scan
1 SIMPLE gd NULL eq_ref PRIMARY PRIMARY 4 scj.gs.gallery_id 1 100.00 NULL
1 SIMPLE gt NULL eq_ref PRIMARY PRIMARY 4 scj.gs.thumb_id 1 100.00 NULL

Странно, так как все на том же локальном компе с 8-кой у меня по умолчанию также не юзался индекс нужный.

[admin]

да, и индекс лучше, и в запроса stats1 таблица первая, а почему-то у вас выбирает info таблицу

варианта 2

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

[anton.sivanov2]

с восьмой версией на самом сервере на первую страницу запрос отрабатывает верно (использует нужный индекс), а вот при пагинации уже начинает использовать индекс по старому(
Сейчас версия мускуля 8

[anton.sivanov2]

Вот такой запрос на пагинации:

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   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 gi.gallery_status = 'active'  AND gi.gallery_type = 0  and gs.tested_thumb = 'no'  and gs.group_id = 0  AND
gs.gallery_id NOT IN ('6371','5155','180918','80138','65421','26866','20664','89113','8591','172100','42377','29591','63307','22643','90032','53457','25422','4205','8956','16555','69731','37062','24623','24219','9205','4906','69274','5557','81491','62740','8896','984','83','4336','23125','46272','63421','3796','78615','19293','21875','64694','10000','44774','5965','71537','16908','65917','7628','42034','89424','116582','2916','48129','268','52851','27757','3905','65830','15120')  ORDER BY  gs.total_ctr DESC  LIMIT 120, 300

[admin]

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

[anton.sivanov2]

Да, можно чекнуть на сервере.
неверно - я имею ввиду, что на страницах пагинации запрос работает медленно, увы.

[admin]

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

[admin]

пока не нашел как настроить мускл поэтому добавил в ап что б подставляло в нужном порядке таблицы
скиньте мне плз админку что бм ожно было проверить правильно ли начало работать у вас