Sql generated by search Search - My Tickets

Hi ;
–If you get users and devel list sorry for the duplicate –
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

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