Оптимизация SQL запроса

[Sashan]

Здравствуйте! Имеется вот такие запросы к бд

1.523 сек.
SELECT p.product_id, p.quantity>0 as instock, 

(SELECT AVG(rating) AS total
FROM oc_review r1
WHERE
   r1.product_id = p.product_id AND
   r1.status = '1'
GROUP BY r1.product_id)

AS rating,

(SELECT price
FROM oc_product_discount pd2
WHERE
   pd2.product_id = p.product_id AND
   pd2.customer_group_id = '1' AND
   pd2.quantity = '1' AND
   ((pd2.date_start = '0000-00-00' OR pd2.date_start < NOW()) AND
   (pd2.date_end = '0000-00-00' OR pd2.date_end > NOW()))
ORDER BY pd2.priority ASC, pd2.price ASC
LIMIT 1)

AS discount,

(SELECT price
FROM oc_product_special ps
WHERE
   ps.product_id = p.product_id AND
   ps.customer_group_id = '1' AND
   ((ps.date_start = '0000-00-00' OR ps.date_start < NOW()) AND
   (ps.date_end = '0000-00-00' OR ps.date_end > NOW()))
ORDER BY ps.priority ASC, ps.price ASC
LIMIT 1)

AS special
FROM oc_product_to_category p2c
LEFT JOIN oc_product p ON (p2c.product_id = p.product_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
   pd.language_id = '1' AND
   p.status = '1' AND
   p.date_available <= NOW() AND
   p2s.store_id = '0' AND
   p2c.category_id = '1254'
GROUP BY p.product_id
ORDER BY p.price = 0, p.quantity = 0, p.sort_order ASC, LCASE(pd.name) ASC
LIMIT 24,12

0.542 сек.
SELECT COUNT(DISTINCT p.product_id) AS total 
FROM oc_product_to_category p2c 
LEFT JOIN oc_product p ON (p2c.product_id = p.product_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
   pd.language_id = '1' AND
   p.status = '1' AND
   p.date_available <= NOW() AND
   p2s.store_id = '0' AND
   p2c.category_id = '1254'

 

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

[100napb]

Будет больше информации \ возможности Вам помочь, если Вы покажите план выполнения запроса. Что бы видна была информация о существующих\используемых индексах, кол-ве перебранных строк и прочее. Для этого добавьте перед в самом начале запроса слово explain, выполните скрипт повторно, а результаты прикрепите к посту.

 

P.S. на первый взгляд все джоины выполнены по primary ключам, свойственным таблицам опенкарта. Что лишает надежды на то, что бы решить задачу самой малой кровью =\

P.P.S.: тухнелький, но вариант: заменить now() на конкретную дату. без секунд часов и минут + добавить кэширование запроса средствами ОС или mysql. Быстрее не будет, но будет не каждый раз

[freelancer]

1. можно выкинуть AVG(rating), discount и special подзапросы если они точно не требуются

2. есть трюк с SQL_CALC_FOUND_ROWS - одним запросом достать товары с нужным limit и подсчитать totals

3. убрать join'ы oc_product_to_store, oc_product_description если у вас 1 язык и 1 магазин без поддоменов

4. можно убрать p.date_available если у вас не практикуется ограничение по дате доступности товара

[chukcha]

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

ORDER BY p.price = 0, p.quantity = 0

вот...

[Otvet]

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

вот...

ну тогда и 

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

LCASE(pd.name) ASC

заменить на сортировку по первичному ключу

 

[freelancer]

5 минут назад, Otvet сказал:

ну тогда и 

заменить на сортировку по первичному ключу

 

хорошая шутка. 1 апреля

[chukcha]

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

хорошая шутка. 1 апреля

А очень надо сортировка по имени? (есть же выбор сортировки)

[freelancer]

@chukcha у меня нет данных какие сортировки нужны конечному пользователю. если нужно сортировать товар по наименованию, то чем нам поможет первичный ключ?

[Otvet]

Только что, freelancer сказал:

@chukcha у меня нет данных какие сортировки нужны конечному пользователю. если нужно сортировать товар по наименованию, то чем нам поможет первичный ключ?

тем что это дефолтная досортировка

этот запрос - не сортировка по наименовнию

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

p.sort_order ASC, LCASE(pd.name) ASC

 

 

LCASE(pd.name) → p.product_id

[freelancer]

досортировка хорошее слово. мне нравится

 

я не вижу php кода, но изначально было предположение, что LCASE(pd.name) - и есть цель, с учетом того что 

ORDER BY p.price = 0, p.quantity = 0, // товары с нулевой ценой в конце

p.sort_order ASC, // sort_order почти 100% случает == 0

LCASE(pd.name)  ASC // сама сортировка

[AlexDW]

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

2. есть трюк с SQL_CALC_FOUND_ROWS - одним запросом достать товары с нужным limit и подсчитать totals

 

раньше пользовал

пока не столкнулся пару-тройку раз с багами SQL_CALC_FOUND_ROWS в зависимости от версии mysql

(крайний раз - буквально пару недель назад)

 

можно использовать на каком-то конкретном проекте, с "правильной" версией

но как универсальное решение в модулях - под большим вопросом

[chukcha]

41 минуту назад, freelancer сказал:

 LCASE(pd.name) -

Это дефолт

 

p.sort_order ASC, LCASE(pd.name) ASC

 

Причина понятна -  реляционная база, не гарантирует однозначный  порядок вывода кортежа
Потому  и сортировки

Чтобі уменьшить количество сортировко сортировок

можно сделать финт ушами

SELECT
CASE
        WHEN p.price >0 THEN 9999
        WHEN p.quantity = 0  THEN 99999
        ELSE p.sort_order
    END) AS sort_order

Надо проверить