@eugeneledenev

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

Хочу ускорить запрос.
Сделал новую таблицу oc_product_attribute_id в которую добавил для каждого поля text индекс text_id.
Переписал запрос с text_id и время по сравнению с поиском через LIKE увеличилось в 35 раз. Что не так?
Первоначальный запрос:
SELECT SQL_NO_CACHE attribute_id, text FROM (
SELECT DISTINCT p.product_id, p2a.attribute_id, p2a.text, p.price as realprice 
FROM oc_product p 
LEFT JOIN oc_manufacturer m ON(m.manufacturer_id=p.manufacturer_id) 
LEFT JOIN oc_product_option_value pov ON (pov.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) 
<b>LEFT JOIN oc_product_attribute p2a ON (p2a.product_id=p.product_id) 
WHERE p2a.attribute_id IS NOT NULL AND EXISTS 
(select 1 FROM oc_product_attribute p2a0 
WHERE p2a0.product_id=p2a.product_id AND p2a0.attribute_id = 20 
AND (p2a0.text = '2х36 Вт' OR p2a0.text like '2х36 Вт:%' OR p2a0.text like '%:2х36 Вт' OR p2a0.text like '%:2х36 Вт:%')) 
AND EXISTS 
(select 1 FROM oc_product_attribute p2a1 
WHERE p2a1.product_id=p2a.product_id AND p2a1.attribute_id = 22 
AND (p2a1.text = 'накладные' OR p2a1.text like 'накладные:%' OR p2a1.text like '%:накладные' OR p2a1.text like '%:накладные:%')) 
AND EXISTS 
(select 1 FROM oc_product_attribute p2a2 
WHERE p2a2.product_id=p2a.product_id AND p2a2.attribute_id = 27 
AND (p2a2.text = '120 см' OR p2a2.text like '120 см:%' OR p2a2.text like '%:120 см' OR p2a2.text like '%:120 см:%')) </b>
AND p.status = '1' AND p2s.store_id = 0) as innertable WHERE 1 GROUP BY attribute_id, text


