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

[mark matyas]

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


That option is not set. The only thing I have set in Everyone under 
System groups is 'Create Ticket'; and under Roles, I have the Requestor 
set to 'Reply to Ticket' and 'Modify Ticket'.

-mark



 >>
 >>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