[rt-users] Re: query problem after 3.4.5 -> 3.6.4 upgrade

[Brian Kerr]

On 7/24/07, Ruslan Zakirov <ruz at bestpractical.com> wrote:
>   Hello, Brian.
> Ok, I've forgotten about Status field and we're talking only about
> "Requestor.EmailAddress = tom OR Owner = tom". I think that I've found
> problem. Here are three queries I want you to compare. Other user can
> do that too, but please replace "main.Owner = ####" with some valid
> user ID as it's very important for mysql optimizer.

Looks much better, here are the queries in the same order you sent them:

mysql> SELECT SQL_NO_CACHE 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
    ->    LEFT JOIN CachedGroupMembers CachedGroupMembers_2
    ->        ON CachedGroupMembers_2.MemberId = Users_3.id
    ->        AND CachedGroupMembers_2.GroupId = Groups_1.id
    -> WHERE
    ->    main.Status != 'deleted'
    ->    AND ( main.Owner = '58936'
    ->        OR ( Users_3.EmailAddress LIKE '%tom%'
    ->             AND CachedGroupMembers_2.id IS NOT NULL )
    ->    )
    ->    AND main.Type = 'ticket'
    ->    AND main.EffectiveId = main.id;
+-------------------------+
| COUNT(DISTINCT main.id) |
+-------------------------+
|                     729 |
+-------------------------+
1 row in set (2 min 20.39 sec)

mysql> SELECT SQL_NO_CACHE 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
    ->    LEFT JOIN CachedGroupMembers CachedGroupMembers_2
    ->        ON CachedGroupMembers_2.MemberId = Users_3.id
    ->        AND CachedGroupMembers_2.GroupId = Groups_1.id
    -> WHERE
    ->    main.Status != 'deleted'
    ->    AND ( main.Owner = '58936' OR CachedGroupMembers_2.id IS NOT NULL )
    ->    AND Users_3.EmailAddress LIKE '%tom%'
    ->    AND main.Type = 'ticket'
    ->    AND main.EffectiveId = main.id;
+-------------------------+
| COUNT(DISTINCT main.id) |
+-------------------------+
|                     729 |
+-------------------------+
1 row in set (1.17 sec)

mysql> SELECT SQL_NO_CACHE COUNT(DISTINCT main.id)
    -> FROM Tickets main
    ->    JOIN Groups Groups_1
    ->        ON  Groups_1.Domain = 'RT::Ticket-Role'
    ->        AND Groups_1.Type = 'Requestor'
    ->        AND Groups_1.Instance = main.id
    ->    LEFT JOIN CachedGroupMembers CachedGroupMembers_2
    ->        ON CachedGroupMembers_2.GroupId = Groups_1.id
    ->    LEFT JOIN Users Users_3
    ->        ON CachedGroupMembers_2.MemberId = Users_3.id
    -> WHERE
    ->    main.Status != 'deleted'
    ->    AND main.Type = 'ticket'
    ->    AND main.EffectiveId = main.id
    ->    AND ( main.Owner = '58936' OR Users_3.EmailAddress LIKE '%tom%' );
+-------------------------+
| COUNT(DISTINCT main.id) |
+-------------------------+
|                     729 |
+-------------------------+
1 row in set (2.31 sec)