MySQL Forums
Forum List  »  Newbie

Query Optimization - order by
Posted by: matt erop
Date: January 24, 2012 08:00AM

Hi, sorry to be a typical noob spamming questions but i have honestly searched for days!

I have a pretty basic query

SELECT id, count(*) FROM table GROUP BY id ORDER BY count (*) DESC LIMIT 50

my table has 1 million rows and this query takes over 3 seconds to execute. It can do the query without the ORDER BY (ie just the count of id grouped) in under 0.00 seconds but soon as i try and sort the results it goes down hill...

Is there anyway to speed this up? Am i right in assuming it can do the other things quickly as the table is indexed but when it creates the count(*) field that is NOT indexed and therefore takes A LOT longer to sort then a usual indexed field would?

please help!

Options: ReplyQuote

Written By
Query Optimization - order by
January 24, 2012 08:00AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.