[rt-users] Identification of a constant running query

[Kevin Falcone]

On Thu, May 15, 2014 at 02:06:05PM -0400, Joe Kirby wrote:
> The following query runs an average of 20,000 times per hour.
> We are unable to determine if this is Quick Search or some other report written that is being refreshed forever.

It's got nothing to do with the Quick Search, note the lack of Queue.

Is it actually slow?  Just because it runs a lot, doesn't mean that
it's inefficient.  It's a query to find out if someone is a member of
a group.  Figure out what group id 919190 is, and show an explain of
the query.

Your RT version is also highly relevant.

-kevin

> SELECT main.*
> FROM
>   ( SELECT DISTINCT main.id
>   FROM Users main
>   JOIN Principals Principals_1
>   ON ( Principals_1.id = main.id )
>   JOIN CachedGroupMembers CachedGroupMembers_2
>   ON ( CachedGroupMembers_2.MemberId     = Principals_1.id )
>   WHERE (Principals_1.Disabled           = '0')
>   AND (CachedGroupMembers_2.GroupId      = '919190')
>   AND (CachedGroupMembers_2.Disabled     = '0')
>   AND (lower(Principals_1.PrincipalType) = 'user')
>   ) distinctquery,
>   Users main
> WHERE (main.id = distinctquery.id)
> ORDER BY main.Name ASC
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 235 bytes
Desc: not available
URL: <http://lists.bestpractical.com/pipermail/rt-users/attachments/20140515/3f39bc69/attachment.sig>