<!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=140310700-27042007><FONT face=Arial
color=#0000ff size=2>Ok, the issue is that MYSQL 5 won't use the index on
main.Name by default (possible keys list PRIMARY only, which is useless for this
ORDER BY clause), which it really needs to do with an ORDER BY clause for
main.Name (or main.RealName as in my example as I have modified the display
code). It is fixed if you force the index use:</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=140310700-27042007><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=140310700-27042007><FONT face=Arial
color=#0000ff size=2>mysql> SELECT DISTINCT main.* FROM Principals
Principals_1, CachedGroupMembers C<BR>achedGroupMembers_2, Groups Groups_3, ACL
ACL_4, Users main FORCE INDEX(Users1) WHERE ((ACL_4.PrincipalType =
Groups_3.Type)) AND ((ACL_4.RightName = 'Own<BR>Ticket')) AND
((CachedGroupMembers_2.MemberId = Principals_1.id)) AND ((Groups_3<BR>.id =
CachedGroupMembers_2.GroupId)) AND ((Principals_1.Disabled = '0')) AND
((P<BR>rincipals_1.PrincipalType = 'User')) AND ((Principals_1.id != '1')) AND
((main.i<BR>d = Principals_1.id)) AND ((ACL_4.ObjectType = 'RT::Queue') OR
(ACL_4.ObjectType<BR> = 'RT::System')) AND ((Groups_3.Domain =
'RT::Queue-Role') OR (Groups_3.Domain<BR>= 'RT::System-Role')) ORDER BY
main.Name ASC;</FONT></SPAN></DIV>
<DIV><SPAN class=140310700-27042007><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=140310700-27042007><FONT face=Arial color=#0000ff size=2>Then
it's nice and fast again. The explain shows that it's still a filesort/temp
query but it does a indexed table scan instead of an unindexed range
scan.</FONT></SPAN></DIV>
<DIV><SPAN class=140310700-27042007><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=140310700-27042007><FONT face=Arial color=#0000ff size=2>I
assume that this would need a SearchBuilder mod to force the use of the index
related to the ORDER BY clause?</FONT></SPAN></DIV>
<DIV><SPAN class=140310700-27042007><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=140310700-27042007><FONT face=Arial color=#0000ff
size=2>PK</DIV>
<DIV dir=ltr align=left><BR><BR></DIV></FONT></SPAN><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>