SQLiteIndexes

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.