Как ускорить SQL запрос?

Question

[Евгений]

Ниже запрос. Выполняется 0,85сек.
Если убрать LCASE(p.model) = 'ao' OR LCASE(p.sku) = 'ao', то скорость падает до 0,2сек.
пробовал без lcase, ставил индексы по model и sku не помогает.
Не могу понять почему так происходит, по идее '=' должно работать быстрее чем 'LIKE'.
Если убрать только одно из двух "=" то уменьшается скорость всего на 0,1сек, а если убрать оба то на 0,6

SELECT SQL_CALC_FOUND_ROWS p.product_id, NULL AS rating, NULL AS discount, NULL AS special 
FROM oc_product p LEFT JOIN oc_product_description pd ON (p.product_id = pd.product_id) 
WHERE p.status = '1' AND ( pd.name LIKE '%ao%' OR pd.tag LIKE '%ao%' OR LCASE(p.model) = 'ao' OR LCASE(p.sku) = 'ao') 
GROUP BY p.product_id 
ORDER BY (p.price=0 OR p.price >= 7000000), p.price DESC, LCASE(pd.name) DESC 
LIMIT 0,20

Answer 1

[Евгений]

Проблем несколько.
1. не работают одновременно индексы текстовые (model+sku) и числовые (status)

SELECT SQL_NO_CACHE p.product_id
FROM oc_product p 
WHERE p.status = '1' AND ( p.model = 'ao' OR p.sku = 'ao' )

0.26 сек срабатывает только индекс по status

SELECT SQL_NO_CACHE p.product_id
FROM oc_product p LEFT JOIN oc_product p2 ON (p.product_id=p2.product_id)
WHERE p.status = '1' AND ( p2.model = 'ao' OR p2.sku = 'ao' )

0 сек отрабатываю оба индекса

2. Как писали выше наличие LCASE убивает индекс.

SELECT SQL_NO_CACHE p.product_id
FROM oc_product p LEFT JOIN oc_product p2 ON (p.product_id=p2.product_id)
WHERE p.status = '1' AND ( LCASE(p2.model) = 'ao' OR LCASE(p2.sku) = 'ao' )

0.60 сек

В итоге запрос сильно усложнился. Время выполнения упало до 0,19 сек. (в 4 раза)

SELECT SQL_NO_CACHE SQL_CALC_FOUND_ROWS p3.product_id, NULL AS rating, NULL AS discount, NULL AS special 
FROM 

 (SELECT p.product_id
 FROM oc_product p 
 LEFT JOIN oc_product p2 ON (p.product_id=p2.product_id) 
 WHERE p.status = '1' AND ( p2.model = 'ao' OR p2.sku = 'ao' ) 
 UNION
 SELECT p.product_id
 FROM oc_product p 
 LEFT JOIN oc_product_description pd ON (p.product_id = pd.product_id) 
 WHERE p.status = '1' AND ( pd.name LIKE '%ao%' OR pd.tag LIKE '%ao%') ) as t1

LEFT JOIN oc_product p3 ON (t1.product_id=p3.product_id)
LEFT JOIN oc_product_description pd ON (p3.product_id = pd.product_id)
GROUP BY p3.product_id 
ORDER BY (p3.price=0 OR p3.price >= 7000000), p3.price DESC, LCASE(pd.name) DESC 
LIMIT 0,20

Answer 2

[Rsa97]

Падает, всё-таки, не скорость, а время выполнения, значит скорость растёт.
А в остальном всё понятно, индексы во WHERE, GROUP BY, ORDER BY работают только по непосредственному значению колонки. Если колонка входит в какое-либо выражение либо функцию, то индекс не работает.

Comments

[Евгений]

LCASE(p.model) = 'ao' OR LCASE(p.sku) = 'ao' - 0.8сек
при замене на p.model = 'ao' OR p.sku = 'ao' - 0,73сек
Все равно очень медленно. Это при наличии индекса и условия равно, а не поиска в тексте.

[ApeCoder]

Какой план запроса?

[Евгений]

ApeCoder,

[Евгений]

ApeCoder,

[Евгений]

ApeCoder,
А вот вариант без LCASE(p.model) = 'ao' OR LCASE(p.sku)

[hx510b]

Евгений, попробуй без order by выполнить, копирование во временную таблицу дает основной вклад. если проблема здесь, то есть смысл order by разгрузить, чтобы он стал эффективным.

[Freeman]

Если колонка входит в функцию или выражение, в развитых СУБД можно создать функциональный индекс. Is it possible to have function-based index in MySQL?

Answer 3

[hx510b]

0. нужно использовать EXPLAIN для анализа причин тормозов https://habr.com/post/211022/
1. LCASE(p.model) = 'ao' - полагаю, индекс не используется - отсюда просадка, лучше изменять регистр в правой части, либо модифицировать данные так, чтобы условия выборки были оптимизированы.
2. заведомо оптимизируемые условия в WHERE:LCASE(p.model) = 'ao' OR LCASE(p.sku) = 'ao'
следует поставить левее, а менее оптимизируемые правее - это поиск подстроки - в самый назад.
суть простая: если какое-то условия сработает - остальные проверяться не будут благодаря OR.
но в данном вопросе это мало влияния оказывает.

Answer 4

[forspamonly2]

вам тут полнотекстовый индекс нужен