Переписанный вариант:
SELECT SQL_NO_CACHE attribute_id, text FROM (
SELECT DISTINCT p.product_id, p2a.attribute_id, p2a.text, p.price as realprice 
FROM oc_product p 
LEFT JOIN oc_manufacturer m ON(m.manufacturer_id=p.manufacturer_id) 
LEFT JOIN oc_product_option_value pov ON (pov.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_id p2a ON (p2a.product_id=p.product_id) 
<b>WHERE p2a.attribute_id IS NOT NULL 
AND EXISTS (select 1 FROM oc_product_attribute_id p2a0 WHERE p2a0.product_id=p2a.product_id AND p2a0.text_id = 67) 
AND EXISTS (select 1 FROM oc_product_attribute_id p2a1 WHERE p2a1.product_id=p2a.product_id AND p2a1.text_id = 82)
AND EXISTS (select 1 FROM oc_product_attribute_id p2a2 WHERE p2a2.product_id=p2a.product_id AND p2a2.text_id = 88) </b>
AND p.status = '1' AND p2s.store_id = 0) as innertable WHERE 1 GROUP BY attribute_id, text

EXPLAIN: prntscr.com/edx5zj

Структура данных:
prntscr.com/edxyjr
  • Вопрос задан
  • 488 просмотров
Решения вопроса 1
@eugeneledenev Автор вопроса
Разобрался, сделал.
Было:
SELECT SQL_NO_CACHE attribute_id, text FROM (
SELECT DISTINCT p.product_id, p2a.attribute_id, p2a.text, p.price as realprice 
FROM oc_product p 
LEFT JOIN oc_manufacturer m ON(m.manufacturer_id=p.manufacturer_id) 
LEFT JOIN oc_product_option_value pov ON (pov.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) 
WHERE p2a.attribute_id IS NOT NULL AND EXISTS 
(select 1 FROM oc_product_attribute p2a0 
WHERE p2a0.product_id=p2a.product_id AND p2a0.attribute_id = 20 
AND (p2a0.text = '2х36 Вт' OR p2a0.text like '2х36 Вт:%' OR p2a0.text like '%:2х36 Вт' OR p2a0.text like '%:2х36 Вт:%')) 
AND EXISTS (select 1 FROM oc_product_attribute p2a1 WHERE p2a1.product_id=p2a.product_id AND p2a1.attribute_id = 22 
AND (p2a1.text = 'накладные' OR p2a1.text like 'накладные:%' OR p2a1.text like '%:накладные' OR p2a1.text like '%:накладные:%')) 
AND EXISTS (select 1 FROM oc_product_attribute p2a2 WHERE p2a2.product_id=p2a.product_id AND p2a2.attribute_id = 27 
AND (p2a2.text = '120 см' OR p2a2.text like '120 см:%' OR p2a2.text like '%:120 см' OR p2a2.text like '%:120 см:%')) 
AND p.status = '1' AND p2s.store_id = 0) as innertable WHERE 1 GROUP BY attribute_id, text


Стало:
SELECT SQL_NO_CACHE attribute_id, text FROM (
SELECT DISTINCT p.product_id, p2a.attribute_id, p2a.text, p.price as realprice 
FROM oc_product p 
LEFT JOIN oc_manufacturer m ON(m.manufacturer_id=p.manufacturer_id) 
LEFT JOIN oc_product_option_value pov ON (pov.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_id p2a ON (p2a.product_id=p.product_id)
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) 
WHERE (p2a0.attribute_id = 20 AND p2a0.text like '%2х36 Вт%') 
AND  (p2a1.attribute_id = 22 AND p2a1.text like '%накладные%')
AND  (p2a2.attribute_id = 27 AND p2a2.text like '%120 см%')
) as innertable WHERE 1 GROUP BY attribute_id, text

Время 0,13 ->0.05.
Когда LIKE %...% заменил на text_id получилось время 0,04 :
SELECT SQL_NO_CACHE attribute_id, text FROM (
SELECT DISTINCT p.product_id, p2a.attribute_id, p2a.text, p.price as realprice 
FROM oc_product p 
LEFT JOIN oc_manufacturer m ON(m.manufacturer_id=p.manufacturer_id) 
LEFT JOIN oc_product_option_value pov ON (pov.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)
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) 
LEFT JOIN oc_product_attribute_id p2a ON (p2a.product_id=p.product_id)
WHERE (p2a0.text_id = '67')
AND  (p2a1.text_id = '82')
AND  (p2a2.text_id = '88')
) as innertable WHERE 1 GROUP BY attribute_id, text

Причем расположение строки LEFT JOIN oc_product_attribute_id p2a ON (p2a.product_id=p.product_id) влияет на время в 2 раза. После Inner join быстрее
Ответ написан
Пригласить эксперта
Ответы на вопрос 2
kumaxim
@kumaxim
Web-программист
Все участвующие в условиях WHERE поля, должны иметь индекс. В идеале это либо PK либо UNIQUE. Я думаю что твоя проблема где-то в этой стороне, но без детальных сведений о твоей БД более точно сказать ничего не могу.
Ответ написан
k1lex
@k1lex
Программист торг. сети. C# (WPF, WinForms), T-SQL
Если вы хотите от запроса скорости, то избавляйтесь от "вложенных" запросов. Вот это:
EXISTS (select 1 FROM oc_product_attribute_id p2a0 WHERE p2a0.product_id=p2a.product_id AND p2a0.text_id = 67)

На каждую строку системе еще и доп запрос делать приходится

Убирайте лишние join-ны.
К примеру у вас есть
LEFT JOIN oc_manufacturer m ON(m.manufacturer_id=p.manufacturer_id)

А теперь найдите где вы к M в выборке данных общаетесь.

Если вы выводите данные attribute_id, text
то зачем вам в подзапросе еще куча полей понадобилась?
SELECT DISTINCT p.product_id, p2a.attribute_id, p2a.text, p.price as realprice


WHERE 1
Я не знаток MySQL но я не понимаю зачем вам это???

Еще видим условие:
WHERE p2a.attribute_id IS NOT NULL

Если p2a.attribute_id обязателен, то стоит написать INNER JOIN к таблице и не мучать себя лишними и бесполезными условиями.
Ответ написан
Ваш ответ на вопрос

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

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