Как оптимизировать sql запрос товаров opencart?

Question

[BushaevDenis]

Добрый день, есть магазин на 700000 товаров.
В связи с этим страницы категорий грузятся секунд по 25.
Посмотрев какие запросы медленнее всего, нашёл 2:
getTotalProducts (Около 9 секунд)
getProducts (10-12 секунд)

Сам долгий запрос:

SELECT
   p.product_id,
   (
      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_category_path cp 
   LEFT JOIN
      oc_product_to_category p2c 
      ON (cp.category_id = p2c.category_id) 
   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 cp.path_id = '5661' 
GROUP BY
   p.product_id 
ORDER BY
   p.sort_order ASC,
   LCASE(pd.name) ASC LIMIT 0,
   16

Сервер: 2ядра, 2gb ram.
Вопрос такой: Я знаю, что такое количесто товаро не мало, но всё же, 20 секунд это явно не нормально. Как можно оптимизировать запрос? Из-за какого подзапроса может быть такая шняга?

Comments

[Denis]

Опенкарт не предназначен для такого количества товаров

[BushaevDenis]

Denis, А что предназначено?

[Denis]

BushaevDenis,
Не подскажу (у меня не большой опыт с различными цмс шопов, но за опенкарт я уверен.) обычно под такое количество товаров мутят что-то свое, поскольку все популярные шопы рассчитаны на широкого потребителя и обычно в цмс закладывается куча излишнего функционала чтобы угодить большинству, но из-за этого жертвуется производительность

[BushaevDenis]

Denis, Я бы не сказал что в OC есть куча функционала) Абсолютно базовый функционал)

[Denis]

BushaevDenis,
Согласен, но допустим мы делали шопы где наличие было за 5000000 наименований используя практически обычный хостинг, и в угоду производительности нас не сильно интересовал такой функционал как расчет налога (НДС), бонусных баллов, вариантов, загрузок файлов, мульти-язычность, мульти-валютность и многое другое

Answer 1

[rPman]

Это что за треш запрос вы привели в вопросе?
В нем вложенные подзапросы в select это не просто бад практикс, это пиздец. Это так в opencart сделано или вы добавили? избавляйтесь от этого, понятно почему оно 20 секунд копается.

Как минимум запрос по oc_review выносится легко. С запросами по oc_product_discount и oc_product_special наверное нужно что то делать, там limit стоит, Даже не разбираясь в структуре проекта и прочего, я бы эти данные выносил тригером в соседнюю таблицу (т.е. эти 'тормоза' были бы в момент заливки данных о товарах и скидках в базу), а на запросе брал бы информацию из нее.

Comments

[BushaevDenis]

Это код Opencart. Вот такой вот капец там.
Но, кст, справедливости ради, я убирал подзапросы, и ничего особо не менялось.
Я, к сожалению, знаю только довольно базовые вещи в SQL, хотелось бы разобраться с JOIN`ами.

[BushaevDenis]

Понятно всё стало. Попробую разбить на подзапросы. Можете поподробнее о триггерах?

[rPman]

Принцип прост, если получение данных сложным запросом при каждом обращении - долго, то нужно результаты этих запросов кэшировать, т.е. задача превращается в поиск механизмов своевременного обновления кэша. Один из способов - возложить контроль над тем что кэш будет соответствовать данным - триггеры базы данных, настраиваете вызов обновления кэша на изменение данных, от которых он зависит.

На практике лучше кэшировать не конечные данные, а промежуточные, выбирая их таким образом, чтобы итоговый запрос был максимально оптимальным. Т.е. например вот эти подзапросы с limit (кстати их можно и другим способом оптимизировать но надо разбираться с базой данных более глубоко).