<!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>UseSQLForACLChecks is set as the default under RT_Config.pm to "Set($UseSQLForACLChecks, undef);"<BR>
<BR>
here is the explain:<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 | main | range | PRIMARY | PRIMARY | 4 | NULL | 316 | Using where; Using temporary; Using filesort |<BR>
| 1 | SIMPLE | Groups_3 | ref | PRIMARY,Groups1,Groups2 | Groups2 | 67 | const | 3992 | Using where; Distinct |<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.Principals_1.id | 1 | Using where; Using index; Distinct |<BR>
+----+-------------+----------------------+--------+--------------------------------+------------+---------+-------------------------------------+------+----------------------------------------------+<BR>
4 rows in set (0.00 sec)<BR>
<BR>
here is the indexes:<BR>
<BR>
mysql> show index from Groups;<BR>
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+<BR>
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |<BR>
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+<BR>
| Groups | 0 | PRIMARY | 1 | id | A | 21563 | NULL | NULL | | BTREE | NULL |<BR>
| Groups | 1 | Groups1 | 1 | Domain | A | 31 | NULL | NULL | YES | BTREE | NULL |<BR>
| Groups | 1 | Groups1 | 2 | Instance | A | 21563 | NULL | NULL | YES | BTREE | NULL |<BR>
| Groups | 1 | Groups1 | 3 | Type | A | 21563 | NULL | NULL | YES | BTREE | NULL |<BR>
| Groups | 1 | Groups1 | 4 | id | A | 21563 | NULL | NULL | | BTREE | NULL |<BR>
| Groups | 1 | Groups2 | 1 | Type | A | 10 | NULL | NULL | YES | BTREE | NULL |<BR>
| Groups | 1 | Groups2 | 2 | Instance | A | 21563 | NULL | NULL | YES | BTREE | NULL |<BR>
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+<BR>
7 rows in set (0.00 sec)<BR>
<BR>
I did find in some trial and error testing if I drop the " OR (Groups_3.Domain = 'RT::System-Role')" from the query it gives the same results in under 1 second vs 11-12 seconds, but bot sure what that OR is adding into the mix. That query is what is performed when I display a ticket through the web interface.<BR>
<BR>
Any help is appreciated!<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>