MySQL Forums
Forum List  »  Optimizer & Parser

optimising query with group by order by count
Posted by: mansup kulel
Date: March 20, 2011 01:22AM

Hi, I'm new here so please accept my apologies if i didn't make the post correctly.

Basically I'm trying to work with a table which has millions of records

and i need to perform the following query

the cat column is varchar(250) NOT NULL and has an index

SELECT `cat`, COUNT(`cat`) as total FROM `products` GROUP BY `SIC` HAVING `total` > 1 ORDER BY `total` DESC LIMIT 0, 200

it takes about 2. secs to execute and uses index, temporary table and filesort

and when i just execute the same query without ORDER BY it only take about 0.05 sec to execute as it only uses index

Could you please asist me on how can i optimise the query which will get about about 0.05 execution time and will not use filesort

Thank you very much for your time.

Options: ReplyQuote

Written By
optimising query with group by order by count
March 20, 2011 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.