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