Проблем несколько.
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
