[rt-devel] Slow Queries part3, Groups

[Ruslan U. Zakirov]

Hello. Here is my comments on this.

Robert Spier wrote:
>>yeh, my real problem is I have almost 700,000 tickets and 150,000 users :)
>>so that 1 second blows out a bit :)
> 
> 
> Oy.
Really HUGE.

[snip]
> 
> 
>>>I suspect the real problem here is MySQL's optimizer is falling down.
>>>Which means we may have to take an alternate track.  Anyone want to
>>>try MySQL 4.1?  Or (Jesse?) maybe split the OR's up into a few faster
>>>queries?  Another thought is to replace some of the text strings with
>>>lookup numbers.. but that'll slow other things down.
>>
>>Yep, there are a few places where mysql will refuse to use indexes,
>>I'd say probably in this case, its deciding that the index just
>>doesn't cut the rows down enough and a sequential search will be
>>faster.
> 
> 
> Right.  Thats what the documentation says.  But even when I force
> those indexes, it still doesn't cut the rows down enough.  (Although
> if it maintains the 50% margin on your system.. that might help.)
At first time I was thinking that this query is exactly that situation 
when mySQL optimizer break plan, but for my DB mySQL it's best plan with 
existent indexes.
I've used 'STRAIGHT_JOIN' pragma and play with joins order and default 
plan was the best by 'query time'.

Matthew, could you test next query on your DB and compare time:
SELECT STRAIGHT_JOIN DISTINCT main.*
         FROM
                 ACL ACL_2,
                 Principals Principals_1,
                 Groups main
         WHERE ... ORDER BY main.Name ASC;

This select uses indexes in all joins, but for me it's ~3 times slower 
than native plan with sequential search on Groups table.


[snip]
> 
> (Jesse-	
>     1- Why doesn't this use a recursive search and therefore use
>     CachedGroupMembers?
>     2- IncludeSystemRights doesn't seem to do anything anymore.
>     Should it be excised?
> )
I think that query is broken or do more then it has to:
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;
I don't understand why we need Principals table in it?

If I eliminate Principals_1.id at all and also 'select from' and 
'ACL_2.PrincipalId = Principals_1.id' change to 'ACL_2.PrincipalId = 
main.id' then I have same results, but query is faster.