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

[Ruslan Zakirov]

Ok, equiv patch is in the repo. Thanks, Dirk, for reporting,
investigating and testing :)

On 6/7/07, Dirk Pape <pape-rt at inf.fu-berlin.de> wrote:
> 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
>


-- 
Best regards, Ruslan.