[Rt-devel] Another sql improvement recommendation for rt3.4.0

[David Kerry]

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 at 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 at 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 at 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