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@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@alink.co.za
Hello? Enema Bondage? I’m calling because I want to be happy, I guess …
Hello, George.
To fix this performance issue you need to create two indexes:
CREATE INDEX LocalUsers1 ON Users(EmailAddress);
CREATE INDEX LocalCGM1 ON CachedGroupMembers(MemberId,GroupId,Disabled);
Please, after each create operation run explain and send me results.On 5/23/06, George Barnett george@alink.co.za wrote:
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@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@alink.co.za
Hello? Enema Bondage? I’m calling because I want to be happy, I guess …
The rt-users Archives
Community help: http://wiki.bestpractical.com
Commercial support: sales@bestpractical.com
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: Careers — Best Practical Solutions
Best regards, Ruslan.