Re: Optimizing a query with group by and order by count
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