MySQL Forums
Forum List  »  Optimizer & Parser

Optimizing a query with group by and order by count
Posted by: mansup kulel
Date: March 20, 2011 01:32AM

I've a table which has millions of records
the table has cat columin which is varchar(250) NOT NULl and has index

i'm trying to optimize this query

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

it takes about 2.5 secs to execute and uses index, temporary tbl and filesort but without the order by statement it only takes about 0.05 sec only using index

now could you please suggest me what should i do to get the least execution time with the order by statement.

Thank you very much for your time.

Options: ReplyQuote

Written By
Optimizing a query with group by and order by count
March 20, 2011 01:32AM

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.