Перейти до вмісту
Пошук в
  • Детальніше...
Шукати результати, які ...
Шукати результати в ...

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


Sashan

Recommended Posts

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

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'

 

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

Надіслати
Поділитися на інших сайтах


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

 

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

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

Надіслати
Поділитися на інших сайтах

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 если у вас не практикуется ограничение по дате доступности товара

Надіслати
Поділитися на інших сайтах

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

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

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

Надіслати
Поділитися на інших сайтах

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

Надіслати
Поділитися на інших сайтах

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

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

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

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

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

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

 

 

LCASE(pd.name) → p.product_id

image.png.6a7875386c9357e122673c75affe421e.png

Надіслати
Поділитися на інших сайтах

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

 

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

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

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

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

Надіслати
Поділитися на інших сайтах

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

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

 

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

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

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

 

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

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

Надіслати
Поділитися на інших сайтах

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

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

Надіслати
Поділитися на інших сайтах

Створіть аккаунт або увійдіть для коментування

Ви повинні бути користувачем, щоб залишити коментар

Створити обліковий запис

Зареєструйтеся для отримання облікового запису. Це просто!

Зареєструвати аккаунт

Вхід

Уже зареєстровані? Увійдіть тут.

Вхід зараз
  • Зараз на сторінці   0 користувачів

    • Ні користувачів, які переглядиють цю сторінку
×
×
  • Створити...

Important Information

На нашому сайті використовуються файли cookie і відбувається обробка деяких персональних даних користувачів, щоб поліпшити користувальницький інтерфейс. Щоб дізнатися для чого і які персональні дані ми обробляємо перейдіть за посиланням . Якщо Ви натиснете «Я даю згоду», це означає, що Ви розумієте і приймаєте всі умови, зазначені в цьому Повідомленні про конфіденційність.