<!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>Ruslan,<BR>
<BR>
So I think I figured it out... the original EXPLAIN was showing it using Groups2 index. I forced a query against the Groups1 index, and the results came back in under 1 second vs 11-15 seconds before. I dropped the Groups2 index for the time being. Maybe with all the re-writes around User_Overlay.pm the indexes were overlooked? Do you know what the Groups2 index was created for?<BR>
<BR>
Thoughts? Current EXPLAIN without the Groups2 index is:<BR>
<BR>
explain 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;<BR>
+----+-------------+----------------------+--------+--------------------------------+------------+---------+-----------------------------+------+-----------------------------------------------------------+<BR>
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |<BR>
+----+-------------+----------------------+--------+--------------------------------+------------+---------+-----------------------------+------+-----------------------------------------------------------+<BR>
| 1 | SIMPLE | Groups_3 | range | PRIMARY,Groups1 | Groups1 | 139 | NULL | 5 | Using where; Using index; Using temporary; Using filesort |<BR>
| 1 | SIMPLE | main | range | PRIMARY | PRIMARY | 4 | NULL | 317 | Using where |<BR>
| 1 | SIMPLE | Principals_1 | eq_ref | PRIMARY | PRIMARY | 4 | rt3.main.id | 1 | Using where; Distinct |<BR>
| 1 | SIMPLE | CachedGroupMembers_2 | ref | DisGrouMem,CachedGroupMembers3 | DisGrouMem | 10 | rt3.Groups_3.id,rt3.main.id | 1 | Using where; Using index; Distinct |<BR>
+----+-------------+----------------------+--------+--------------------------------+------------+---------+-----------------------------+------+-----------------------------------------------------------+<BR>
4 rows in set (0.00 sec)<BR>
<BR>
<BR>
Nicola<BR>
<BR>
-----Original Message-----<BR>
From: ruslan.zakirov@gmail.com on behalf of Ruslan Zakirov<BR>
Sent: Fri 6/25/2010 11:05 AM<BR>
To: Foggi, Nicola<BR>
Cc: rt-users@lists.bestpractical.com<BR>
Subject: Re: [rt-users] slow mysql query after upgrade from 3.8.6 to 3.8.8<BR>
<BR>
Hello,<BR>
<BR>
Do you use SQLForACLChecks option?<BR>
Where is EXPLAIN for this query?<BR>
Show indexes from Groups table.<BR>
<BR>
On Fri, Jun 25, 2010 at 8:04 AM, Foggi, Nicola <NFOGGI@depaul.edu> wrote:<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 ON<BR>
> ( CachedGroupMembers_2.MemberId = Principals_1.id ) JOIN Groups Groups_3 ON<BR>
> ( Groups_3.id = CachedGroupMembers_2.GroupId ) WHERE (Principals_1.Disabled<BR>
> = '0') AND (Principals_1.id != '1') AND (Principals_1.PrincipalType =<BR>
> 'User') AND ((Groups_3.Domain = 'RT::Queue-Role' AND Groups_3.Instance =<BR>
> '3') OR (Groups_3.Domain = 'RT::System-Role')) AND (Groups_3.Type =<BR>
> '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>
> 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>
<BR>
<BR>
--<BR>
Best regards, Ruslan.<BR>
<BR>
</FONT>
</P>
</BODY>
</HTML>