<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=Content-Type content="text/html; charset=us-ascii">
<META content="MSHTML 6.00.6000.16414" name=GENERATOR></HEAD>
<BODY>
<DIV><SPAN class=343161603-26042007><FONT face=Arial size=2>I know I asked this 
before but I've been swamped and lost track of there the discussion got 
to.</FONT></SPAN></DIV>
<DIV><SPAN class=343161603-26042007><FONT face=Arial 
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=343161603-26042007><FONT face=Arial size=2>RT 3.6.3, Mysql 
5.0.27. The main ticket search page is really slow to load, typically 45 
seconds, sometimes longer. Problem query and explain below. It's starting to 
become a problem for us. Seems that the first row of the explain output is the 
guilty one. Rows_examined is absurdly high.</FONT></SPAN></DIV>
<DIV><SPAN class=343161603-26042007><FONT face=Arial 
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=343161603-26042007><FONT face=Arial 
size=2>PK</FONT></SPAN></DIV>
<DIV><SPAN class=343161603-26042007><FONT face=Arial 
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=343161603-26042007><FONT face=Arial size=2># Query_time: 
45  Lock_time: 0  Rows_sent: 290  Rows_examined: 
65256162</FONT></SPAN></DIV>
<DIV><SPAN class=343161603-26042007><FONT face=Arial size=2>SELECT DISTINCT 
main.* FROM Users main , Principals Principals_1, CachedGroupMembers 
CachedGroupMembers_2, Groups Groups_3, ACL ACL_4  WHERE 
((ACL_4.PrincipalType = Groups_3.Type)) AND ((ACL_4.RightName = 'OwnTicket')) 
AND ((CachedGroupMembers_2.MemberId = Principals_1.id)) AND ((Groups_3.id = 
CachedGroupMembers_2.GroupId)) AND ((Principals_1.Disabled = '0')) AND 
((Principals_1.PrincipalType = 'User')) AND ((Principals_1.id != '1')) AND 
((main.id = Principals_1.id)) AND ((ACL_4.ObjectType = 'RT::Queue') OR 
(ACL_4.ObjectType = 'RT::System')) AND ((Groups_3.Domain = 'RT::Queue-Role') OR 
(Groups_3.Domain = 'RT::System-Role'))  ORDER BY main.RealName 
ASC;</FONT></SPAN></DIV>
<DIV><SPAN class=343161603-26042007><FONT face=Arial 
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=343161603-26042007><FONT face=Arial 
size=2>*************************** 1. row 
***************************<BR>           
id: 1<BR>  select_type: 
SIMPLE<BR>        table: 
main<BR>         type: 
range<BR>possible_keys: 
PRIMARY<BR>          key: 
PRIMARY<BR>      key_len: 
4<BR>          ref: 
NULL<BR>         rows: 
673<BR>        Extra: Using where; Using 
temporary; Using filesort<BR>*************************** 2. row 
***************************<BR>*************************** 2. row 
***************************<BR>           
id: 1<BR>  select_type: 
SIMPLE<BR>        table: 
Principals_1<BR>         type: 
eq_ref<BR>possible_keys: 
PRIMARY<BR>          key: 
PRIMARY<BR>      key_len: 
4<BR>          ref: 
rt3.main.id<BR>         rows: 
1<BR>        Extra: Using where; 
Distinct<BR>*************************** 3. row 
***************************<BR>           
id: 1<BR>  select_type: 
SIMPLE<BR>        table: 
CachedGroupMembers_2<BR>         type: 
ref<BR>possible_keys: 
DisGrouMem,SHRD_CGM1<BR>          
key: SHRD_CGM1<BR>      key_len: 
5<BR>          ref: 
rt3.main.id<BR>         rows: 
1<BR>        Extra: Using where; Using index; 
Distinct<BR>*************************** 4. row 
***************************<BR>           
id: 1<BR>  select_type: 
SIMPLE<BR>        table: 
ACL_4<BR>         type: 
range<BR>possible_keys: 
ACL1<BR>          key: 
ACL1<BR>      key_len: 
54<BR>          ref: 
NULL<BR>         rows: 
77<BR>        Extra: Using where; Using 
index; Distinct<BR>*************************** 5. row 
***************************<BR>           
id: 1<BR>  select_type: 
SIMPLE<BR>        table: 
Groups_3<BR>         type: 
eq_ref<BR>possible_keys: 
PRIMARY,Groups1,Groups2<BR>          
key: PRIMARY<BR>      key_len: 
4<BR>          ref: 
rt3.CachedGroupMembers_2.GroupId<BR>         
rows: 1<BR>        Extra: Using where; 
Distinct<BR></FONT></SPAN></DIV>
<DIV><SPAN class=343161603-26042007><FONT face=Arial 
size=2></FONT></SPAN> </DIV>
<DIV> </DIV>
<DIV align=left><FONT face=Arial size=2>--</FONT></DIV>
<DIV align=left><FONT face=Arial size=2>Philip Kime</FONT></DIV>
<DIV align=left><FONT face=Arial size=2>NOPS Systems Architect</FONT></DIV>
<DIV align=left><FONT face=Arial size=2>310 401 0407</FONT></DIV>
<DIV> </DIV></BODY></HTML>