[rt-devel] Slow Queries part3, Groups

[Ruslan U. Zakirov]

Hello. Again.
Robert Spier wrote:
> Since I obviously can't leave well-enough alone, I futzed a little
> more tonight.
> 
> By splitting the query into two pieces, I can make MySQL's optimizer
> much happier.
> 
> SELECT DISTINCT main.* FROM Groups main, Principals Principals_1, ACL
> ACL_2 WHERE ( ( ACL_2.RightName = 'SuperUser' OR ACL_2.RightName =
> 'OwnTicket' ) 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) AND ( ACL_2.ObjectType = 'RT::System' OR (
> ACL_2.ObjectType = 'RT::Queue' AND ACL_2.ObjectId = 13 ) )
> )
I'm not best of best in DBs, but I want draw your attention to table 
Principals and where clauses of this select.
First of all, we use only id from this table.
Second, in accordance with RT::Group->PrincipalObj
...
   $self->{'PrincipalObj'} = RT::Principal->new($self->CurrentUser);
   $self->{'PrincipalObj'}->LoadByCols('ObjectId' => $self->Id,
                                      'PrincipalType' => 'Group') ;
...
So for this still working only because of this:
select count(*) from Principals where id != ObjectId;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
And at last, query MUST be:
SELECT DISTINCT main.*
         FROM
                 Groups main,
                 Principals Principals_1,
                 ACL ACL_2
         WHERE (
                 (
                         ACL_2.RightName = 'SuperUser' OR
                         ACL_2.RightName = 'OwnTicket'
                 ) 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.ObjectId AND
                         Principals_1.PrincipalType = 'Group'
                 ) AND (
                         ACL_2.ObjectType = 'RT::System' OR
                         (
                                 ACL_2.ObjectType = 'RT::Queue' AND
                                 ACL_2.ObjectId = 8
                         )
                 )
         )

> UNION 
> SELECT DISTINCT main.* FROM Groups main, Principals
> Principals_1, ACL ACL_2 WHERE ( ( ACL_2.RightName = 'SuperUser' OR
> ACL_2.RightName = 'OwnTicket' ) AND ( ( ( 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 ) ) );
> 
[snip]

	Best regards. Ruslan.