[rt-users] Performance-Bug in SelfService when updated from 3.6.1 to 3.6.3

[Dirk Pape]

Hello,

I recently upgraded our test system from 3.6.1 to 3.6.3 and I observed a 
performance problem in SelfService which is new. SelfService/index.html 
takes more than 15 minutes to load on our server where RT.

I tracked this issue down to a change in SelfService/Elements/MyRequest.

In 3.6.1 and before SelfService only searched for Tickets of which the User 
is requestor.
In 3.6.3 it searches for Tickets where user ist Requestor, AdminCc or Cc of.

This search is not well performed by my mysql db server (Ver 12.22 Distrib 
4.0.24, for pc-linux-gnu (i386)) as reported by me in other tickets before. 
I changed SelfService/Elements/MyRequest only to include Tickets with 
requestor and the page is performing well again.

All indexes are as specified by the default installation. I tested some 
additional indexes whithout any success.

Is this a known issue only applying to mysql? Only to mysql 4? Or why does 
no others report this error?

Here is the query and the "explaination":

# Query_time: 805  Lock_time: 0  Rows_sent: 1  Rows_examined: 204821648
SELECT COUNT(DISTINCT main.id) FROM (((((((((Tickets main  JOIN Groups 
Groups_4  ON ( Groups_4.Instance = main.id))  JOIN Groups Groups_7  ON ( 
Groups_7.Instance = main.id))  LEFT JOIN CachedGroupMembers 
CachedGroupMembers_5  ON ((CachedGroupMembers_5.GroupId != 
CachedGroupMembers_5.MemberId)) AND (  CachedGroupMembers_5.GroupId = 
Groups_4.id))  JOIN Groups Groups_1  ON ( Groups_1.Instance = main.id)) 
LEFT JOIN CachedGroupMembers CachedGroupMembers_8  ON ( 
CachedGroupMembers_8.GroupId = Groups_7.id) AND ( 
(CachedGroupMembers_8.GroupId != CachedGroupMembers_8.MemberId)))  LEFT 
JOIN Users Users_6  ON ( Users_6.id = CachedGroupMembers_5.MemberId))  LEFT 
JOIN Users Users_9  ON ( Users_9.id = CachedGroupMembers_8.MemberId))  LEFT 
JOIN CachedGroupMembers CachedGroupMembers_2  ON ( 
CachedGroupMembers_2.GroupId = Groups_1.id) AND ( 
(CachedGroupMembers_2.GroupId != CachedGroupMembers_2.MemberId)))  LEFT 
JOIN Users Users_3  ON ( Users_3.id = CachedGroupMembers_2.MemberId)) 
WHERE ((Groups_1.Domain = 'RT::Ticket-Role')) AND ((Groups_1.Type = 
'Requestor')) AND ((Groups_4.Domain = 'RT::Ticket-Role')) AND 
((Groups_4.Type = 'Cc')) AND ((Groups_7.Domain = 'RT::Ticket-Role')) AND 
((Groups_7.Type = 'AdminCc')) AND ((main.EffectiveId = main.id)) AND 
((main.Status != 'deleted')) AND ((main.Type = 'ticket')) AND ( (  ( 
(Users_3.EmailAddress = '') )  OR  ( (Users_6.EmailAddress = '') )  OR  ( 
(Users_9.EmailAddress = '') )  )  AND  ( (main.Status = 'open') OR 
(main.Status = 'new') OR (main.Status = 'stalled') ) );

+----------------------+--------+-----------------+------------+---------+-------------------------------+-------+--------------------------+
| table                | type   | possible_keys   | key        | key_len | 
ref                           | rows  | Extra                    |
+----------------------+--------+-----------------+------------+---------+-------------------------------+-------+--------------------------+
| Groups_1             | ref    | Groups1,Groups2 | Groups2    |      65 | 
const                         | 51330 | Using where              |
| main                 | eq_ref | PRIMARY         | PRIMARY    |       4 | 
Groups_1.Instance             |     1 | Using where              |
| Groups_7             | ref    | Groups1,Groups2 | Groups1    |      65 | 
const                         | 72594 | Using where; Using index |
| CachedGroupMembers_8 | ref    | DisGrouMem      | DisGrouMem |       5 | 
Groups_7.id                   |     1 | Using index              |
| Groups_4             | ref    | Groups1,Groups2 | Groups1    |      65 | 
const                         | 72594 | Using where; Using index |
| CachedGroupMembers_5 | ref    | DisGrouMem      | DisGrouMem |       5 | 
Groups_4.id                   |     1 | Using index              |
| Users_6              | eq_ref | PRIMARY         | PRIMARY    |       4 | 
CachedGroupMembers_5.MemberId |     1 |                          |
| Users_9              | eq_ref | PRIMARY         | PRIMARY    |       4 | 
CachedGroupMembers_8.MemberId |     1 |                          |
| CachedGroupMembers_2 | ref    | DisGrouMem      | DisGrouMem |       5 | 
Groups_1.id                   |     1 | Using index              |
| Users_3              | eq_ref | PRIMARY         | PRIMARY    |       4 | 
CachedGroupMembers_2.MemberId |     1 | Using where              |
+----------------------+--------+-----------------+------------+---------+-------------------------------+-------+--------------------------+
10 rows in set (0.05 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