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 =
'[EMAIL PROTECTED]') ) ) 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: [EMAIL PROTECTED]
Hello? Enema Bondage? I'm calling because I want to be happy, I guess ...
_______________________________________________
http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users
Community help: http://wiki.bestpractical.com
Commercial support: [EMAIL PROTECTED]
Discover RT's hidden secrets with RT Essentials from O'Reilly Media.
Buy a copy at http://rtbook.bestpractical.com
We're hiring! Come hack Perl for Best Practical:
http://bestpractical.com/about/jobs.html
