Slow performance of combined MySQL queries

Question

I have this query with multiple subqueries which runs quite slow.

SELECT DISTINCT pav.products_options_values_id, 
                pav.products_options_values_name, 
                pav.products_options_values_sort_order 
FROM            products_stock ps, 
                products_options_values pav, 
                ( 
                                SELECT DISTINCT pa.products_id, 
                                                pov.products_options_values_id, 
                                                pov.products_options_values_name, 
                                                pa.options_values_price, 
                                                pa.price_prefix 
                                FROM            products_attributes pa, 
                                                products_options_values pov, 
                                                ( 
                                                                SELECT DISTINCT p.products_image,
                                                                                p.products_quantity,
                                                                                p.products_status,
                                                                                m.manufacturers_id,
                                                                                p.products_id,
                                                                                p.products_date_added,
                                                                                p.products_subimage1,
                                                                                pd.products_name,
                                                                                p.products_price,
                                                                                p.products_length,
                                                                                p.products_width,
                                                                                p.products_height,
                                                                                p.products_tax_class_id,IF(s.status, s.specials_new_products_price, NULL)             AS specials_new_products_price,
                                                                                IF(s.status, s.specials_new_products_price, p.products_price)                         AS final_price,
                                                                                IF(p.clearance_price < p.products_cost*2.25, p.clearance_price, p.products_cost*2.25) AS sorting_price
                                                                FROM            products p 
                                                                LEFT JOIN       manufacturers m
                                                                using          (manufacturers_id)
                                                                LEFT JOIN       specials s 
                                                                ON              p.products_id = s.products_id
                                                                LEFT JOIN       products_attributes pa
                                                                ON              p.products_id = pa.products_id
                                                                LEFT JOIN       products_options po
                                                                ON              pa.options_id = po.products_options_id
                                                                LEFT JOIN       products_options_values pov
                                                                ON              pa.options_values_id = pov.products_options_values_id ,
                                                                                products_description pd,
                                                                                categories c,
                                                                                products_to_categories p2c
                                                                WHERE           p.products_status = '1'
                                                                AND             p.products_id = pd.products_id
                                                                AND             pd.language_id = '1'
                                                                AND             p.products_id = p2c.products_id
                                                                AND             p2c.categories_id = c.categories_id
                                                                AND             (( 
                                                                                                                pd.products_name LIKE '%a%'
                                                                                                OR              po.products_options_name LIKE '%a%'
                                                                                                OR              pov.products_options_values_name LIKE '%a%'
                                                                                                OR              pd.products_description LIKE '%a%') )
                                                                ORDER BY        p.products_id DESC) m
                                WHERE           m.products_id = pa.products_id 
                                AND             pa.options_id = 1
                                AND             pa.options_values_id = pov.products_options_values_id
                                AND             pov.language_id = '1' 
                                GROUP BY        pov.products_options_values_id 
                                ORDER BY        pov.products_options_values_sort_order ASC) q
WHERE           q.products_id = ps.products_id 
AND             ps.products_stock_attributes = concat('1-', pav.products_options_values_id) 
AND             ps.products_stock_quantity > 0 
ORDER BY        pav.products_options_values_sort_order ASC

This is EXPLAIN result:

Been trying to optimize it for hours, but I probably misread the EXPLAIN information because no matter what I do, it doesn't seem to make it faster, so I am reaching out for help from the experts here.

What can be the cause of it becoming so slow and what should I do to make it fast?

Answer 1

First, I cleaned up the query using consistent JOIN clauses vs comma listed tables. Next, your inner-most query has an order by which apparently is not being utilized to any benefit. Order by will typically cause big delay in queries if it cant be optimized.

SELECT DISTINCT 
      pav.products_options_values_id,
      pav.products_options_values_name,
      pav.products_options_values_sort_order
   FROM
      products_stock ps
         JOIN products_options_values pav
            ON ps.products_stock_attributes = concat('1-', pav.products_options_values_id) 
            AND ps.products_stock_quantity > 0 
         JOIN ( SELECT DISTINCT 
                      pa.products_id, 
                      pov.products_options_values_id, 
                      pov.products_options_values_name, 
                      pa.options_values_price, 
                      pa.price_prefix 
                   FROM
                      products_attributes pa
                         JOIN products_options_values pov
                            ON pa.options_values_id = pov.products_options_values_id
                            AND pov.language_id = '1' 
                         JOIN ( SELECT DISTINCT 
                                      p.products_image,
                                      p.products_quantity,
                                      p.products_status,
                                      p.manufacturers_id,
                                      p.products_id,
                                      p.products_date_added,
                                      p.products_subimage1,
                                      pd.products_name,
                                      p.products_price,
                                      p.products_length,
                                      p.products_width,
                                      p.products_height,
                                      p.products_tax_class_id,
                                      IF(s.status, s.specials_new_products_price, NULL ) 
                                         AS specials_new_products_price,
                                      IF(s.status, s.specials_new_products_price, p.products_price )
                                         AS final_price,
                                      IF( p.clearance_price < p.products_cost * 2.25, p.clearance_price, p.products_cost * 2.25 ) 
                                         AS sorting_price
                                   FROM
                                      products p 
                                         JOIN products_description pd
                                            ON p.products_id = pd.products_id
                                            AND pd.language_id = '1'

THIS SECTION CAN BE REMOVED              JOIN products_to_categories p2c
                                            AND p.products_id = p2c.products_id
                                            JOIN categories c
                                               ON p2c.categories_id = c.categories_id
                                         LEFT JOIN manufacturers m
UP TO THIS LINE                              ON P.manufacturers_id = m.manufacturers_id

                                         LEFT JOIN specials s 
                                            ON p.products_id = s.products_id
                                         LEFT JOIN products_attributes pa
                                            ON p.products_id = pa.products_id
                                            LEFT JOIN products_options po
                                               ON pa.options_id = po.products_options_id
                                            LEFT JOIN products_options_values pov
                                               ON pa.options_values_id = pov.products_options_values_id,
                                   WHERE
                                          p.products_status = '1'
                                      AND (    pd.products_name LIKE '%a%'
                                            OR pd.products_description LIKE '%a%'
                                            OR po.products_options_name LIKE '%a%'
                                            OR pov.products_options_values_name LIKE '%a%' )
                                   ORDER BY
                                      p.products_id DESC) m 
                   WHERE
                          pa.products_id = m.products_id
                      AND pa.options_id = 1
                   GROUP BY
                      pov.products_options_values_id 
                   ORDER BY
                      pov.products_options_values_sort_order ASC) q
            ON ps.products_id = q.products_id 
   ORDER BY
      pav.products_options_values_sort_order ASC

