<HTML><BODY style="word-wrap: break-word; -khtml-nbsp-mode: space; -khtml-line-break: after-white-space; ">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-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">_______________________________________________</DIV><DIV style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 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="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; min-height: 14px; "><BR></DIV><DIV style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">Community help: <A href="http://wiki.bestpractical.com">http://wiki.bestpractical.com</A></DIV><DIV style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">Commercial support: <A href="mailto:sales@bestpractical.com">sales@bestpractical.com</A></DIV><DIV style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; min-height: 14px; "><BR></DIV><DIV style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; min-height: 14px; "><BR></DIV><DIV style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">Discover RT's hidden secrets with RT Essentials from O'Reilly Media.<SPAN class="Apple-converted-space"> </SPAN></DIV><DIV style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">Buy a copy at <A href="http://rtbook.bestpractical.com">http://rtbook.bestpractical.com</A></DIV> </BLOCKQUOTE></DIV><BR></DIV></BODY></HTML>