<html><head><style type="text/css"><!-- DIV {margin:0px;} --></style></head><body><div style="font-family:arial, helvetica, sans-serif;font-size:12pt;color:#7f003f;"><DIV></DIV>
<DIV>I suppose this is my fault for packing so much into a single query, but I really do want to use tablesort on it.</DIV>
<DIV> </DIV>
<DIV>Here's the query (pager_query with a limit of 20)</DIV>
<DIV>SELECT n.nid, n.title, n.status, n.created, n.uid, ra.energy, nc.totalcount, nc.daycount, <BR> ppc.totalcount AS printcount, pmc.sentcount, r.realname, <BR> cs.comment_count, v.value AS votes <BR> FROM node n <BR> INNER JOIN realname r ON r.uid=n.uid <BR> INNER JOIN radioactivity ra ON ra.id=n.nid <BR> LEFT JOIN node_counter nc ON nc.nid=n.nid <BR> LEFT JOIN node_comment_statistics cs ON cs.nid=n.nid <BR> LEFT JOIN votingapi_cache v ON v.content_id=n.nid AND v.function='sum' <BR> LEFT JOIN print_page_counter ppc ON ppc.path=CONCAT('node/', n.nid) <BR> LEFT JOIN print_mail_page_counter pmc ON pmc.path=CONCAT('node/', n.nid) <BR> WHERE n.type = 'blog' AND n.status = 1 <BR> ORDER BY ra.energy DESC<BR></DIV>
<DIV>On my test site, this runs really fast - but there are only 6 rows. On the production site, there are 8,000 nodes and this query takes 35 seconds to run.</DIV>
<DIV> </DIV>
<DIV>EXPLAIN tells me:</DIV>
<DIV>
<TABLE class=data id=table_results>
<THEAD>
<TR>
<TH>id </TH>
<TH>select_type </TH>
<TH>table </TH>
<TH class=condition>type </TH>
<TH>possible_keys </TH>
<TH>key </TH>
<TH>key_len </TH>
<TH>ref </TH>
<TH>rows </TH>
<TH>Extra </TH></TR></THEAD>
<TBODY>
<TR class=odd>
<TD class=" nowrap" align=right>1</TD>
<TD class="">SIMPLE</TD>
<TD class="">ra</TD>
<TD class=" condition">ALL</TD>
<TD class="">PRIMARY</TD>
<TD class=""><I>NULL</I></TD>
<TD class=""><I>NULL</I></TD>
<TD class=""><I>NULL</I></TD>
<TD class=" nowrap" align=right>6</TD>
<TD class="">Using temporary; Using filesort</TD></TR>
<TR class=even>
<TD class=" nowrap" align=right>1</TD>
<TD class="">SIMPLE</TD>
<TD class="">n</TD>
<TD class=" condition">eq_ref</TD>
<TD class="">PRIMARY,node_status_type,node_type,uid</TD>
<TD class="">PRIMARY</TD>
<TD class="">4</TD>
<TD class="">drupal6b.ra.id</TD>
<TD class=" nowrap" align=right>1</TD>
<TD class="">Using where</TD></TR>
<TR class=odd>
<TD class=" nowrap" align=right>1</TD>
<TD class="">SIMPLE</TD>
<TD class="">nc</TD>
<TD class=" condition">eq_ref</TD>
<TD class="">PRIMARY</TD>
<TD class="">PRIMARY</TD>
<TD class="">4</TD>
<TD class="">drupal6b.n.nid</TD>
<TD class=" nowrap" align=right>1</TD>
<TD class=""> </TD></TR>
<TR class=even>
<TD class=" nowrap" align=right>1</TD>
<TD class="">SIMPLE</TD>
<TD class="">cs</TD>
<TD class=" condition">eq_ref</TD>
<TD class="">PRIMARY</TD>
<TD class="">PRIMARY</TD>
<TD class="">4</TD>
<TD class="">drupal6b.ra.id</TD>
<TD class=" nowrap" align=right>1</TD>
<TD class=""> </TD></TR>
<TR class=odd>
<TD class=" nowrap" align=right>1</TD>
<TD class="">SIMPLE</TD>
<TD class="">v</TD>
<TD class=" condition">ALL</TD>
<TD class=""><I>NULL</I></TD>
<TD class=""><I>NULL</I></TD>
<TD class=""><I>NULL</I></TD>
<TD class=""><I>NULL</I></TD>
<TD class=" nowrap" align=right>3729</TD>
<TD class=""> </TD></TR>
<TR class=even>
<TD class=" nowrap" align=right>1</TD>
<TD class="">SIMPLE</TD>
<TD class="">ppc</TD>
<TD class=" condition">ALL</TD>
<TD class="">PRIMARY</TD>
<TD class=""><I>NULL</I></TD>
<TD class=""><I>NULL</I></TD>
<TD class=""><I>NULL</I></TD>
<TD class=" nowrap" align=right>2738</TD>
<TD class=""> </TD></TR>
<TR class=odd>
<TD class=" nowrap" align=right>1</TD>
<TD class="">SIMPLE</TD>
<TD class="">pmc</TD>
<TD class=" condition">ALL</TD>
<TD class="">PRIMARY</TD>
<TD class=""><I>NULL</I></TD>
<TD class=""><I>NULL</I></TD>
<TD class=""><I>NULL</I></TD>
<TD class=" nowrap" align=right>2745</TD>
<TD class=""> </TD></TR>
<TR class="even hover">
<TD class=" nowrap" align=right>1</TD>
<TD class="">SIMPLE</TD>
<TD class="">r</TD>
<TD class=" condition">eq_ref</TD>
<TD class="">PRIMARY</TD>
<TD class="">PRIMARY</TD>
<TD class="">4</TD>
<TD class="">drupal6b.n.uid</TD>
<TD class=" nowrap" align=right>1</TD>
<TD class="">Using where</TD></TR></TBODY></TABLE></DIV>
<DIV> </DIV>
<DIV>With the performance I see, I am not suprised to see "filesort" in there.</DIV>
<DIV> </DIV>
<DIV>Can someone suggest ways to improve the performance?</DIV>
<DIV> </DIV>
<DIV> </DIV>
<P><FONT face="bookman old style, new york, times, serif" color=#ff007f size=4><EM><STRONG>Nancy</STRONG></EM></FONT></P></div></body></html>