[rt-devel] Adding indexes to speed up RT
nobelt at team.outblaze.com
Mon Oct 22 04:53:20 EDT 2001
We have been using RT2 for product for quite some time and it's working
great for us. We have close to 80000 tickets and have discovered that
RT gets slower as more tickets are created.
After some digging, we found that the following 2 queries (and other
similar ones) are being generated from the ::SearchBuilder and causing
table scans and thus slowing down the performance of RT and consequently
making it unusable.
1) SELECT distinct main.* FROM Attachments main WHERE ((main.Parent =
'0')) AND ((main.TransactionId = '233472'))
2) SELECT distinct main.* FROM Tickets main WHERE ((main.Status =
'open')OR (main.Status = 'new')) AND ((main.Queue = '16'))
We then created several indexes to the tables in the database and it
showed immediate improvement in speed. Here is a list of indexes which
we created to each table.
Table - index name - column
ACL - idx0 - RightScope, PrincipalId
idx1 - RightScope, RightAppliesTo, RightName, PrincipalType,
Attachments - idx0 - TransactionId, Parent
Tickets - idx0 - id, EffectiveId
idx1 - Status,Queue
Users - idx0 - Name
idx1 - id, EmailAddress
Watchers - idx0 - Type, Scope, Value
Jesse, please consider putting these index creations into your next
rollout as without these the RT system becomes completely unusable with
high number of tickets.
More information about the Rt-devel