Как лучше оптимизировать запрос в базу?

Question

[Дмитрий Игнатов]

Здравствуйте! Есть такой запрос

select distinct `pfv`.`value_id`
from `products`
    left join `product_filter_value` as `pfv` on `pfv`.`product_id` = `products`.`id`
where `products`.`category_id` = '12'
  and exists (select * from `filter_values` inner join `product_filter_value` on `filter_values`.`id` = `product_filter_value`.`value_id` where `products`.`id` = `product_filter_value`.`product_id` and `filter_values`.`id` in ('4', '5', '6'))
  and exists (select * from `filter_values` inner join `product_filter_value` on `filter_values`.`id` = `product_filter_value`.`value_id` where `products`.`id` = `product_filter_value`.`product_id` and `filter_values`.`id` in ('8', '9', '12', '14', '15', '16'))
  and exists (select * from `filter_values` inner join `product_filter_value` on `filter_values`.`id` = `product_filter_value`.`value_id` where `products`.`id` = `product_filter_value`.`product_id` and `filter_values`.`id` in ('1', '2', '3', '800', '802', '803', '804'))

Можете дать совет как его оптимизировать? Проовал через join но скорость падала

Comments

[Akina]

XY-problem.

Формулируйте задачу, а не попытку её решить.

[Дмитрий]

как минимум explain бы не помешал.

[maksam07]

То есть во так, без переменных, вы сразу выполняете этот статичный запрос?

Answer 1

[alexalexes]

Есть два дилетантских момента построения БД:
1. Используется строковый тип данных вместо целочисленного на всех полях, где оно используется как идентификатор, (все, что ...id).
Вероятно, второе следствие наивного проектирования БД:
2. На поля, которые участвуют в соединении таблиц или в условии фильтрации через where не предусмотрены индексы. По данному запросу сразу просится проверить наличие или создать индексы на:
`products`.`category_id`, `product_filter_value`.`value_id`, `product_filter_value`.`product_id`.

Comments

[Дмитрий Игнатов]

Да, к сожалению не я проектировал, мне дали уже в таком виде. Индексы есть, уже создал.
Про вервый пункт не совсем понял, можете пояснить?

[alexalexes]

Пример:
`products`.`category_id` = '12'
12 записано в кавычках, значит поле category_id имеет как минимум типа varchar или text.
Если в качестве идентификатора используются числовые значения, а не какой-нибудь guid/контрольная сумма, или в идентификации объекта не предусмотрены какие-нибудь литералы, типа '12a', то нет причин делать это поле не целочисленным. Это лишает возможности делать автоинкременты к полям, если они первичные ключи, приводит к избыточности хранения данных, замедлению работы индексов.

[Дмитрий Игнатов]

alexalexes, там само поле integer, но такой запрос генерирует сам Laravel

[alexalexes]

Значит, я забываю, что это mySQL, тут нет строгой проверки типа входных параметров.
А что там по плану выполнения запроса?
explain <ваш запрос>

[Дмитрий Игнатов]

Тут кстати интересный момент, первый exist использует правильный индекс, а последующие не такие же индексы

[alexalexes]

Дмитрий Игнатов, попробуйте запустить анализ индексов заново, может просто попортились.

[Дмитрий Игнатов]

alexalexes, а как это сделать?

[alexalexes]

Используйте optimize table.