[rt-devel] Adding indexes to speed up RT

[Nobel Tse]

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'))

etc..

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,
PrincipalId

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.


Nobel