[rt-devel] Slow Queries part3, Groups

[Ruslan U. Zakirov]

Ruslan U. Zakirov wrote:
> Robert Spier wrote:
> 
>>> 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
>>
>>
>>
>> I don't believe the Principal's stuff is the slow part of this query.
>> (At least not from my analysis of the EXPLAIN output.)  I know the
>> Principal stuff is there for a reason, I believe it has to do with
>> delegation of rights, but Jesse can explain that better.
> 
> Ugh.
> I talk not about perfomance issues. I talk about query itself. It's 
> wrong and only one particular feature save RT from logic breakage of 
> results from this select. This feature is that that each row in 
> Principals table have ObjectId == id.
> Groups.id (in our select main.id) referenced not by Principals.id, but 
> by Principals.ObjectId.
> 
> Open your eyes and look at the query logic and not on how it's fast or no.
>     
>     Best regards and nothing personal. Ruslan.
> 
>>
>>
>>> And at last, query MUST be:
>>> SELECT DISTINCT main.*
>>
>>
>>
>> What did I screw up?
You - nothing. It's Jesse :) as I think.
>>
>> -R

Also second part as I think have to be something like this:
SELECT DISTINCT main.*
          FROM
                  Groups main,
                  ACL ACL_2
          WHERE (
                  (
                          ACL_2.RightName = 'SuperUser' OR
                          ACL_2.RightName = 'OwnTicket'
                  ) AND (
                          (
                                  (
                                          main.Domain = 'RT::Queue-Role' AND
                                          main.Instance = 8
                                  ) OR (
                                          main.Domain = 
'RT::Ticket-Role' AND
                                          main.Instance = 12000
                                  )
                          ) AND
                          main.Type = ACL_2.PrincipalType
                  ) AND (
                          ACL_2.ObjectType = 'RT::System' OR
                          (
                                  ACL_2.ObjectType = 'RT::Queue' AND
                                  ACL_2.ObjectId = 8
                          )
                  )
          );