MySQL Forums :: Newbie :: Query Optimization - order by

Advanced Search

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

Subject Written By Posted
Query Optimization - order by matt erop 01/24/2012 08:00AM
Re: Query Optimization - order by Rick James 01/26/2012 01:22AM

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.