[rt-users] Slow Query when Displaying "Owners" dropdown (Query Builder, Reply, etc.)

[Drew Barnes]

Remove OwnTicket Right from Everyone group and only assign it to techs/IT
workers.


Quoting mark matyas <mark at library.ucsc.edu>:

> Hello RT Users,
>
> We just upgraded from RT 3.0 to 3.4.4 and everything went fine expect
> for one thing:
> There is a specific query that takes more than 50 Seconds and is
> executed frequently as the results of the Query seems to be the list of
> Owners in the Drop down menu for the Query Builder page or When
> Selecting Reply, etc. So when a user clicks on the Query Builder it
> takes 50 seconds, making it very hard to use sometimes.
> The MySql query cache kicks in after that and subsequent clicks to query
> builder are faster, but the 50 second time still happens quite often as
> the cache gets updated.
> We have close to 100,000 tickets and a few thousand users (autocreated,
> mainly). If I start from a fresh database I don't have this problem,
> it's only after I import our current, relatively large DB.
>
> System:
> RT 3.4.4 + apache2 + fastcgi + mysql 4.1 + HTML::Mason v1.3101 + DBI
> v1.48 + DBIx::SearchBuilder v1.33;
>
>  From the MySQL slow query log:
> # Query_time: 56  Lock_time: 1  Rows_sent: 115  Rows_examined: 208668
> use rt3;
> SELECT DISTINCT main.* FROM Users main , Principals Principals_1, ACL
> ACL_2, Groups Groups_3, CachedGroupMembers CachedGroupMembers_4  WHERE
> ((ACL_2.RightName = 'OwnTicket')) AND ((CachedGroupMembers_4.MemberId =
> Principals_1.id)) AND ((Groups_3.id = CachedGroupMembers_4.GroupId)) AND
> ((Principals_1.Disabled = '0')or(Principals_1.Disabled = '0')) AND
> ((Principals_1.id != '1')) AND ((main.id = Principals_1.id)) AND  ( (
> ACL_2.PrincipalId = Groups_3.id AND ACL_2.PrincipalType = 'Group'
>                  AND (   Groups_3.Domain = 'SystemInternal' OR
> Groups_3.Domain = 'UserDefined' OR Groups_3.Domain = 'ACLEquivalence'))
>                   OR ( ( (Groups_3.Domain = 'RT::Queue-Role' )  )  AND
> Groups_3.Type = ACL_2.PrincipalType) )  AND (ACL_2.ObjectType =
> 'RT::System' OR  (ACL_2.ObjectType = 'RT::Queue') )    ORDER BY
> main.Name ASC;
>
> That query displays valid users that can own a ticket.
>
> Is there a way to optimize this query? Where is the bottleneck here (Why
> so many rows examined)? How can I safely delete Unprivledged users from
> the database to clean it up a bit? Would that help?
> Or, as a last resort, how would I change every "Owner" drop-down to be a
> fill-in text field instead?
>
> I found this old thread
> http://lists.bestpractical.com/pipermail/rt-devel/2003-October/004918.html
> but it was a while ago and it seems like some internals have changed
> since then; also there's no obviuos solution.
>
> Any help would be greatly appreciated.
> Thanks!
>
> Mark Matyas
> Computing and Network Services
> McHenry Library
> University of California, Santa Cruz
> _______________________________________________
> http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users
>
> Be sure to check out the RT Wiki at http://wiki.bestpractical.com
>
> Download a free sample chapter of RT Essentials from O'Reilly Media at
> http://rtbook.bestpractical.com
>
> WE'RE COMING TO YOUR TOWN SOON - RT Training in Amsterdam, Boston and
> San Francisco - Find out more at
> http://bestpractical.com/services/training.html
>