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