@LebedevStr

Как оптимизировать такой SQL запрос?

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

Сейчас данный запрос выполняется 37 секунд.

SELECT product_id FROM(
SELECT DISTINCT p.product_id, pd.name, p.model, p.quantity, p.price, p.sort_order, p.date_added , p.price as realprice 
FROM oc_product p 
LEFT JOIN oc_product_option_value pov ON (pov.product_id=p.product_id) 
LEFT JOIN oc_product_description pd ON (pd.product_id=p.product_id) 
LEFT JOIN oc_product_to_store p2s ON (p2s.product_id=p.product_id) 
LEFT JOIN oc_product_to_category p2c ON (p2c.product_id=p.product_id) 
LEFT JOIN oc_category_path cp ON(cp.category_id=p2c.category_id) 
LEFT JOIN oc_product_attribute p2a ON (p2a.product_id=p.product_id) 
LEFT JOIN oc_product_attribute p2a0 ON (p2a0.product_id=p2a.product_id) 
LEFT JOIN oc_product_attribute p2a1 ON (p2a1.product_id=p2a.product_id) 
LEFT JOIN oc_product_attribute p2a2 ON (p2a2.product_id=p2a.product_id) 
LEFT JOIN oc_product_attribute p2a3 ON (p2a3.product_id=p2a.product_id) 
LEFT JOIN oc_product_attribute p2a4 ON (p2a4.product_id=p2a.product_id) 
LEFT JOIN oc_product_attribute p2a5 ON (p2a5.product_id=p2a.product_id) 
WHERE 1 AND cp.path_id IN (3558) 
AND p2a0.attribute_id = 12 AND (p2a0.text LIKE '%ЛПО%') 
AND p2a1.attribute_id = 20 AND (p2a1.text LIKE '%2х36 Вт%') 
AND p2a2.attribute_id = 22 AND (p2a2.text LIKE '%накладные%') 
AND p2a3.attribute_id = 27 AND (p2a3.text LIKE '%60 см%') 
AND p2a4.attribute_id = 29 AND (p2a4.text LIKE '%T8%') 
AND p2a5.attribute_id = 30 AND (p2a5.text LIKE '%220 В%') 
AND pd.language_id = '1' AND p.status = '1' AND p2s.store_id = 0) as innertable 
WHERE 1 ORDER BY sort_order ASC, LCASE(name) ASC LIMIT 0,20
  • Вопрос задан
  • 551 просмотр
Пригласить эксперта
Ответы на вопрос 8
DmitriyEntelis
@DmitriyEntelis
Думаю за деньги
Жесть на самом деле ;-)

Смотрите, начнем с того что каждый join это произведение матриц. Есть смысл как можно более сильно зарезать условие join, те вместо
LEFT JOIN oc_product_attribute p2a0 ON (p2a0.product_id=p2a.product_id)

писать
JOIN oc_product_attribute p2a0 ON (p2a0.product_id=p.product_id and p2a0.attribute_id = 12 AND p2a0.text LIKE '%ЛПО%')

Обратите внимание что я ссылаюсь на таблицу p - в ней уже есть product_id
И убеждаемся что в oc_product_attribute есть индекс (product_id, attribute_id, text)

Далее:
Хороший способ оптимизировать запросы - разбить их на несколько, в данном случае это вполне возможно.
6 запросов вида
select product_id from oc_product_attribute p2a0 where (p2a0.product_id=p.product_id and p2a0.attribute_id = 12 AND p2a0.text LIKE '%ЛПО%')

Дальше в ЯП выбираем product_id которые есть во всех 6 выборках.
Дальше
select * from oc_product  where product_id IN (1,2,3 ...)
Ответ написан
e_svirsky
@e_svirsky
Web Developer
Нужно определить какой LEFT JOIN ложит ваш запрос. Потом проверить - индексы, если нет, сделать. Если есть, тогда подумать, может стоит добавить избыточность. Еще тут проблема в LIKE. Может есть смысл использовать какой поисковой движок, Elastic, Sphinx.
Ответ написан
@edb
SQL
1. замените все left join на inner join. Это не обязательно принесет автоматически производительность, но будет правильнее. Потому что AND cp.path_id IN (3558) делает из всех "left join" "inner join".
2. уберите из "FROM" все соединения oc_product_attribute, и замените условия в where по этим таблицам на exists например:
and exists (select * 
                  from oc_product_attribute a 
                  where a.product_id=p2a.product_id 
                      and a.attribute_id = 12 
                      and a.text LIKE '%ЛПО%'
               )

таким образом вы избавитесь от distinct
если еще нет индекса на attribute_id, создайте его.
3. Вам не надо использовать подзапрос. Это лишнее
4. Оставьте в select только product_id
Это все, что можно сказать только по запросу. Все остальное будет гаданием на кофейной гуще. Впрочем, это тоже все может быть пальцем в небо :)
Ответ написан
evnuh
@evnuh
Поиск Гугл помог мне, впусти и ты его в свой дом
Подзапрос, дистинкт, джоины, нечёткий поиск с %%, сортировка по выражению, лимит - да это самая худшая ситуация, которую можно представить для реляционной субд.
Ответ написан
un1t
@un1t
Вам точно нужны запросы вида?
p2a0.text LIKE '%ЛПО%'
Возможно достаточно?
p2a0.text LIKE 'ЛПО'
Ответ написан
idShura
@idShura
Можно вот так попробовать (+то что советовали выше):

SELECT product_id FROM(
	SELECT DISTINCT p.product_id, pd.name, p.model, p.quantity, p.price, p.sort_order, p.date_added , p.price as realprice 
	  FROM oc_product p 
	LEFT JOIN oc_product_option_value pov ON pov.product_id = p.product_id
	LEFT JOIN oc_product_description pd ON pd.product_id = p.product_id 
	LEFT JOIN oc_product_to_store p2s ON p2s.product_id = p.product_id 
	LEFT JOIN oc_product_to_category p2c ON p2c.product_id = p.product_id
	LEFT JOIN oc_category_path cp ON cp.category_id = p2c.category_id
	LEFT JOIN oc_product_attribute p2 ON p2.product_id = p.product_id 
	WHERE 1 AND cp.path_id IN (3558) 
	  AND (
	  	   (p2.attribute_id = 12 AND p2a0.text LIKE '%ЛПО%') OR
	       (p2a1.attribute_id = 20 AND p2a1.text LIKE '%2х36 Вт%') OR
	       (p2a2.attribute_id = 22 AND p2a2.text LIKE '%накладны%') OR
	       (p2a3.attribute_id = 27 AND p2a3.text LIKE '%60 см%') OR
	       (p2a4.attribute_id = 29 AND p2a4.text LIKE '%T8%') OR
	       (p2a5.attribute_id = 30 AND p2a5.text LIKE '%220 В%')
	      )
	  AND pd.language_id = '1' AND p.status = '1' AND p2s.store_id = 0) as innertable 
WHERE 1 ORDER BY sort_order ASC, LCASE(name) ASC LIMIT 0,20
Ответ написан
Ваш ответ на вопрос

Войдите, чтобы написать ответ

Войти через центр авторизации
Похожие вопросы