I have a Debian/Lenny server with its RT package (3.6.2), using SQLite, which is the default, and should be enough for my current needs.
Quite quickly, it became very slow. E.g. 'Display' for a ticket took around 8 seconds on an idle server.
I looked around a bit, activated SQL logging, then found out that the SQLite schema has fewer indexes than other schemas. I created the indexes that are in schema.Pg and not in schema.SQLite, and changed one to be as in schema.Pg, and 'Display' is less than 1 second now.
To do this, I did:
sqlite3 /var/lib/dbconfig-common/sqlite3/request-tracker3.6/rtdb sqlite> CREATE INDEX Principals2 ON Principals (ObjectId); sqlite> CREATE INDEX Groups2 On Groups (Type, Instance, Domain); sqlite> CREATE INDEX ACL1 on ACL(RightName, ObjectType, ObjectId,PrincipalType,PrincipalId); sqlite> CREATE INDEX CachedGroupMembers2 on CachedGroupMembers (MemberId); sqlite> CREATE INDEX CachedGroupMembers3 on CachedGroupMembers (GroupId); sqlite> CREATE INDEX DisGrouMem on CachedGroupMembers (GroupId,MemberId,Disabled); sqlite> drop index ObjectCustomFieldValues1; sqlite> CREATE INDEX ObjectCustomFieldValues1 ON ObjectCustomFieldValues (CustomField,ObjectType,ObjectId,Content); sqlite> .quit
The difference between the indexes in the various schemas is still (mostly?) the same as of RT 3.8.4.