I forgot to include the top part with the query stats: # Time: 100624 22:44:19 # u...@host: rt_user[rt_user] @ rt.internal [10.12.10.72] # Query_time: 12 Lock_time: 0 Rows_sent: 8 Rows_examined: 3314678 use rt3; SELECT DISTINCT main.* FROM Users main JOIN Principals Principals_1 ON ( Principals_1.id = main.id ) JOIN CachedGroupMembers CachedGroupMem bers_2 ON ( CachedGroupMembers_2.MemberId = Principals_1.id ) JOIN Groups Groups_3 ON ( Groups_3.id = CachedGroupMembers_2.GroupId ) WHER E (Principals_1.Disabled = '0') AND (Principals_1.id != '1') AND (Principals_1.PrincipalType = 'User') AND ((Groups_3.Domain = 'RT::Queue-Ro le' AND Groups_3.Instance = '3') OR (Groups_3.Domain = 'RT::System-Role')) AND (Groups_3.Type = 'AdminCc') ORDER BY main.Name ASC;
Is there a reason it's looking at 3314678 rows for what returns the user listing? Nicola 2010/6/25 Foggi, Nicola <[email protected]> > > 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 > # u...@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 > > > Discover RT's hidden secrets with RT Essentials from O'Reilly Media. > Buy a copy at http://rtbook.bestpractical.com >
Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com
