Peter, I am fairly certain, it's not slow because of the event_id look up but because of the GROUP BY
Suhail On Sun, Aug 16, 2009 at 2:56 PM, Peter Brawley <peter.braw...@earthlink.net>wrote: > Suhail, > > Having problems with this query, any ideas on how to optimize this further? > > Did you try writing the event_ids in your IN() list to a temp table & > joining to that table? > > PB > > ----- > > Suhail Doshi wrote: > > Having problems with this query, any ideas on how to optimize this further? > > mysql> explain SELECT cache_property_str.name as name, > SUM(cache_property_str.tally) as count > FROM cache_property_str > WHERE > cache_property_str.status = 1 AND > cache_property_str.event_id IN > (84007,84862,84965,85356,85453,85659,85874,86049,86319,86451,86571,86740,86800,86966,87138,87233,87720,88015,88179,88359,88517,88694,88805,89026,89164,89277,89396,89698,90002,90384,90428,91561,92128,92339,92743,93006,93227,93645,93755,93844,93966,94120,94330,94487,94712,95068,95301,95439,95677,95822,96138,97151,97362,97512,97771,97986,98419,98642,99033,99291,99601,99835,100529,100695,100883,101070,101976,102435,102705,102864,103098,103303,103415,103612,103799,103841,104422,104792,105027,105218,105526,105689,105909,106173,106311,106459,107118,107320,107662,107970,108155,108379,108418,108618,108779,108960,109506,109691,110067,110469,110698,110806,111201,111286,111641,112174,112375,112568,112656,113094,113248,113344,113449,113561,113909,114170,114322,114432,115059,115146,115244,115541,115689,116305,116405,116762,117148,117296,117389,117504,117779,117945,118285,118447,118571,118752) > GROUP BY cache_property_str.name > ORDER BY NULL > LIMIT 10; > +----+-------------+--------------------+-------+---------------------+---------------------+---------+------+------+------------------------------+ > | id | select_type | table | type | possible_keys > | key | key_len | ref | rows | Extra > | > +----+-------------+--------------------+-------+---------------------+---------------------+---------+------+------+------------------------------+ > | 1 | SIMPLE | cache_property_str | range | property_unique_idx > | property_unique_idx | 5 | NULL | 245 | Using where; Using > temporary | > +----+-------------+--------------------+-------+---------------------+---------------------+---------+------+------+------------------------------+ > 1 row in set (0.00 sec) > > > CREATE TABLE `cache_property_str` ( > `id` int(11) unsigned NOT NULL auto_increment, > `event_id` int(11) unsigned NOT NULL, > `name` binary(16) NOT NULL, > `value` binary(16) NOT NULL, > `tally` bigint(20) unsigned NOT NULL, > `status` tinyint(2) unsigned NOT NULL, > `modified` datetime NOT NULL, > PRIMARY KEY (`id`), > UNIQUE KEY `property_unique_idx` (`event_id`,`status`,`name`,`value`) > ) ENGINE=InnoDB AUTO_INCREMENT=4041064 DEFAULT CHARSET=latin1 > > > > ------------------------------ > No virus found in this incoming message. > Checked by AVG - www.avg.com > Version: 8.5.392 / Virus Database: 270.13.58/2306 - Release Date: 08/16/09 > 06:09:00 > > > >