You have a join to your categories table, but not pulling any values or other criteria to return columns. I have thus removed as it is unnecessary. WITH that gone, I then looked at your Products_To_Categories table AND that too has no bearing in the query other than an extra join not being used anywhere else and thus removed. The Manufacturers table is also not required as all you are getting is the manufacturer's ID, which exists on the products table, so yet ANOTHER table not required

Now on to indexes. I would look at the following indexes for your tables

table                   index
products                ( products_status, products_id )
products_description    ( products_id, language_id )
products_to_categories  ( products_id, categories_id ) 
categories              ( categories_id )
manufacturers           ( manufacturers_id )
specials                ( products_id, status, specials_new_products_price )
products_attributes     ( products_id, options_id, options_values_id )
products_options        ( products_options_id, products_options_name )
products_options_values ( products_options_values_id, products_options_values_name )

This is just a first pass at the review. Now I want to take it one additional step to possibly remove a layer of nested queries. Your INNER-MOST query does a join to "products_options_values" but ONLY for Language ID = 1. Why would you not add that column criteria to the inner query. Then add the pov.products_options_values_id and pov.products_options_values_name columns to your inner query and language ID to the inner query and you don't need to re-join to them again. They can be referenced via the "m." alias of the inner-most query. vs the rejoin.

Similarly, your inner-most query does a LEFT JOIN to the products_attributes, but outside the "m" alias query result applies a WHERE clause ONLY of options_id = 1. This doesn't make sense. Why not just add that too to the inner-most query. Eliminate the extra levels would probably help a lot as you are limiting down a pull of all records if you are only looking for things like Language ID = 1, or Options_ID = 1.

Again, this formatted query IS the same context as yours, just structured slightly different and shorter indentation to see better the context and nested requirements.

FEEDBACK FROM CHAT

select 
      PQ.*
   from
    ( SELECT STRAIGHT_JOIN DISTINCT 
          p.products_id, 
          p.products_image, 
          p.products_quantity, 
          p.products_status, 
          p.manufacturers_id, 
          p.products_date_added, 
          p.products_subimage1, 
          pd.products_name, 
          p.products_price, 
          p.products_length, 
          p.products_width, 
          p.products_height, 
          pov.products_options_values_id, 
          pov.products_options_values_name, 
          pov.language_id, 
          pov.products_options_values_sort_order, 
          p.products_tax_class_id,
          IF(s.status, s.specials_new_products_price, NULL) 
             AS specials_new_products_price, 
          IF(s.status, s.specials_new_products_price, p.products_price) 
             AS final_price, 
          IF(p.clearance_price < p.products_cost*2.25, p.clearance_price, p.products_cost*2.25) 
             AS sorting_price 
       FROM 
          products_attributes pa 
             JOIN products_options_values pov 
                ON pa.options_values_id = pov.products_options_values_id
                AND pov.language_id = '1' 
                LEFT JOIN products_options po 
                   ON pa.options_id = po.products_options_id 
             JOIN products p 
                ON pa.products_id = p.products_id
                AND p.products_status = '1' 
                JOIN products_description pd
                   ON p.products_id = pd.products_id 
                   AND pd.language_id = '1' 
                JOIN products_to_categories p2c 
                   ON p.products_id = p2c.products_id 
                   JOIN categories c
                      ON p2c.categories_id = c.categories_id 
                LEFT JOIN manufacturers m 
                   using (manufacturers_id) 
                LEFT JOIN specials s 
                   ON p.products_id = s.products_id 
       WHERE 
              pa.options_id = '1' 
          AND (  pd.products_name LIKE '%a%' 
              OR po.products_options_name LIKE '%a%' 
              OR pov.products_options_values_name LIKE '%a%' 
              OR pd.products_description LIKE '%a%'))  PQ
   order by 
      PQ.Products_id

Also, note, I removed the final "Order by" clause