[rt-users] ticket taking a long time to draw

[George Barnett]

Hi,

Tickets are taking a very long time to draw on our RT install.  After a 
bit of digging, I've found it's the 'More about XYZ' box that lists 
other tickets the user has open.

This query:

SELECT DISTINCT main.* FROM (((Tickets main  JOIN Groups Groups_1  ON ( 
Groups_1.Instance = main.id))  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 
((main.EffectiveId = main.id)) AND ((main.Status != 'deleted')) AND 
((main.Type = 'ticket')) AND ( (  ( (Users_3.EmailAddress = 
'foo.bar at test.com') )  ) AND ( (main.Status = 'new')OR(main.Status = 
'open') ) )  ORDER BY main.Priority DESC  LIMIT 10;

We have around 800K tickets in the database and this is causing the 
select to take about 16 seconds.  The DB server isn't small either, it's 
a 4 way opteron.

after doing an explain select, I noticed the row estimate of 230K
+----+-------------+----------------------+--------+-----------------+------------+---------+-----------------------------------+--------+----------------------------------------------+ 
| id | select_type | table                | type   | possible_keys   | 
key        | key_len | ref                               | rows   | 
Extra                            | 
+----+-------------+----------------------+--------+-----------------+------------+---------+-----------------------------------+--------+----------------------------------------------+ 
|  1 | SIMPLE      | Groups_1             | ref    | Groups1,Groups2 | 
Groups2    |      65 | const                             | 231336 | 
Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | main                 | eq_ref | PRIMARY         | 
PRIMARY    |       4 | rt3.Groups_1.Instance             |      1 | 
Using where                            |
|  1 | SIMPLE      | CachedGroupMembers_2 | ref    | DisGrouMem      | 
DisGrouMem |       5 | rt3.Groups_1.id                   |      1 | 
Using index; Distinct                        |
|  1 | SIMPLE      | Users_3              | eq_ref | PRIMARY,Users4  | 
PRIMARY    |       4 | rt3.CachedGroupMembers_2.MemberId |      1 | 
Using where; Distinct                        |
+----+-------------+----------------------+--------+-----------------+------------+---------+-----------------------------------+--------+----------------------------------------------+

Hence I have added an index on Groups(Domain,Type) which has lowered 
this number to 20k, however it still takes ages.

Is there something I'm missing?

rt 3.4.5
searchbuilder 1.37





-- 
George Barnett
Reality Engineer

m: (+44) 797 457 1868
e: george at alink.co.za

Hello?  Enema Bondage?  I'm calling because I want to be happy, I guess ...