List:General Discussion« Previous MessageNext Message »
From:Suhail Doshi Date:August 16 2009 7:13pm
Subject:Group by optimization
View as plain text  
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

Thread
Group by optimizationSuhail Doshi16 Aug
  • Re: Group by optimizationPeter Brawley16 Aug
    • Re: Group by optimizationSuhail Doshi17 Aug