[Rt-devel] Slowest query in our setup

[Ruslan U. Zakirov]

Joop van de Wege wrote:
> Hi All,
> 
> I verified that query against our Oracle9i RT3 instance and I get rather
> decent performance. The problem I see is that a full table scan is done
> over table GROUPS. This is cause by the fact that the index on DOMAIN
> isn't used. If I add a level one index, just using DOMAIN as an indexed
> column performance goes up 3 times and explain plan shows the use of the
> index.
I use MySQL 4.0.x and this index doesn't help.
MySQL 4.0.x doesn't support subqueries and as consequence uses straight 
subsequent joins. One join - one index.
For example in this slow query ACL and Groups are restricted by clause:
(
    ACL_1.PrincipalId = main.id AND ACL_1.PrincipalType = 'Group'
) OR (
    main.Type = ACL_1.PrincipalType
)

This clause can be split in two and optimized with indexies first on 
ACL(PrincipalId, PrincipalType,...) and second on 
ACL(PrincipalType,...). MySQL can use only one index so it doesn't use any.

May be I would try 4.1.x series.
 
 

> I have seen this more often that apps use multiple key indices but that
> often doesn't work, atleast not with Oracle.
I think you can share your set of indices for Oracle.

> 
> On a side note:
> Maybe not everyone knows this but if you use mod_perl and RT(2/3) then
> you can use this in httpd.conf:
> PerlSetEnv DBI_PROFILE DBI::ProfileDumper::Apache  
> At each stop/start of Apache you'll get profiling data in your logs
> directory which you can analyse with dbiprof. If a mod_perl/apache
> process terminaties due to max accesses reached it will also dump its
> profile data.
> This way one can find quite a few interesting things.
I didn't spend much time with this DBI feature.
Please add some notes to:
http://wiki.bestpractical.com/?Debug

> 
> Joop
>