Another point regarding slow database access and RT 3.4.0rc1…
I’ve been going through the queries being run, and I think I can
make one simple recommendation to improve the performance of
RT significantly (having spent a fair bit of time with various
flavours of databases over the years).
If possible, avoid using functions on a column in a query.
ie: select * from users where lower(email)=‘abc@bar.com’;
In all the databases I’ve used, the optimizer will not use
any indexes associated with that column as soon as you wrap
it in a function and will do a full table scan.
In the above example, an index defined as:
“create index on users(email)” would not be used for that
query.
There are ways around this (ie: Oracle’s function-based indexes,
but I don’t know if that’s supported by all databases and that’s
a bit of band-aid solution).
The better way is to store the data in a known format (all lowercase
in this example) and do something like this instead:
select * from users where email=lower(‘abc@bar.com’);
I point this out because loading up a ticket used this query:
SELECT DISTINCT main.*
FROM Tickets main, Groups Groups_1, CachedGroupMembers CachedGroupMembers_2,
Users Users_3
WHERE (CachedGroupMembers_2.MemberId = Users_3.id) AND
(Groups_1.id = CachedGroupMembers_2.GroupId) AND
(main.EffectiveId = main.id) AND
(main.Status != ‘deleted’) AND
(main.id = Groups_1.Instance) AND
(((LOWER(Users_3.EmailAddress) = ‘user@abc.com’) AND
(Groups_1.Type = ‘Requestor’)) AND
((main.Status =‘new’) OR (main.Status =‘open’)))
ORDER BY main.Priority DESC LIMIT 10;
It took close to 2 seconds to run on my database (27k users).
Remove the ‘LOWER(Users_3.EmailAddress)’ and the query takes < 10ms because
it uses all the indexes.
David Kerry