How to optimize sql query for opencart products?

Question

opencart

Good afternoon, there is a store with 700,000 items.
In this regard, category pages are loaded in 25 seconds.
Looking at which queries are the slowest, I found 2:
getTotalProducts (About 9 seconds)
getProducts (10-12 seconds)
The long query itself:

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

Server: 2 cores, 2gb ram.
The question is this: I know that such a quantity of goods is not small, but still, 20 seconds is clearly not normal. How can a query be optimized? Because of what subquery can be such shnyag?

Answer 1

What kind of trash request did you cite in the question?
In it, nested subqueries in select is not just bad practice, it's fucked up. Is this how it is done in opencart or did you add it? get rid of it, it's clear why it digs for 20 seconds.
At least the request for oc_review is easy to make. With requests for oc_product_discount and oc_product_special, you probably need to do something, there is a limit. Even without understanding the structure of the project and other things, I would take this data out as a trigger in the adjacent table (i.e. these 'brakes' would be at the time of uploading data about goods and discounts to the database), and on request would take information from it.