[rt-devel] Slow Queries part3, Groups

[Robert Spier]

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 ) )
)
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 ) ) );

Adding this index makes the second half even happier:
 CREATE INDEX Groups9 ON Groups (Type,Domain,Instance);

(The first half deals with global and group rights.  The second half
deals with individual ticket ("role") rights, like AdminCC and Owner.

The fun part:
    1- UNION is not standardized between databases.  
    2- SearchBuilder doesn't know about UNION
    3- The code that builds this query is already a mess of special
    cases and "cheating".

My current possible solution is to add some sort of 'preload' to
RT::SearchBuilder (or just RT::Groups) so you can build a search and
get all the values for the iterator preloaded.. then Groups::WithRight
would just (simply) perform two searches and set the iterator values
to the results of both.  The question, of course, is what would that
break.

Another solution, which might be cheating, is to add another argument
to WithRight -- IncludeRoleRights, default to 1.  (Going back to the
original reason here of building a list of possible owners) While
"Owner" implies certain rights, the list of possible owners comes from
the Queue, so there's no reason to even perform the Role search
(second half above).  You then test for IncludeRoleRights on the line
( if ( defined $args{'Object'} ) { ) that guards the building of the
RoleRight part of the query.

A little wave of the magic wand, and simple searches run faster.  tada!

-R


(Note, the maximum value in the 'rows' column is now 869.. much different than the 50 or 90k we had before.)

 +--------------+--------+---------------------------------------------------------+---------+---------+---------------------+------+-------------------------------------------+
| table        | type   | possible_keys                                           | key     | key_len | ref                 | rows | Extra                                     |
+--------------+--------+---------------------------------------------------------+---------+---------+---------------------+------+-------------------------------------------+
| ACL_2        | range  | ACL1                                                    | ACL1    |      79 | NULL                |   12 | Using where; Using index; Using temporary |
| Principals_1 | eq_ref | PRIMARY                                                 | PRIMARY |       4 | ACL_2.PrincipalId   |    1 | Using index                               |
| main         | eq_ref | PRIMARY,Groups1,Groups3,Groups4                         | PRIMARY |       4 | Principals_1.id     |    1 | Using where                               |
| ACL_2        | range  | ACL1                                                    | ACL1    |      54 | NULL                |   12 | Using where; Using index; Using temporary |
| main         | ref    | PRIMARY,Groups1,Groups2,Groups8,Groups9,Groups3,Groups4 | Groups9 |      65 | ACL_2.PrincipalType |  869 | Using where                               |
| Principals_1 | eq_ref | PRIMARY                                                 | PRIMARY |       4 | main.id             |    1 | Using index; Distinct                     |
+--------------+--------+------------------------