MySQL very slow with OrderBy

Question

I've been having an issue with one of my queries running very slowly when OrderBy has been applied.

I've already taken a look around and surrounded my query with a SELECT * FROM (sql stuff) OrderBy field. This decreased the time from 22 seconds to 10 seconds, but I really need to get it quicker. The SQL_NO_CACHE was just for testing.

This returns 5866 records in 10 seconds. Removing the orderby returns in 2 seconds.

SELECT 
     SQL_NO_CACHE * 
FROM
     (SELECT 
          SUM(
               MATCH(product_name) 
               AGAINST ('"Jeans"' IN BOOLEAN MODE) + 
               MATCH(store_name) 
               AGAINST ('"Jeans"' IN BOOLEAN MODE)
          ) AS searchScore,
          product_name,
          section_url,
          product_link_url,
          affiliate_store_product_id,
          store_url,
          product_date_added,
          product_image,
          product_image_path,
          product_sale_price,
          product_price,
          product_price_currency,
          product_url,
          product_id,
          product_channel_id,
          store_name,
          product_brand,
          colour_id,
          colour_name 
     FROM
          products 
          INNER JOIN stores 
               ON store_id = product_store_id 
          LEFT OUTER JOIN product_colours 
               ON product_colour_product_id = product_id 
          LEFT OUTER JOIN colours 
               ON colour_id = product_colour_colour_id 
          LEFT OUTER JOIN sections 
               ON product_channel_id = section_id 
          INNER JOIN storeShipping 
               ON storeShipping_store_id = store_id 
          INNER JOIN shipping_locations 
               ON shipping_location_id = storeShipping_shipping_location_id 
          JOIN product_categories 
               ON product_category_product_id = product_id 
          JOIN categories 
               ON category_id = product_category_category_id 
     WHERE (
               MATCH(product_name) AGAINST ('"Jeans"' IN BOOLEAN MODE) 
               OR MATCH(store_name) AGAINST ('"Jeans"' IN BOOLEAN MODE)
          ) 
          AND product_status = 1 
          AND category_status = 1 
          AND product_excluded = 0 
          AND product_feed_status = 1 
          AND store_status = 1 
          AND shipping_location_currency_code = 'AUD' 
          AND product_image_path IS NOT NULL 
          AND (
               product_channel_id = 1 
               OR product_channel_id = 2 
               OR product_channel_id = 3 
               OR product_channel_id = 4
          ) 
     GROUP BY product_url) AS T 
ORDER BY searchScore DESC ;

Here is the explain for the orderby

+----+-------------+--------------------+--------+---------------------------------------------------------------------------------------------------------------------------+------------------------+---------+--------------------------------------------------+--------+----------------------------------------------+
| id | select_type |       table        |  type  |                                                       possible_keys                                                       |          key           | key_len |                       ref                        |  rows  |                    Extra                     |
+----+-------------+--------------------+--------+---------------------------------------------------------------------------------------------------------------------------+------------------------+---------+--------------------------------------------------+--------+----------------------------------------------+
|  1 | PRIMARY     | <derived2>         | ALL    | \N                                                                                                                        | \N                     | \N      | \N                                               |   5866 | Using filesort                               |
|  2 | DERIVED     | products           | ref    | PRIMARY,idx_product,idx_channel,idx_path,idx_store,idx_excluded,idx_status,idx_product_feed_status,idx_product_image_path | idx_status             | 2       |                                                  | 306688 | Using where; Using temporary; Using filesort |
|  2 | DERIVED     | stores             | eq_ref | PRIMARY,idx_storestatus                                                                                                   | PRIMARY                | 4       | products.product_store_id                        |      1 | Using where                                  |
|  2 | DERIVED     | product_categories | ref    | PRIMARY,idx_category,idx_categoryproduct                                                                                  | idx_categoryproduct    | 4       | products.product_id                              |      1 |                                              |
|  2 | DERIVED     | categories         | eq_ref | PRIMARY,NewIndex1                                                                                                         | PRIMARY                | 4       | product_categories.product_category_category_id  |      1 | Using where                                  |
|  2 | DERIVED     | product_colours    | ref    | idx_colourproduct                                                                                                         | idx_colourproduct      | 5       | products.product_id                              |      2 |                                              |
|  2 | DERIVED     | colours            | eq_ref | PRIMARY                                                                                                                   | PRIMARY                | 4       | product_colours.product_colour_colour_id         |      1 |                                              |
|  2 | DERIVED     | storeShipping      | ref    | idx_storeshippingstore,idx_storeshippinglocation                                                                          | idx_storeshippingstore | 5       | stores.store_id                                  |      4 | Using where                                  |
|  2 | DERIVED     | shipping_locations | eq_ref | PRIMARY,idx_shippinglocation                                                                                              | PRIMARY                | 4       | storeShipping.storeShipping_shipping_location_id |      1 | Using where                                  |
|  2 | DERIVED     | sections           | eq_ref | PRIMARY                                                                                                                   | PRIMARY                | 4       | products.product_channel_id                      |      1 |                                              |
+----+-------------+--------------------+--------+---------------------------------------------------------------------------------------------------------------------------+------------------------+---------+--------------------------------------------------+--------+----------------------------------------------+

And without the orderby

