Оптимизация SQL запроса от фильтра товаров

[Varov]

Здравствуйте!
Имеется такой запрос время выполнения больше трех секунд:

SELECT MAX(a.attribute_id) attribute_id, MAX(ad.name) attribute_name, MAX(pa.text) value, COUNT(*) total 
FROM `oc_product` p 
LEFT JOIN `oc_product_attribute` pa ON (p.product_id = pa.product_id) 
LEFT JOIN `oc_attribute` a ON (a.attribute_id = pa.attribute_id) 
LEFT JOIN `oc_attribute_description` ad ON (ad.attribute_id = a.attribute_id) 
LEFT JOIN `oc_product_description` pd ON (p.product_id = pd.product_id) 
LEFT JOIN `oc_product_to_store` p2s ON (p.product_id = p2s.product_id) 
WHERE p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '0' AND pd.language_id = '1' AND pa.language_id = '1' AND ad.language_id = '1' 
GROUP BY lower(pa.text), a.attribute_id HAVING COUNT(*) > 0

результатом запроса является таблица для фильтра товаров

Создал оступ playground базе данных:
https://server78.hosting.reg.r... 0934_forum
USER: u1110934_forum
PASS: forum1221

 

Я думаю это из-за того что запрос перебирает всю таблицу oc_product_attribute без индексов, не знаю как это решить, помогите пожалуйста

 

[chukcha]

Я так и не понял его назначение

[Varov]

33 минуты назад, nikifalex сказал:

mysqli::real_connect(): (HY000/1045): Access denied for user 'u1110934_forum'@'localhost' (using password: YES)

 

поставил пароль: forum1221

[Varov]

33 минуты назад, nikifalex сказал:

запрос ужасен. Что за фильтр такой запрос делает?

В теме journal3 есть фильтр товаров по атрибутам, так вот это он

 

[Varov]

8 минут назад, chukcha сказал:

Я так и не понял его назначение

 

Запрос делает выборку сколько товаров соответствует каждому значению атрибута

[spectre]

похоже на фильтр про

[SooR]

Добавьте отдельных индексов на product_id и attribute_id.

Если длинных текстов хранить не планируется, можно перевести `text` в varchar(255)

[Varov]

Я думаюможно сделать даже просто выгрузку всех атрибутов в одну таблицу, но у меня это тоже получается времени больше 3 секунд выполнение:
 

SELECT Mpa.attribute_id) attribute_id, MAX(ad.name) attribute_name, MAX(pa.text) value, 2 
FROM `oc_product` p 
LEFT JOIN `oc_product_attribute` pa ON (p.product_id = pa.product_id) 
LEFT JOIN `oc_attribute_description` ad ON (ad.attribute_id = pa.attribute_id)  
GROUP BY lower(pa.text)

 

[chukcha]

2 часа назад, Varov сказал:

MAX(a.attribute_id) attribute_id

 

11 минут назад, Varov сказал:

Запрос делает выборку сколько товаров

КАК?

[Varov]

2 минуты назад, SooR сказал:

Добавьте отдельных индексов на product_id и attribute_id.

Если длинных текстов хранить не планируется, можно перевести `text` в varchar(255)

 

Хм, но в таблице  oc_product_attribute ячейки в столбцах product_id и attribute_id повторяются, или один индекс на два столбца?

[Varov]

1 минуту назад, chukcha сказал:

14 минут назад, Varov сказал:

Запрос делает выборку сколько товаров

КАК?

Как понимаю в колонку total записывается количество товаров соответствующих значению атрибута

[chukcha]

3 часа назад, Varov сказал:

MAX(a.attribute_id)

Не понимаю..

[chukcha]

MAX(ad.name) attribute_name

не понимаю

MAX(pa.text) value

Не понимаю, но это еще как-то можно оправдать, но все равно  - не понимаю

[SooR]

16 минут назад, chukcha сказал:

не понимаю

 

Это он так "сгруппировал"

[SooR]

1 час назад, Varov сказал:

Хм, но в таблице  oc_product_attribute ячейки в столбцах product_id и attribute_id повторяются, или один индекс на два столбца?

Отдельно индекс для attribute_id и отдельно для product_id, тот что есть - оставить

[Varov]

3 часа назад, SooR сказал:

Отдельно индекс для attribute_id и отдельно для product_id, тот что есть - оставить

 

Добавил индексы, запрос быстрее не стал, насколько мне известно запрос может работать толко по одному индексу, тут нужен составной, или я ошибаюсь?

[SooR]

7 часов назад, Varov сказал:

 

Добавил индексы, запрос быстрее не стал, насколько мне известно запрос может работать толко по одному индексу, тут нужен составной, или я ошибаюсь?

Попробуйте составной

[Varov]

4 часа назад, nikifalex сказал:

запрос же без параметров. Ну и закэшировать результат и забыть

 

Не кэшируется почему-то, это первый опыт в опенкарт, не подскажете как это сделать ?

[chukcha]

