[rt-devel] Slow Queries part3, Groups

[Matthew Watson]

Heya,

specs : rt 3.0.5 , mysql 4.0.15a-standard-log, freebsd 4.7

 I'm about 3/4 of the way through my conversion from rt2-rt3. and noticing 
that rt3
is a bit faster for certain things (searching by requestor). one very slow 
query i'm
seeing is when looking for privileged users that a ticket may be assigned 
to, this is what i'm
getting (apologies if this wraps abit weird)


mysql> explain SELECT DISTINCT main.* FROM Groups main, Principals 
Principals_1, ACL ACL_2  WHERE ((ACL_2.RightName = 
'OwnTicket')OR(ACL_2.RightName = 'SuperUser')) AND
    ->           ( (    ACL_2.PrincipalId = Principals_1.id
    ->              AND ACL_2.PrincipalType = 'Group'
    ->              AND (   main.Domain = 'SystemInternal'
    ->                   OR main.Domain = 'UserDefined'
    ->                   OR main.Domain = 'ACLEquivalence')
    ->              AND main.id = Principals_1.id)
    ->             OR ( ( (main.Domain = 'RT::Queue-Role' AND main.Instance 
= 13)  OR ( main.Domain = 'RT::Ticket-Role' AND main.Instance = 363628)  ) 
AND main.Type = ACL_2.PrincipalType AND main.id = Principals_1.id) )
    ->          AND (ACL_2.ObjectType = 'RT::System'  OR (ACL_2.ObjectType 
= 'RT::Queue' AND ACL_2.ObjectId = 13) )  ORDER BY main.Name ASC
    -> ;
+--------------+--------+-------------------------+---------+---------+----
-----+---------+-----------------------------------------------------------+
| table        | type   | possible_keys           | key     | key_len | ref 
| rows    | Extra                                                     |
+--------------+--------+-------------------------+---------+---------+----
-----+---------+-----------------------------------------------------------+
| ACL_2        | range  | ACL1                    | ACL1    |      54 | 
NULL    |      21 | Using where; Using index; Using temporary; Using 
filesort |
| main         | ALL    | PRIMARY,Groups1,Groups2 | NULL    |    NULL | 
NULL    | 1599452 | Using where 
|
| Principals_1 | eq_ref | PRIMARY                 | PRIMARY |       4 | 
main.id |       1 | Using where; Using index; Distinct 
|
+--------------+--------+-------------------------+---------+---------+----
-----+---------+-----------------------------------------------------------+
3 rows in set (0.00 sec)

I'm guessing a index om Groups.Domain will improve things a bit (currently 
importing, so I don't really want to generate an
index at the moment, as it will lock the tables).

Anyone else getting this?

Thanks

Matthew Watson
Netspace Online Systems.