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

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