Hello.
SELECT + EXPLAIN attached.
Fix to issue with Queue-Roles(Queue CC/AdminCc rights was not inherited
by tickets) broke optimisation ability.
Best regards. Ruslan.
ownership.sql (1.78 KB)
Hello.
SELECT + EXPLAIN attached.Fix to issue with Queue-Roles(Queue CC/AdminCc rights was not inherited
by tickets) broke optimisation ability.
Can you list off the indices you have on groups?
Jesse Vincent wrote:
Hello.
SELECT + EXPLAIN attached.Fix to issue with Queue-Roles(Queue CC/AdminCc rights was not inherited
by tickets) broke optimisation ability.Can you list off the indices you have on groups?
show keys from Groups;
| Non_unique | Key_name | Seq_in_index | Column_name | Cardinality |
| 0 | PRIMARY | 1 | id | 211821 |
| 1 | Groups1 | 1 | Domain | 17 |
| 1 | Groups1 | 2 | Instance | 105910 |
| 1 | Groups1 | 3 | Type | 211821 |
| 1 | Groups1 | 4 | id | 211821 |
| 1 | Groups2 | 1 | Type | 17 |
| 1 | Groups2 | 2 | Instance | 211821 |
| 1 | Groups2 | 3 | Domain | 211821 |
| 1 | Groups4 | 1 | Type | 69 |
Ruslan U. Zakirov wrote:
Jesse Vincent wrote:
Hello.SELECT + EXPLAIN attached.
Fix to issue with Queue-Roles(Queue CC/AdminCc rights was not
inherited by tickets) broke optimisation ability.Can you list off the indices you have on groups?
IMHO indicies can’t help here cause it’s mysql 4.0.20 that doesn’t
support subqueries and because of OR clause mysql can’t use index in
Groups <=> ACL join at all.
But as I can see if you do:
UPDATE ACL a, Groups g SET a.PrincipalType = ‘UserEquiv’ WHERE g.Type =
‘UserEquiv’ AND g.id = a.PrincipalId;
UPDATE Groups SET Type = ‘Group’ WHERE Type = ‘’;
Then query could be changed:
SELECT DISTINCT main.*
FROM Groups main, Principals Principals_2, ACL ACL_1
WHERE
( main.id = Principals_2.id) AND
((ACL_1.RightName = ‘OwnTicket’)OR(ACL_1.RightName = ‘SuperUser’)) AND
((Principals_2.Disabled = ‘0’)) AND
main.Type = ACL_1.PrincipalType AND
( (
ACL_1.PrincipalId = main.id AND
( main.Domain = ‘SystemInternal’ OR main.Domain = ‘UserDefined’
OR main.Domain = ‘ACLEquivalence’)
) OR (
( (main.Domain = ‘RT::Queue-Role’ AND main.Instance = 9) OR (
main.Domain = ‘RT::Ticket-Role’ AND main.Instance = 47279) )
) )
AND (ACL_1.ObjectType = ‘RT::System’ OR (ACL_1.ObjectType = ‘RT::Queue’
AND ACL_1.ObjectId = 9) )
ORDER BY main.Name ASC;
| table | type | possible_keys | key
| key_len | ref | rows | Extra
|
| ACL_1 | range | ACL1,ACLFKI1,ACL2,ACL3,ACL4 | ACL1
| 54 | NULL | 11 | Using where; Using
temporary; Using filesort |
| main | ref | PRIMARY,Groups1,Groups2,Groups4,Groups5 |
Groups2 | 65 | ACL_1.PrincipalType | 491 | Using where
|
| Principals_2 | eq_ref | PRIMARY |
PRIMARY | 4 | main.id | 1 | Using where; Distinct
|
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 have seen this more often that apps use multiple key indices but that
often doesn’t work, atleast not with 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.
Joop
Joop van de Wege JoopvandeWege@mococo.nl
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:
Request Tracker Wiki
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.
If the index was:
ACL(PrincipalType, PrincipalId)
it may be able to use it for both.
(Index Prefix Matching)