[rt-devel] Adding indexes to speed up RT

Nobel Tse 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 mailing list