[Rt-devel] 3.6.4rc1 performance regression on a fairly complex search (CROSS JOIN???)

[Dirk Pape]

Hello Ruslan,

--Am 6. Juni 2007 15:17:09 +0400 schrieb Ruslan Zakirov 
<ruz at bestpractical.com>:

> Can you send EXPLAINs for both they are slightly different? Also, I
> don't remember what version of mysql you're using.

The version is: mysql  Ver 12.22 Distrib 4.0.24, for pc-linux-gnu (i386)

The following explain is for 3.6.3:

mysql> explain SELECT COUNT(DISTINCT main.id) FROM Tickets main JOIN Groups 
Groups_1  ON ( Groups_1.Instance = main.id ) JOIN CachedGroupMembers 
CachedGroupMembers_2 ON ( CachedGroupMembers_2.GroupId != 
CachedGroupMembers_2.MemberId ) AND ( CachedGroupMembers_2.GroupId = 
Groups_1.id ) JOIN Users Users_3  ON ( Users_3.id = 
CachedGroupMembers_2.MemberId )  WHERE (Groups_1.Domain = 
'RT::Ticket-Role') AND (main.Status != 'deleted') AND (Groups_1.Type = 
'Requestor') AND (main.Queue = '57' AND  (  ( Users_3.EmailAddress LIKE 
'%staff@%' OR Users_3.EmailAddress LIKE '%math.fu%' OR Users_3.EmailAddress 
LIKE '%inf.fu%' OR Users_3.EmailAddress LIKE '%mi.fu%' OR 
Users_3.EmailAddress LIKE '%cedis.fu%' OR Users_3.EmailAddress LIKE 
'%zuv.fu%' OR Users_3.EmailAddress LIKE '%pcpool.mi%' )  )  AND  ( 
main.Status = 'new' OR main.Status = 'open' ) ) AND (main.Type = 'ticket') 
AND (main.EffectiveId = 
main.id);+----------------------+--------+------------------------------------+---------------+---------+------------------------------+-------+--------------------------+
| table                | type   | possible_keys                      | key 
| key_len | ref                          | rows  | Extra 
|
+----------------------+--------+------------------------------------+---------------+---------+------------------------------+-------+--------------------------+
| Users_3              | index  | PRIMARY,Users3                     | 
Users4        |     121 | NULL                         | 20436 | Using 
where; Using index |
| CachedGroupMembers_2 | ref    | DisGrouMem,SHREDDER_CGM1           | 
SHREDDER_CGM1 |       5 | Users_3.id                   |     1 | Using 
where; Using index |
| Groups_1             | eq_ref | PRIMARY,Groups1,Groups2            | 
PRIMARY       |       4 | CachedGroupMembers_2.GroupId |     1 | Using 
where              |
| main                 | eq_ref | PRIMARY,Tickets1,Tickets5,Tickets4 | 
PRIMARY       |       4 | Groups_1.Instance            |     1 | Using 
where              |
+----------------------+--------+------------------------------------+---------------+---------+------------------------------+-------+--------------------------+
4 rows in set (0.00 sec)


and the next is for 3.6.4rc1+Ruslan-patch:


mysql> explain SELECT COUNT(DISTINCT main.id) FROM Tickets main CROSS JOIN 
Users Users_3 JOIN Groups Groups_1  ON ( Groups_1.Domain = 
'RT::Ticket-Role' ) AND ( Groups_1.Type = 'Requestor' ) AND ( 
Groups_1.Instance = main.id ) JOIN CachedGroupMembers CachedGroupMembers_2 
ON ( CachedGroupMembers_2.MemberId = Users_3.id ) AND ( 
CachedGroupMembers_2.GroupId = Groups_1.id )  WHERE (main.Status != 
'deleted') AND (main.Queue = '57' AND  (  ( Users_3.EmailAddress LIKE 
'%staff@%' AND CachedGroupMembers_2.id IS NOT NULL )  OR  ( 
Users_3.EmailAddress LIKE '%math.fu%' AND CachedGroupMembers_2.id IS NOT 
NULL )  OR  ( Users_3.EmailAddress LIKE '%inf.fu%' AND 
CachedGroupMembers_2.id IS NOT NULL )  OR  ( Users_3.EmailAddress LIKE 
'%mi.fu%'AND CachedGroupMembers_2.id IS NOT NULL )  OR  ( 
Users_3.EmailAddress LIKE '%cedis.fu%' AND CachedGroupMembers_2.id IS NOT 
NULL )  OR  ( Users_3.EmailAddress LIKE '%zuv.fu%' AND 
CachedGroupMembers_2.id IS NOT NULL )  OR  ( Users_3.EmailAddress LIKE 
'%pcpool.mi%' AND CachedGroupMembers_2.id IS NOT NULL )  )  AND  ( 
main.Status = 'new' OR main.Status = 'open') ) AND (main.Type = 'ticket') 
AND (main.EffectiveId = main.id);
+----------------------+--------+------------------------------------+---------------+---------+------------------------------+-------+--------------------------+
| table                | type   | possible_keys                      | key 
| key_len | ref                          | rows  | Extra 
|
+----------------------+--------+------------------------------------+---------------+---------+------------------------------+-------+--------------------------+
| Users_3              | index  | PRIMARY,Users3                     | 
Users4        |     121 | NULL                         | 20436 | Using 
where; Using index |
| CachedGroupMembers_2 | ref    | PRIMARY,DisGrouMem,SHREDDER_CGM1   | 
SHREDDER_CGM1 |       5 | Users_3.id                   |     1 | Using 
where; Using index |
| Groups_1             | eq_ref | PRIMARY,Groups1,Groups2            | 
PRIMARY       |       4 | CachedGroupMembers_2.GroupId |     1 | Using 
where              |
| main                 | eq_ref | PRIMARY,Tickets1,Tickets5,Tickets4 | 
PRIMARY       |       4 | Groups_1.Instance            |     1 | Using 
where              |
+----------------------+--------+------------------------------------+---------------+---------+------------------------------+-------+--------------------------+
4 rows in set (0.00 sec)

Regards, Dirk.

-- 
Dr. Dirk Pape (eAS - Projektleitung Campus Management)
Freie Universitaet Berlin
Grunewaldstr. 34a, 12165 Berlin
Tel. +49 (0)30 838 75143, Fax. +49 (0)30 838 54654