Как победить медленный SQL запрос с множественными JOIN?

Question

[Евгений]

Запрос выполняется больше 2 минут. Изначально вместо text_id было LIKE '%text%'. Переделал на ID, но проблема сохранилась.
Если убрать пару JOIN типа таких "INNER JOIN oc_product_attribute_id p2a7", то время в десятки раз падает.
Что происходит, почему начинаются с определенного количества JOIN тормоза?
Как такие ситуации лечатся?

SELECT count(*) as total, attribute_id, text  
FROM (
SELECT DISTINCT p.product_id, p2a.attribute_id, p2a.text, p.price as realprice  
FROM oc_product p 
INNER JOIN oc_product_attribute_id p2a0 ON ((p2a0.product_id=p.product_id)) 
INNER JOIN oc_product_attribute_id p2a1 ON ((p2a1.product_id=p.product_id)) 
INNER JOIN oc_product_attribute_id p2a2 ON ((p2a2.product_id=p.product_id)) 
INNER JOIN oc_product_attribute_id p2a3 ON ((p2a3.product_id=p.product_id)) 
INNER JOIN oc_product_attribute_id p2a4 ON ((p2a4.product_id=p.product_id)) 
INNER JOIN oc_product_attribute_id p2a5 ON ((p2a5.product_id=p.product_id)) 
INNER JOIN oc_product_attribute_id p2a6 ON ((p2a6.product_id=p.product_id)) 
INNER JOIN oc_product_attribute_id p2a7 ON ((p2a7.product_id=p.product_id)) 
INNER JOIN oc_product_attribute_id p2a ON (p2a.product_id=p.product_id) 
WHERE 1 
AND (p2a0.text_id = '126') 
AND (p2a1.text_id = '63') 
AND (p2a2.text_id = '120') 
AND (p2a3.text_id = '121') 
AND (p2a4.text_id = '82') 
AND (p2a5.text_id = '15') 
AND (p2a6.text_id = '86') 
AND (p2a7.text_id = '70') 
AND p.status = '1') as innertable 
WHERE 1  GROUP BY attribute_id, text;

Если оставить выборку со всеми JOIN только по oc_product_attribute_id, то скорость порядка 0,4 сек, что все равно не быстро.
oc_product - 117тыс записей
oc_product_attribute_id - 21тыс записей

Answer 1

[Андрей Николаев]

Во-первых, за count(*) as total по рукам, считайте хотя бы ID.
И я бы уже в коде (я так понимаю это интернет-магазин) разработал бы аналог "фасетногоый поиск". Я так понимаю в вложенном (большом) запросе вы делаете поиск товара по аттрибутам.

И на конец - попробуйте без INNER JOIN - на матричном умножении и уже сами в WHERE проконтролируйте (иногда это быстрее чем JOIN)

Comments

[d-stream]

Как-то сравнивал на MS SQL count(1) с count(*) - разница в пределах погрешности измерения (типа 380.5842 одно и 380.5827 другое, а в след раз оба запроса на несколько секунд быстрее)

Думаю оптимизатор MySQL уже тоже умеет понимать про "звездочку"

[Евгений]

d-stream: Я тоже когда тестил на запросах, аналогично разницы не увидел.

[d-stream]

Евгений: но могу подтвердить, что эти рецепты достаточно распространены и бывают даже долгие и затейливые холивары на эту тему... так же как и "join a.id=b.id vs join b.id=a.id" =))

[Андрей Николаев]

d-stream: Да, Вы правы. Видимо привычка.

[Евгений]

d-stream: Все надо тестить. Бывает решение классное, переписал запрос, а по факту либо дольше либо так же. Не многие разбираются в глубинах MySQL, т.к. с современными серверами часто даже не лучший запрос летает и копать глубже необходимости нет. И мой запрос нормально работает на стандартном магазине с 5тыс товаров.