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

Question

[Евгений]

Хочу ускорить запрос.
Сделал новую таблицу 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

Comments

[Adamos]

> Что не так?
Не отформатирован код в вопросе. Кто же в такой каше будет копаться?

[Adamos]

oc_product_attribute_id.text_id может повторяться для product_id?
Если нет, то, может быть, эту таблицу и незачем дергать четыре раза?

[Евгений]

для каждого product_id несколько значений атрибутов (oc_product_attribute_id.text_id)

Answer 1

[Евгений]

Разобрался, сделал.
Было:

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 быстрее

Comments

[Алексей]

Примите мои поздравления. Скрипт стал намного красивее и понятнее. Удачи!

Answer 2

[Максим Кудрявцев]

Все участвующие в условиях WHERE поля, должны иметь индекс. В идеале это либо PK либо UNIQUE. Я думаю что твоя проблема где-то в этой стороне, но без детальных сведений о твоей БД более точно сказать ничего не могу.

Comments

[Евгений]

Добавил структуру данных в вопрос prntscr.com/edxyjr Таблица oc_product_attribute - 21320 строк oc_product_attribute_id - 31250строк, т.к. в первой таблице в поле text значения могут быть задвоены, например ЛСП:ЛПП, а во второй это будет разбито на 2 строки в одной ЛСП в другой ЛПП.

Answer 3

[Алексей]

Если вы хотите от запроса скорости, то избавляйтесь от "вложенных" запросов. Вот это:

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 к таблице и не мучать себя лишними и бесполезными условиями.

Comments

[Евгений]

Функция, которая формирует запрос универсальная и при необходимости она в условия добавляет таблицу m и т.п, а Join рисует по умолчанию на любой вариант запроса. Аналогично с WHERE 1, чтобы при необходимости добавить AND .... По вложенным запросам понял, буду думать. Только совсем не понимаю как работает WHERE p2a.attribute_id IS NOT NULL AND EXISTS .... Там получаются 3 таблицы с единицами каждая и как они влияют на p2a.attribute_id для меня загадка.