Group by optimization

--0016e64135b6a8e99704714714b0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

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 =3D 1 AND
cache_property_str.event_id IN
(84007,84862,84965,85356,85453,85659,85874,86049,86319,86451 ,86571,86740,86=
800,86966,87138,87233,87720,88015,88179,88359,88517,88694,88 805,89026,89164=
,89277,89396,89698,90002,90384,90428,91561,92128,92339,92743 ,93006,93227,93=
645,93755,93844,93966,94120,94330,94487,94712,95068,95301,95 439,95677,95822=
,96138,97151,97362,97512,97771,97986,98419,98642,99033,99291 ,99601,99835,10=
0529,100695,100883,101070,101976,102435,102705,102864,103098 ,103303,103415,=
103612,103799,103841,104422,104792,105027,105218,105526,1056 89,105909,10617=
3,106311,106459,107118,107320,107662,107970,108155,108379,10 8418,108618,108=
779,108960,109506,109691,110067,110469,110698,110806,111201, 111286,111641,1=
12174,112375,112568,112656,113094,113248,113344,113449,11356 1,113909,114170=
,114322,114432,115059,115146,115244,115541,115689,116305,116 405,116762,1171=
48,117296,117389,117504,117779,117945,118285,118447,118571,1 18752)
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=3DInnoDB AUTO_INCREMENT=3D4041064 DEFAULT CHARSET=3Dlatin1

--0016e64135b6a8e99704714714b0--

Re: Group by optimization

--------------010003040406080201000400
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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,10270 5,102864,103098,103303,103415,103612,103799,103841,104422,10 4792,105027,105218,105526,105689,105909,106173,106311,106459 ,107118,107320,107662,107970,108155,108379,108418,108618,108 779,108960,109506,109691,110067,110469,110698,110806,111201, 111286,111641,112174,112375,112568,112656,113094,113248,1133 44,113449,113561,113909,114170,114322,114432,115059,115146,1 15244,115541,11568
9,116305,116405,116762,117148,117296,117389,117504,117779,11 7945,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
>
>

--------------010003040406080201000400--

Re: Group by optimization

--0016e644cf06e9525404714aed1d
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

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
wrote:

> Suhail,
>
> Having problems with this query, any ideas on how to optimize this furthe=
r?
>
> 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 furthe=
r?
>
> 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 =3D 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,891=
64,89277,89396,89698,90002,90384,90428,91561,92128,92339,927 43,93006,93227,=
93645,93755,93844,93966,94120,94330,94487,94712,95068,95301, 95439,95677,958=
22,96138,97151,97362,97512,97771,97986,98419,98642,99033,992 91,99601,99835,=
100529,100695,100883,101070,101976,102435,102705,102864,1030 98,103303,10341=
5,103612,103799,103841,104422,104792,105027,105218,105526,10 5689,105909,106=
173,106311,106459,107118,107320,107662,107970,108155,108379, 108418,108618,1=
08779,108960,109506,109691,110067,110469,110698,110806,11120 1,111286,111641=
,112174,112375,112568,112656,113094,113248,113344,113449,113 561,113909,1141=
70,114322,114432,115059,115146,115244,115541,115689,116305,1 16405,116762,11=
7148,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=3DInnoDB AUTO_INCREMENT=3D4041064 DEFAULT CHARSET=3Dlatin1
>
>
>
> ------------------------------
> 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/0=
9 06:09:00
>
>
>
>

--0016e644cf06e9525404714aed1d--