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

[Brian Kerr]

Hi,

This ticket system has been upgraded from 2.0.12 -> 3.4.5 -> 3.6.4.
Mysql 4.1.22, Centos 4.4, apache 2.0.52, mod_fastcgi.

When doing a search by requestor/owner email address now, we are
getting a nasty query produced.  Here are the details of the query.
Let me know if you need any more information.

Thanks,
Brian

# Query_time: 621  Lock_time: 0  Rows_sent: 1  Rows_examined: 420186151
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);

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                           |
1609 | Using index              |
|  1 | SIMPLE      | Groups_1             | ref    | Groups1,Groups2
        | Groups2    |      65 | const                          |
10626 | 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)