<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
<META NAME="Generator" CONTENT="MS Exchange Server version 6.5.7655.8">
<TITLE>RE: [rt-users] slow mysql query after upgrade from 3.8.6 to 3.8.8</TITLE>
</HEAD>
<BODY>
<!-- Converted from text/plain format -->
<BR>
<P><FONT SIZE=2>I forgot to include the top part with the query stats:<BR>
<BR>
# Time: 100624 22:44:19<BR>
# User@Host: rt_user[rt_user] @ rt.internal [10.12.10.72]<BR>
# Query_time: 12 Lock_time: 0 Rows_sent: 8 Rows_examined: 3314678<BR>
use rt3;<BR>
SELECT DISTINCT main.* FROM Users main JOIN Principals Principals_1 ON ( Principals_1.id = main.id ) JOIN CachedGroupMembers CachedGroupMem<BR>
bers_2 ON ( CachedGroupMembers_2.MemberId = Principals_1.id ) JOIN Groups Groups_3 ON ( Groups_3.id = CachedGroupMembers_2.GroupId ) WHER<BR>
E (Principals_1.Disabled = '0') AND (Principals_1.id != '1') AND (Principals_1.PrincipalType = 'User') AND ((Groups_3.Domain = 'RT::Queue-Ro<BR>
le' AND Groups_3.Instance = '3') OR (Groups_3.Domain = 'RT::System-Role')) AND (Groups_3.Type = 'AdminCc') ORDER BY main.Name ASC;<BR>
<BR>
Is there a reason it's looking at 3314678 rows for what returns the user listing?<BR>
<BR>
Nicola<BR>
<BR>
2010/6/25 Foggi, Nicola <NFOGGI@depaul.edu><BR>
<BR>
><BR>
> hey everyone,<BR>
><BR>
> after upgrading from 3.8.6 to 3.8.8 we're getting a slow query on this<BR>
> query:<BR>
><BR>
> use rt3;<BR>
> SELECT DISTINCT main.* FROM Users main JOIN Principals Principals_1 ON (<BR>
> Principals_1.id = main.id ) JOIN CachedGroupMembers CachedGroupMembers_2<BR>
> ON ( CachedGroupMembers_2.MemberId = Principals_1.id ) JOIN Groups Groups_3<BR>
> ON ( Groups_3.id = CachedGroupMembers_2.GroupId ) WHERE<BR>
> (Principals_1.Disabled = '0') AND (Principals_1.id != '1') AND<BR>
> (Principals_1.PrincipalType = 'User') AND ((Groups_3.Domain =<BR>
> 'RT::Queue-Role' AND Groups_3.Instance = '3') OR (Groups_3.Domain =<BR>
> 'RT::System-Role')) AND (Groups_3.Type = 'AdminCc') ORDER BY main.Name ASC;<BR>
> # Time: 100624 22:44:20<BR>
> # User@Host: rt_user[rt_user] @ rt.internal [10.12.10.72]<BR>
> # Query_time: 13 Lock_time: 0 Rows_sent: 1 Rows_examined: 0<BR>
> SELECT GET_LOCK('Apache-Session-dc95ab57bb8d19e23fa6fa70314e3c0e', 3600);<BR>
> # Time: 100624 22:49:28<BR>
><BR>
> when loading any ticket page. I've verified the cachedgroupmembers3 index<BR>
> is in place:<BR>
><BR>
> show index from CachedGroupMembers;<BR>
> ...<BR>
> | CachedGroupMembers | 1 | CachedGroupMembers3 | 1 |<BR>
> MemberId | A | 36038 | NULL | NULL | YES |<BR>
> BTREE | NULL |<BR>
> | CachedGroupMembers | 1 | CachedGroupMembers3 | 2 |<BR>
> ImmediateParentId | A | 36038 | NULL | NULL | YES |<BR>
> BTREE | NULL |<BR>
><BR>
><BR>
> but still extremely slow... any ideas? it's pretty bad...<BR>
><BR>
> Nicola<BR>
><BR>
><BR>
> Discover RT's hidden secrets with RT Essentials from O'Reilly Media.<BR>
> Buy a copy at <A HREF="http://rtbook.bestpractical.com">http://rtbook.bestpractical.com</A><BR>
><BR>
<BR>
</FONT>
</P>
</BODY>
</HTML>