Optimising query with count, group by and order by

[ibinod]

I've a table that has over a million of records,

 

the table has a column `cat` varchar(250) NOT NULL with index

 

i need to execute a query in this manner

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

 

and it takes about 2.5 sec to execute using index, temp tbl and filesort but when i remove the order by statement it only takes about 0.05 sec to execute with only using index.

 

can someone please explain me what i can i do to minimize the execution time for my query with having orderby

 

Thank you very much

 

[The Little Guy]

Take a look at this article, it may help:

 

http://www.facebook.com/note.php?note_id=206034210932