SELECT MAX(a.attribute_id) attribute_id, MAX(ad.name) attribute_name, MAX(pa.text) value, COUNT(*) total 
FROM `oc_product` p 
LEFT JOIN `oc_product_attribute` pa ON (p.product_id = pa.product_id AND pa.language_id = '1') 
LEFT JOIN `oc_attribute` a ON (a.attribute_id = pa.attribute_id) 
LEFT JOIN `oc_attribute_description` ad ON (ad.attribute_id = a.attribute_id AND ad.language_id = '1') 
LEFT JOIN `oc_product_description` pd ON (p.product_id = pd.product_id) 
LEFT JOIN `oc_product_to_store` p2s ON (p.product_id = p2s.product_id) 
WHERE p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '0' AND pd.language_id = '1' 
AND a.attribute_id IS NOT NULL
GROUP BY lower(pa.text), a.attribute_id 
HAVING COUNT(*) > 0

 

[Yoda]

Друзья, вы тут можете индексами увешаться как цыганка бусами.
Толку не будет и вот почему:

 

Базовая таблица у нас  FROM `oc_product`, а групировка идет по вычисляемымы значениям (pa.text) - уже фуллскан и это не базовая таблица. Поэтому какие индексы здесь не выдумывай толку от  них не будет. Так как на операции GROUP BY, они работают удачно только в случае, если у нас поля, по которым происходит группировка принадлежит той же таблице, которая является основной в запросе. 

Ну и COUNT(*) total FROM `oc_product` p в последнем каменте - это просто детский сад.

Какой тут индекс должен сработать?  У нас есть поле * ? 
Вреднейший совет реально. Хотите чтобы работали индексы используйте SELECT COUNT(_field_name_). И никогда SELECT COUNT(*).

 

Единственное правильное решение в этой ситуации - это забить на родной фильтр Journal, купить ocfilter у @SooR и не выдумывать велоспед. У него в фильтре отличное решение, в виде нормализации значений атрибутов и предвычисляемых справочников для подсчета значений. Что в целом для структуры данных атрибутов opencart, пожалуй является наиболее простым и верным подходом в решении вопроса реализации фильтра.

 

Позволю еще немного промо по ocfilter - немного напильника, чуть-чуть хорошего серевера и в магазине на 300 000 товаров, вот такие цифры на категорию:

БЕЗ ГЛОБАЛЬНЫХ КЕШЕЙ!

[chukcha]

1 час назад, SooR сказал:

Попробуйте составной

так там есть составной
product_id attribute_id language_id

[chukcha]

SELECT SQL_NO_CACHE a.attribute_id attr_id, ad.name attribute_name, pa.text val, COUNT(p.product_id) total 
FROM `oc_product` p 
LEFT JOIN `oc_product_attribute` pa ON (p.product_id = pa.product_id AND pa.language_id = '1') 
LEFT JOIN `oc_attribute` a ON (a.attribute_id = pa.attribute_id) 
LEFT JOIN `oc_attribute_description` ad ON (ad.attribute_id = a.attribute_id AND ad.language_id = '1') 
LEFT JOIN `oc_product_description` pd ON (p.product_id = pd.product_id) 
LEFT JOIN `oc_product_to_store` p2s ON (p.product_id = p2s.product_id) 
WHERE p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '0' AND pd.language_id = '1' 
AND a.attribute_id IS NOT NULL
GROUP BY lower(pa.text), a.attribute_id 
HAVING COUNT(*) > 0
ORDER BY attr_id

Убрал агргетатные MAX

Кстати
oc_attribute - здесь лишняя

[Varov]

15 минут назад, chukcha сказал:

SELECT SQL_NO_CACHE a.attribute_id attr_id, ad.name attribute_name, pa.text val, COUNT(p.product_id) total 
FROM `oc_product` p 
LEFT JOIN `oc_product_attribute` pa ON (p.product_id = pa.product_id AND pa.language_id = '1') 
LEFT JOIN `oc_attribute` a ON (a.attribute_id = pa.attribute_id) 
LEFT JOIN `oc_attribute_description` ad ON (ad.attribute_id = a.attribute_id AND ad.language_id = '1') 
LEFT JOIN `oc_product_description` pd ON (p.product_id = pd.product_id) 
LEFT JOIN `oc_product_to_store` p2s ON (p.product_id = p2s.product_id) 
WHERE p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '0' AND pd.language_id = '1' 
AND a.attribute_id IS NOT NULL
GROUP BY lower(pa.text), a.attribute_id 
HAVING COUNT(*) > 0
ORDER BY attr_id

Убрал агргетатные MAX

Кстати
oc_attribute - здесь лишняя

 

Запросто прежнему выполняется больше 3 секунд, возможно @Yoda прав и реально надо поставить другой фильтр и не мучиться с тем что есть

Змінено 16 серпня 2020 користувачем Varov

[Varov]

17 минут назад, chukcha сказал:

Кстати
oc_attribute - здесь лишняя

 

я пробовал убрать 

oc_product

oc_product_to_store

oc_product_description

так как для моих нужд они тоже лишние, скорости не прибавляет, по видимому как и реально идет полный переход таблицы oc_product_attribute

[Varov]

@SooR Насколько ваш фильтр будет болезненно интегрировать в тему journal3? Был ли опыт работы с этой темой?