<!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
style="WORD-WRAP: break-word; -khtml-nbsp-mode: space; -khtml-line-break: after-white-space">
<DIV dir=ltr align=left><SPAN class=406123822-26042007><FONT face=Arial
color=#0000ff size=2>The problem seems to be caused by the ORDER BY clause. If I
remove this, it's very fast.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=406123822-26042007><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=406123822-26042007><FONT face=Arial
color=#0000ff size=2>PK</FONT></SPAN></DIV><BR>
<DIV class=OutlookMessageHeader lang=en-us dir=ltr align=left>
<HR tabIndex=-1>
<FONT face=Tahoma size=2><B>From:</B> Jesse Vincent
[mailto:jesse@bestpractical.com] <BR><B>Sent:</B> Thursday, April 26, 2007 6:00
AM<BR><B>To:</B> Todd Chapman<BR><B>Cc:</B> Philip Kime; RT
Users<BR><B>Subject:</B> Re: [rt-users] Slow ticket search page becoming a
problem<BR></FONT><BR></DIV>
<DIV></DIV>That looks suspiciously like the problem I'd been talking to you
about before, Todd.
<DIV><BR>
<DIV>
<DIV>On Apr 25, 2007, at 11:27 PM, Philip Kime wrote:</DIV><BR
class=Apple-interchange-newline>
<BLOCKQUOTE type="cite">
<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>
<DIV style="MARGIN: 0px">_______________________________________________</DIV>
<DIV style="MARGIN: 0px"><A
href="http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users">http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users</A></DIV>
<DIV style="MIN-HEIGHT: 14px; MARGIN: 0px"><BR></DIV>
<DIV style="MARGIN: 0px">Community help: <A
href="http://wiki.bestpractical.com">http://wiki.bestpractical.com</A></DIV>
<DIV style="MARGIN: 0px">Commercial support: <A
href="mailto:sales@bestpractical.com">sales@bestpractical.com</A></DIV>
<DIV style="MIN-HEIGHT: 14px; MARGIN: 0px"><BR></DIV>
<DIV style="MIN-HEIGHT: 14px; MARGIN: 0px"><BR></DIV>
<DIV style="MARGIN: 0px">Discover RT's hidden secrets with RT Essentials from
O'Reilly Media.<SPAN class=Apple-converted-space> </SPAN></DIV>
<DIV style="MARGIN: 0px">Buy a copy at <A
href="http://rtbook.bestpractical.com">http://rtbook.bestpractical.com</A></DIV></BLOCKQUOTE></DIV><BR></DIV></BODY></HTML>