Ruslan,
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?
Thoughts? Current EXPLAIN without the Groups2 index is:
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;
+----+-------------+----------------------+--------+--------------------------------+------------+---------+-----------------------------+------+-----------------------------------------------------------+
| id | select_type | table | type | possible_keys
| key | key_len | ref | rows | Extra
|
+----+-------------+----------------------+--------+--------------------------------+------------+---------+-----------------------------+------+-----------------------------------------------------------+
| 1 | SIMPLE | Groups_3 | range | PRIMARY,Groups1
| Groups1 | 139 | NULL | 5 | Using where;
Using index; Using temporary; Using filesort |
| 1 | SIMPLE | main | range | PRIMARY
| PRIMARY | 4 | NULL | 317 | Using where
|
| 1 | SIMPLE | Principals_1 | eq_ref | PRIMARY
| PRIMARY | 4 | rt3.main.id | 1 | Using where;
Distinct |
| 1 | SIMPLE | CachedGroupMembers_2 | ref |
DisGrouMem,CachedGroupMembers3 | DisGrouMem | 10 |
rt3.Groups_3.id,rt3.main.id | 1 | Using where; Using index; Distinct
|
+----+-------------+----------------------+--------+--------------------------------+------------+---------+-----------------------------+------+-----------------------------------------------------------+
4 rows in set (0.00 sec)
Nicola
-----Original Message-----
From: [email protected] on behalf of Ruslan Zakirov
Sent: Fri 6/25/2010 11:05 AM
To: Foggi, Nicola
Cc: [email protected]
Subject: Re: [rt-users] slow mysql query after upgrade from 3.8.6 to 3.8.8
Hello,
Do you use SQLForACLChecks option?
Where is EXPLAIN for this query?
Show indexes from Groups table.
On Fri, Jun 25, 2010 at 8:04 AM, Foggi, Nicola <[email protected]> wrote:
>
> 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
>
--
Best regards, Ruslan.
Discover RT's hidden secrets with RT Essentials from O'Reilly Media.
Buy a copy at http://rtbook.bestpractical.com
