Как можно оптимизировать запрос sql или конфигурацию сервера mysql?

Question

[AlexRas]

Всем привет.

Есть такой запрос:

SELECT COUNT(DISTINCT t.subscriber_id) as counter 

FROM `list_subscriber` `t`  

LEFT JOIN `list_field_value` `fieldValues984` ON `fieldValues984`.`subscriber_id`=`t`.`subscriber_id` 
LEFT JOIN `list_field_value` `fieldValues986` ON `fieldValues984`.`subscriber_id`=`t`.`subscriber_id` 

WHERE 
    `t`.`list_id` = 60
    AND `t`.`status` = 'confirmed'

    AND `fieldValues984`.`field_id` = 984 
    AND `fieldValues984`.`value` LIKE '%2022%'

    AND `fieldValues986`.`field_id` = 986 
    AND `fieldValues986`.`value` LIKE '%example.ru%'

Запрос формирует CMS MailWizz, написанная на yii1.

Размеры всей базы около 120гб.

Таблица list_subscriber - 20 миллионов записей.

Таблица list_field_value - 50 миллионов записей.

Запрос выполняется примерно 5 секунд, но на странице подобных запросов может быть больше и сами запросы могут быть сложнее, больше join, больше условий.

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

EXPLAIN запроса:

EXPLAIN SELECT COUNT(DISTINCT t.subscriber_id) as counter 

FROM `list_subscriber` `t`  

LEFT JOIN `list_field_value` `fieldValues984` ON `fieldValues984`.`subscriber_id`=`t`.`subscriber_id`  

WHERE 
    `t`.`list_id` = 60
    AND `t`.`status` = 'confirmed'

    AND `fieldValues984`.`field_id` = 984 
    AND `fieldValues984`.`value` LIKE '%2022%'

Comments

[Дмитрий]

explain?

[AlexRas]

Дмитрий, добавил

[Akina]

AND `alias`.`value` LIKE '%literal%'

Это не оптимизируется.

[Дмитрий]

Akina, функциональными индексами? Типа REVERSE(`alias`.`value`) like and `alias`.`value` like

[N]

Дмитрий,
Я тут мимо проходил...стало интересно про LIKE и индексы...
Загуглил "функциональные индексы MySQL", вник, изучил - не понял как к задаче применить...
Далее гуглю за "REVERSE"...в итоге он существует с версии 8...но опять же - как к задаче применить относительно LIKE - тут хз... O_o

[Дмитрий]

N, ну по факту условие field like "%value%" (которое индекс не может использовать) можно считать как field like "value%" и reverse(field) like "value%". - которое в 1 случае индекс по field использовать может, а во втором случае можно подпихнуть функциональный индекс

[N]

Дмитрий, Щас понял...спасибо.
Но на практике не видел...скорее всего ещё придётся указывать в самом запросе когда и какой индекс использовать (есть такое в MySQL), ибо сам "движок мускула" не всегда может адекватно подобрать индексы...

[Дмитрий]

N, есть. но с последними версиям мускула это делается не часто. думаю и здесь должен сожрать.

[N]

Дмитрий, Принял. Понял. Когда-нибудь проверю))

Ну и автор вопроса:
Отпишись, если такое намного ускорит запрос...

[Akina]

Дмитрий,

по факту условие field like "%value%" (которое индекс не может использовать) можно считать как field like "value%" и reverse(field) like "value%".

Только в ну очень специальных условиях - например, если точно известно, что в строковом поле хранится дата, причём в определённом формате (год в конце, символы после года не присутствуют).

Но в этом конкретном случае куда как разумнее WHERE RIGHT(column, 4) = '2022'.

[Дмитрий]

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

Answer 1

[Алексей Лебедев]

Like вообще беда, нормально индексе работают только example.ru%
Можно попробовать поиграться с кодировкой поле к которому применяется Like.
Убрать регистрозависимость. То есть использовать в поле и в поиске только строчные буквы.
У нас был MS SQL и это положительно повлияло на LIKE %example.ru%