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

[Jesse Vincent]

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

If you look a bit deeper, you'll see that that's being done by
SearchBuilder when a case-insensitive search is called for on a database
that doesn't natively support case-insensitive queries.

> 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.
 
Postgres and Oracle both support functional indexes.

> The better way is to store the data in a known format (all lowercase
> in this example) and do something like this instead:

Except that would mean storing usernames in all lowercase, as well as
all correspondence related to a ticket.

> 
> 
> I point this out because loading up a ticket used this query:

That query sure looks like a search for all tickets with user at abc.com,
not loading a single ticket...What happens if you add a functional
index?


> 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
> 
> _______________________________________________
> Rt-devel mailing list
> Rt-devel at lists.bestpractical.com
> http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-devel
> 

--