+--------+-------------+--------------------+--------+---------------------------------------------------------------------------------------------------------------------------+------------------------+---------+--------------------------------------------------+--------+----------------------------------------------+
|     id | select_type |       table        |  type  |                                                       possible_keys                                                       |          key           | key_len |                       ref                        |  rows  |                    Extra                     |
+--------+-------------+--------------------+--------+---------------------------------------------------------------------------------------------------------------------------+------------------------+---------+--------------------------------------------------+--------+----------------------------------------------+
|      1 | SIMPLE      | products           | ref    | PRIMARY,idx_product,idx_channel,idx_path,idx_store,idx_excluded,idx_status,idx_product_feed_status,idx_product_image_path | idx_status             |       2 | const                                            | 306688 | Using where; Using temporary; Using filesort |
|      1 | SIMPLE      | stores             | eq_ref | PRIMARY,idx_storestatus                                                                                                   | PRIMARY                |       4 | products.product_store_id                        |      1 | Using where                                  |
|      1 | SIMPLE      | product_categories | ref    | PRIMARY,idx_category,idx_categoryproduct                                                                                  | idx_categoryproduct    |       4 | products.product_id                              |      1 |                                              |
|      1 | SIMPLE      | categories         | eq_ref | PRIMARY,NewIndex1                                                                                                         | PRIMARY                |       4 | product_categories.product_category_category_id  |      1 | Using where                                  |
|      1 | SIMPLE      | product_colours    | ref    | idx_colourproduct                                                                                                         | idx_colourproduct      |       5 | products.product_id                              |      2 |                                              |
|      1 | SIMPLE      | colours            | eq_ref | PRIMARY                                                                                                                   | PRIMARY                |       4 | product_colours.product_colour_colour_id         |      1 |                                              |
|      1 | SIMPLE      | storeShipping      | ref    | idx_storeshippingstore,idx_storeshippinglocation                                                                          | idx_storeshippingstore |       5 | stores.store_id                                  |      4 | Using where                                  |
|      1 | SIMPLE      | shipping_locations | eq_ref | PRIMARY,idx_shippinglocation                                                                                              | PRIMARY                |       4 | storeShipping.storeShipping_shipping_location_id |      1 | Using where                                  |
|      1 | SIMPLE      | sections           | eq_ref | PRIMARY                                                                                                                   | PRIMARY                |       4 | products.product_channel_id                      |      1 |                                              |
+--------+-------------+--------------------+--------+---------------------------------------------------------------------------------------------------------------------------+------------------------+---------+--------------------------------------------------+--------+----------------------------------------------+

Anything you guys can do to give me a boost would be great!

Thanks!

Answer 1

I have modified your query a little. Can you try it? Also please INDEX the column product_url this will improve the execution a lot. Also if possible INDEX the columns product_name and store_name.

SELECT SUM(CASE WHEN product_name LIKE '%Jeans%' OR store_name LIKE '%Jeans%' 
                THEN 1 
           ELSE 0 END) AS searchScore,
       product_name, 
       section_url, 
       product_link_url, 
       affiliate_store_product_id, 
       store_url, 
       product_date_added, 
       product_image, 
       product_image_path, 
       product_sale_price, 
       product_price, 
       product_price_currency, 
       product_url, 
       product_id, 
       product_channel_id, 
       store_name, 
       product_brand, 
       colour_id, 
       colour_name 
FROM products 
             INNER JOIN stores ON store_id = product_store_id 
             LEFT OUTER JOIN product_colours ON product_colour_product_id = product_id 
             LEFT OUTER JOIN colours ON colour_id = product_colour_colour_id 
             LEFT OUTER JOIN sections ON product_channel_id = section_id 
             INNER JOIN storeShipping ON storeShipping_store_id = store_id 
             INNER JOIN shipping_locations ON shipping_location_id = storeShipping_shipping_location_id 
             JOIN product_categories ON product_category_product_id = product_id 
             JOIN categories ON category_id = product_category_category_id 
WHERE (product_name LIKE '%Jeans%' OR store_name LIKE '%Jeans%')
AND product_status = 1 
AND category_status = 1 
AND product_excluded = 0 
AND product_feed_status = 1 
AND store_status = 1 
AND shipping_location_currency_code = 'AUD' 
AND product_image_path IS NOT NULL 
AND product_channel_id IN (1, 2, 3, 4) 
GROUP BY product_url
ORDER BY searchScore

Answer 2

You're ordering by searchScore, which is not table field, but some dynamically aggregated value. Therefore MySQL can't use indexes for sorting and uses FileSort instead. This is of course slow.

Now, regarding optimization tips:

1. If your product_url, product_name, store_name are not greater than 255 chars - then use VARCHAR(255) or even CHAR(255) for them. And ensure that product_url is indexed field (not full text index, but normal index). Actually, all 3 fields have to be indexed.

2. Are you sure you need to use MATCH AGAINST construction? It would work faster if you'd use REGEXP there, but I dont know exact requirements.

3. Its again related to p.1 - you have fields as (VAR)CHAR(255) - then add composite index of all the fields in WHERE + GROUP BY, I mean: product_name, store_name, product_status, category_status, product_excluded, product_feed_status, store_status, shipping_location_currency_mode, product_image_path, product_channel_id, product_url. Plus, you haev to have product_url as a separate index.

Hope it will help. After my proposed modifications you should have this query executed in less than one second.