How to speed up SQL query?

Question

SQL

Below is a request. Runs 0.85sec.
If you remove LCASE(p.model) = 'ao' OR LCASE(p.sku) = 'ao', then the speed drops to 0.2sec.
tried without lcase, put indexes on model and sku does not help.
I can't understand why this happens, in theory '=' should work faster than 'LIKE'.
If you remove only one of the two "=", then the speed decreases by only 0.1 seconds, and if you remove both, then by 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

There are several problems.
1. Text (model+sku) and numeric (status) indexes do not work at the same time

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 sec only index by status is triggered

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 sec I work out both indexes
2. As mentioned above, the presence of LCASE kills the index.

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 sec
As a result, the query became much more complicated. Execution time dropped to 0.19 sec. (4 times)

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

Falls, after all, not the speed, but the execution time, which means the speed is growing.
Otherwise, everything is clear, indexes in WHERE, GROUP BY, ORDER BY work only on the direct value of the column. If the column is included in any expression or function, then the index does not work.

Answer 3

0. you need to use EXPLAIN to analyze the causes of brakes https://habr.com/post/211022/
1. LCASE(p.model) = 'ao' - I guess the index is not used - hence the drawdown, it's better to change the case on the right side, or modify the data so that the sampling conditions are optimized.
2. deliberately optimized conditions in WHERE: LCASE(p.model) = 'ao' OR LCASE(p.sku) = 'ao'
should be placed to the left, and less optimized to the right - this is a search for a substring - to the very back.
the essence is simple: if some conditions work, the rest will not be checked thanks to OR.
but in this case it has little effect.

Answer 4

you need a full-text index here