[rt-users] slow mysql query after upgrade from 3.8.6 to 3.8.8

[Foggi, Nicola]

hey everyone,

after upgrading from 3.8.6 to 3.8.8 we're getting a slow query on this query:

use rt3;
SELECT DISTINCT main.* FROM Users main JOIN Principals Principals_1  ON ( Principals_1.id = main.id ) JOIN CachedGroupMembers CachedGroupMembers_2  ON ( CachedGroupMembers_2.MemberId = Principals_1.id ) JOIN Groups Groups_3  ON ( Groups_3.id = CachedGroupMembers_2.GroupId )  WHERE (Principals_1.Disabled = '0') AND (Principals_1.id != '1') AND (Principals_1.PrincipalType = 'User') AND ((Groups_3.Domain = 'RT::Queue-Role' AND Groups_3.Instance = '3') OR (Groups_3.Domain = 'RT::System-Role')) AND (Groups_3.Type = 'AdminCc')  ORDER BY main.Name ASC;
# Time: 100624 22:44:20
# User at Host: rt_user[rt_user] @ rt.internal [10.12.10.72]
# Query_time: 13  Lock_time: 0  Rows_sent: 1  Rows_examined: 0
SELECT GET_LOCK('Apache-Session-dc95ab57bb8d19e23fa6fa70314e3c0e', 3600);
# Time: 100624 22:49:28

when loading any ticket page.  I've verified the cachedgroupmembers3 index is in place:

show index from CachedGroupMembers;
...
| CachedGroupMembers |          1 | CachedGroupMembers3 |            1 | MemberId          | A         |       36038 |     NULL | NULL   | YES  | BTREE      | NULL    | 
| CachedGroupMembers |          1 | CachedGroupMembers3 |            2 | ImmediateParentId | A         |       36038 |     NULL | NULL   | YES  | BTREE      | NULL    | 


but still extremely slow... any ideas?  it's pretty bad...

Nicola
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.bestpractical.com/pipermail/rt-users/attachments/20100624/81867264/attachment.htm>