What is slowing down this product search MySQL query?

Question

We are using the OpenCart ecommerce platform running on PHP 7.2 with MySQL 5.7.27, with about 5000 products.

We use an extension to search through products in the admin panel and it takes about 70-80 seconds on average to execute the search query.

Raw query:

SELECT
   SQL_CALC_FOUND_ROWS pd.*,
   p.*,
   (
      SELECT
         price 
      FROM
         product_special 
      WHERE
         product_id = p.product_id 
         AND 
         (
            date_start = '0000-00-00' 
            OR date_start < NOW() 
            AND 
            (
               date_end = '0000-00-00' 
               OR date_end > NOW()
            )
         )
      ORDER BY
         priority,
         price LIMIT 1
   )
   AS special_price,
   IF(p.image IS NOT NULL 
   AND p.image <> '' 
   AND p.image <> 'no_image.png', 'Igen', 'Nem') AS image_text,
   IF(p.status, 'Engedélyezett', 'Letiltott') AS status_text,
   GROUP_CONCAT(DISTINCT CONCAT_WS(' > ', fgd.name, fd.name) 
ORDER BY
   CONCAT_WS(' > ', fgd.name, fd.name) ASC SEPARATOR '
 ') AS filter_text, GROUP_CONCAT(DISTINCT fd.filter_id ORDER BY CONCAT_WS(' > ', fgd.name, fd.name) ASC SEPARATOR '_') AS filter, GROUP_CONCAT(DISTINCT cat.name ORDER BY cat.name ASC SEPARATOR ' ') AS category_text, GROUP_CONCAT(DISTINCT cat.category_id ORDER BY cat.name ASC SEPARATOR '_') AS category, GROUP_CONCAT(DISTINCT IF(p2s.store_id = 0, 'Butopêa HU', s.name) SEPARATOR ' ') AS store_text, GROUP_CONCAT(DISTINCT p2s.store_id SEPARATOR '_') AS store FROM product p LEFT JOIN product_description pd ON (p.product_id = pd.product_id AND pd.language_id = '2') LEFT JOIN product_to_category p2c ON (p.product_id = p2c.product_id) LEFT JOIN (SELECT cp.category_id AS category_id, GROUP_CONCAT(cd1.name ORDER BY cp.level SEPARATOR ' > ') AS name FROM category_path cp LEFT JOIN category c ON (cp.path_id = c.category_id) LEFT JOIN category_description cd1 ON (c.category_id = cd1.category_id) LEFT JOIN category_description cd2 ON (cp.category_id = cd2.category_id) WHERE cd1.language_id = '2' AND cd2.language_id = '2' GROUP BY cp.category_id ORDER BY name) AS cat ON (p2c.category_id = cat.category_id) LEFT JOIN product_to_category p2c2 ON (p.product_id = p2c2.product_id) LEFT JOIN product_filter p2f ON (p.product_id = p2f.product_id) LEFT JOIN filter f ON (f.filter_id = p2f.filter_id) LEFT JOIN filter_description fd ON (fd.filter_id = p2f.filter_id AND fd.language_id = '2') LEFT JOIN filter_group_description fgd ON (f.filter_group_id = fgd.filter_group_id AND fgd.language_id = '2') 
   LEFT JOIN
      product_filter p2f2 
      ON (p.product_id = p2f2.product_id) 
   LEFT JOIN
      product_to_store p2s 
      ON (p.product_id = p2s.product_id) 
   LEFT JOIN
      store s 
      ON (s.store_id = p2s.store_id) 
   LEFT JOIN
      product_to_store p2s2 
      ON (p.product_id = p2s2.product_id) 
GROUP BY
   p.product_id 
ORDER BY
   pd.name ASC LIMIT 0,
   190

I tried using MySQL's EXPLAIN functionality to see what's going on, but nothing catches my attention right away:

My test environment is running on Intel NVME, 2666 MHz DDR4 RAM, and i7 8th gen. CPU, and yet it's still very slow.

I appreciate any hints as to what is slowing this query down.

Answer 1

It looks like some many-to-many mappings being used in that SELECT. WooCommerce (and the underlying Wordpress) have an inefficient way of implementing such.

Here is my discussion of how to change the schema to improve performance of wp_postmeta and similar tables (product_to_category and product_to_store): http://mysql.rjweb.org/doc.php/index_cookbook_mysql#speeding_up_wp_postmeta

This may be a bug:

                   ( date_start = '0000-00-00'
                      OR  date_start < NOW()
                      AND  ( date_end = '0000-00-00'
                       OR  date_end > NOW() ) 
                   )

You probably wanted extra parentheses:

                   (       ( date_start = '0000-00-00'
                         OR  date_start < NOW() )
                      AND  ( date_end = '0000-00-00'
                         OR  date_end > NOW() ) 
                   )

Also,

 ( date_start = '0000-00-00' OR  date_start < NOW() )

can be simplified to just

 ( date_start < NOW() )

And, the query seems to have the explode-implode syndrome where the JOINs expand to generate a large temp table, only to have the GROUP BY collapse down to the original size. The workaround is to turn

 GROUP_CONCAT(... foo.x ...) AS blah.
 ...
 LEFT JOIN foo ... ON ...

into

 ( SELECT GROUP_CONCAT(... foo.x ...) FROM foo WHERE ... ) AS blah,

If that eliminates all the LEFT JOINs, then the GROUP BY p.product_id can also be eliminated.

Do not say LEFT JOIN when the 'right' table is not optional. (Instead, say JOIN.)

            LEFT JOIN  category_description cd1  ON (cp.category_id = cd1.category_id)
            WHERE  cd1.language_id = '2'  -- this invalidates the `LEFT`

cd2 seems not to be used except for checking that cd2.language_id = '2'. Consider removing references to it.

This requires two temp tables since they are different:

    GROUP BY  p.product_id
    ORDER BY  pd.name ASC

Am I correct in saying that pd.name is simply the name for p.product_id in 'language' 2? If so, this may be semantically the same, but faster because of eliminating a temp table and sort):

    GROUP BY  pd.name
    ORDER BY  pd.name

Once that is done, it may be better to have INDEX(language_id, name) on pd.

The speedup from LIMIT 0, 190 is mostly eliminated by SQL_CALC_FOUND_ROWS.

Over-normalization led to having the two components of this in separate tables?

CONCAT_WS(' > ', fgd.name, fd.name)