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

[Brian Kerr]

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
    ->    LEFT JOIN CachedGroupMembers CachedGroupMembers_2
    ->        ON CachedGroupMembers_2.MemberId = Users_3.id
    ->        AND CachedGroupMembers_2.GroupId = Groups_1.id
    -> WHERE main.Status != 'deleted'
    ->
    -> AND ( ( Users_3.EmailAddress LIKE '%username%' AND
    -> CachedGroupMembers_2.id IS NOT NULL )
    ->    OR main.Status = 'new' OR main.Status = 'open')
    ->    AND (main.Type = 'ticket')
    ->    AND (main.EffectiveId = main.id);
+----+-------------+----------------------+--------+---------------------------+------------+---------+--------------------------------+-------+--------------------------+
| id | select_type | table                | type   | possible_keys
        | key        | key_len | ref                            | rows
 | Extra                    |
+----+-------------+----------------------+--------+---------------------------+------------+---------+--------------------------------+-------+--------------------------+
|  1 | SIMPLE      | Users_3              | index  | NULL
        | Users4     |     121 | NULL                           |
1685 | Using index              |
|  1 | SIMPLE      | Groups_1             | ref    |
Groups1,Groups2,idx_RT_1 | Groups2    |      65 | const
          | 38962 | Using where; Using index |
|  1 | SIMPLE      | main                 | eq_ref |
PRIMARY,Tickets4,Tickets5 | PRIMARY    |       4 |
rt3.Groups_1.Instance          |     1 | Using where              |
|  1 | SIMPLE      | CachedGroupMembers_2 | ref    |
DisGrouMem,GrouMem        | DisGrouMem |      10 |
rt3.Groups_1.id,rt3.Users_3.id |     1 | Using where; Using index |
+----+-------------+----------------------+--------+---------------------------+------------+---------+--------------------------------+-------+--------------------------+
4 rows in set (0.00 sec)

mysql> explain SELECT 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
    ->        AND Users_3.EmailAddress LIKE '%username%'
    -> WHERE main.Status != 'deleted'
    -> AND ( Users_3.id IS NOT NULL );
+----+-------------+----------------------+--------+---------------------------+------------+---------+-----------------------------------+-------+--------------------------+
| id | select_type | table                | type   | possible_keys
        | key        | key_len | ref                               |
rows  | Extra                    |
+----+-------------+----------------------+--------+---------------------------+------------+---------+-----------------------------------+-------+--------------------------+
|  1 | SIMPLE      | Groups_1             | ref    |
Groups1,Groups2,idx_RT_1 | Groups2    |      65 | const
             | 39468 | Using where; Using index |
|  1 | SIMPLE      | main                 | eq_ref |
PRIMARY,Tickets4,Tickets5 | PRIMARY    |       4 |
rt3.Groups_1.Instance             |     1 | Using where              |
|  1 | SIMPLE      | CachedGroupMembers_2 | ref    |
DisGrouMem,GrouMem        | DisGrouMem |       5 | rt3.Groups_1.id
              |     1 | Using index              |
|  1 | SIMPLE      | Users_3              | eq_ref | PRIMARY,Users3
        | PRIMARY    |       4 | rt3.CachedGroupMembers_2.MemberId |
  1 | Using where              |
+----+-------------+----------------------+--------+---------------------------+------------+---------+-----------------------------------+-------+--------------------------+
4 rows in set (0.00 sec)