MySQL Forums
Forum List  »  Optimizer & Parser

Re: Optimizing a query with group by and order by count
Posted by: mansup kulel
Date: March 21, 2011 10:32PM

Hi,
Thank you very much for your reply (sorry due to some misunderstanding as i'm new i happen to make duplicate post)

Here's my table structure

CREATE TABLE IF NOT EXISTS `listing` (
`ID` mediumint(10) unsigned NOT NULL AUTO_INCREMENT,
`Name` varchar(250) DEFAULT NULL,
`Address` text,
`City` varchar(250) DEFAULT NULL,
`State` char(2) DEFAULT NULL,
`Zip` varchar(20) DEFAULT NULL,
`SIC` varchar(250) NOT NULL,
PRIMARY KEY (`ID`),
KEY `City` (`City`),
KEY `Zip` (`Zip`),
KEY `SIC` (`SIC`),
KEY `Name_2` (`Name`),
FULLTEXT KEY `Name` (`Company_Name`),
FULLTEXT KEY `Zip_2` (`Zip`),
FULLTEXT KEY `City_2` (`City`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=2190756 ;

basically the SIC feild is just categories

and i need the following query to be executed very fast

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

and here is the explain

+----+-------------+---------+-------+---------------+------+---------+------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+-------+---------------+------+---------+------+------+----------------------------------------------+
| 1 | SIMPLE | listing | index | NULL | SIC | 252 | NULL | 3800 | Using index; Using temporary; Using filesort |
+----+-------------+---------+-------+---------------+------+---------+------+------+----------------------------------------------+
1 row in set (0.00 sec)


With the above query it takes over 2 Secs to execute
and it's same even without the having statement

and if i don't use the order by statement than only the query seems faster as i want
but i need to have the order by statement.

I guess i need to avoid using filesort to make it faster can you please suggest me something more

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Optimizing a query with group by and order by count
3189
March 21, 2011 10:32PM


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.