[rt-users] sql generated by search Search - My Tickets

Raed El-Hames rfh at vialtus.com
Tue Feb 9 11:35:55 EST 2010


Hi ;

3.8.7
with Set($UseSQLForACLChecks,'1');

When debugging what sql is generated by what I noticed the system saved 
search Search - My Tickets is generating the following sql:

SELECT DISTINCT main.* FROM Tickets main JOIN Groups Groups_1  ON ( 
Groups_1.Domain = 'RT::Ticket-Role' ) AND ( Groups_1.Instance = main.id 
) LEFT JOIN CachedGroupMembers CachedGroupMembers_2  ON ( 
CachedGroupMembers_2.MemberId = '1766849' ) AND ( 
CachedGroupMembers_2.GroupId = Groups_1.id )  WHERE (main.Status != 
'deleted') AND (main.Owner = '1766849' AND  ( main.Status = 'new' OR 
main.Status = 'open' OR main.Status = 'stalled' )  AND  (  ( main.Owner 
= '1766849' )  OR  ( CachedGroupMembers_2.MemberId IS NOT NULL AND 
Groups_1.Type = 'Requestor' )  OR  ( CachedGroupMembers_2.MemberId IS 
NOT NULL AND Groups_1.Type = 'Cc' )  OR  ( CachedGroupMembers_2.MemberId 
IS NOT NULL AND Groups_1.Type = 'AdminCc' )  ) ) AND (main.Type = 
'ticket') AND (main.EffectiveId = main.id)  ORDER BY main.Id DESC  LIMIT 30

(the above query is requested twice)
I would like to understand why the Cc/Requestor/AdminCc are looked up, 
which make the query much larger, I am certain that its to do with 
UseSQLForACLChecks , which I cannot get my head around, can some one 
explain:

- How UseSQLForACLChecks works, where its generated etc
- Can  I disable this check on certain lookups (ie system saved searches 
for example)

Baring in mind my system have ~ 300 queues and ~ 30m rows in 
CachedGroupMembers , optimising these searches is essential for me.


Any pointers will be appreciated.


Regards;
Roy




More information about the rt-users mailing list