ORDER BY Causes MySQL query to become Extremely Slow

Question

I have the following query:

SELECT * 
FROM   products 
   INNER JOIN product_meta 
           ON products.id = product_meta.product_id 
   JOIN sales_rights 
     ON product_meta.product_id = sales_rights.product_id 
WHERE  ( products.categories REGEXP '[[:<:]]5[[:>:]]' ) 
   AND ( active = '1' ) 
   AND ( products.show_browse = 1 ) 
   AND ( product_meta.software_platform_mac IS NOT NULL ) 
   AND ( sales_rights.country_id = '240' 
          OR sales_rights.country_id = '223' ) 
GROUP  BY products.id 
ORDER  BY products.avg_rating DESC
LIMIT  0, 18;

Running the query with the omission of the ORDER BY section and the query runs in ~90ms, with the ORDER BY section and the query takes ~8s.

I've browsed around SO and have found the reason for this could be that the sort is being executed before all the data is returned, and instead we should be running ORDER BY on the result set instead? (See this post: Slow query when using ORDER BY)

But I can't quite figure out the definitive way on how I do this?

Mike Sherrill 'Cat Recall' · Answer 1 · 2012-11-30T15:18:35.720

I've browsed around SO and have found the reason for this could be that the sort is being executed before all the data is returned, and instead we should be running ORDER BY on the result set instead?

I find that hard to believe, but if that's indeed the issue, I think you'll need to do something like this. (Note where I put the parens.)

select * from
(
  SELECT products.id, products.avg_rating 
  FROM   products 
  INNER JOIN product_meta 
          ON products.id = product_meta.product_id 
  JOIN sales_rights 
    ON product_meta.product_id = sales_rights.product_id 
  WHERE  ( products.categories REGEXP '[[:<:]]5[[:>:]]' ) 
   AND ( active = '1' ) 
   AND ( products.show_browse = 1 ) 
   AND ( product_meta.software_platform_mac IS NOT NULL ) 
   AND ( sales_rights.country_id = '240' 
          OR sales_rights.country_id = '223' ) 
  GROUP  BY products.id 
) as X
ORDER  BY avg_rating DESC
LIMIT  0, 18;

Also, edit your question and include a link to that advice. I think many of us would benefit from reading it.

Additional, possibly unrelated issues

Every column used in a WHERE clause should probably be indexed somehow. Multi-column indexes might perform better for this particular query.

The column products.categories seems to be storing multiple values that you filter with regular expressions. Storing multiple values in a single column is usually a bad idea.

MySQL's GROUP BY is indeterminate. A standard SQL statement using a GROUP BY might return fewer rows, and it might return them faster.

Question edited to include link. However, your answer causes the error: `Every derived table must have its own alias` — Which I assume would mean we need to add `AS something` after the closing paren (before `ORDER BY`) however then I get `Duplicate column name 'id'` — Which I assume is due to the tables each having an `id` field, but I'm not sure how to resolve this? — Christian Owens, Nov 30 '12 at 12:49
That link goes to your own question, not the question that says MySQL starts sorting before all the data is returned. You're right about the position of the alias for the derived table. It looks like only the table "products" has a column named "id". Use actual column names instead of `SELECT *` and the fix should be obvious. If that fails, edit your question and post the SQL DDL for all the tables in that query. — Mike Sherrill 'Cat Recall', Nov 30 '12 at 13:07
Apologies for that, clipboard issue. Re-edited with correct link. Will try your suggestion. — Christian Owens, Nov 30 '12 at 13:10
I don't believe this will solve the problem either, but I'm pretty sure this is what you're getting at by "running ORDER BY on the whole result set". — Mike Sherrill 'Cat Recall', Nov 30 '12 at 13:40

score 0 · Answer 2 · answered Nov 30 '12 at 13:56

0

If you can, you may want to index your ID columns so that the query will run quicker. This is a DBA-level solution, rather than a SQL solution - tuning the database will help overall performance.

answered Nov 30 '12 at 13:56

William M-B

321
4
13

All the ID columns are indexed. – Christian Owens Nov 30 '12 at 15:29

score 0 · Accepted Answer · answered Dec 04 '12 at 23:44

The issue in the instance of this query, was that by using GROUP BY and ORDER BY in a query, MySQL is unable to use the index if the GROUP BY and ORDER BY expressions are different.

Related Reading:

ORDER BY Causes MySQL query to become Extremely Slow

3 Answers3