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

[mark matyas]

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