<html><head><style type="text/css"><!-- DIV {margin:0px;} --></style></head><body><div style="font-family:arial, helvetica, sans-serif;font-size:12pt"><DIV></DIV>
<DIV>Print_page_counter and print_mail_page_counter both have indexes on "path" which is how I am accessing them.</DIV>
<DIV> </DIV>
<DIV>I added "AND v.content_type='node'" to the votingapi_cache because it has an index on content_type and content_id. That cut the time in half.</DIV>
<DIV> </DIV>
<DIV>Radioactivity has an index on id, class, and decay_profile (in that order). My query has id and class; but does not have decay_profile.</DIV>
<DIV> </DIV>
<DIV>Thank you, it is faster now, but I'd still like it even faster.</DIV>
<DIV> </DIV>
<DIV>So I now have</DIV>
<DIV><FONT face="Courier New, courier, monaco, monospace, sans-serif" color=#6000bf size=2>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 radioactivity ra ON ra.id=n.nid AND ra.class='node'<BR> INNER JOIN realname r ON r.uid=n.uid <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' AND v.content_type='node'<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> LIMIT 10<BR></FONT> </DIV>
<P><FONT face="bookman old style, new york, times, serif" color=#ff007f size=4><EM><STRONG>Nancy</STRONG></EM></FONT><BR></P>
<DIV style="FONT-SIZE: 12pt; FONT-FAMILY: arial, helvetica, sans-serif"><BR>
<DIV style="FONT-SIZE: 12pt; FONT-FAMILY: times new roman, new york, times, serif"><FONT face=Tahoma size=2>
<HR SIZE=1>
<B><SPAN style="FONT-WEIGHT: bold">From:</SPAN></B> Domenic Santangelo <domenics@gmail.com><BR><B><SPAN style="FONT-WEIGHT: bold">To:</SPAN></B> development@drupal.org<BR><B><SPAN style="FONT-WEIGHT: bold">Sent:</SPAN></B> Mon, July 19, 2010 1:55:48 PM<BR><B><SPAN style="FONT-WEIGHT: bold">Subject:</SPAN></B> Re: [development] MySql Performance Problem<BR></FONT><BR><BASE><BR>
<DIV>
<DIV>On Jul 19, 2010, at 10:18 AM, nan wich wrote:</DIV><BR>
<BLOCKQUOTE type="cite"><SPAN class=Apple-style-span style="WORD-SPACING: 0px; FONT: medium Helvetica; TEXT-TRANSFORM: none; TEXT-INDENT: 0px; WHITE-SPACE: normal; LETTER-SPACING: normal; BORDER-COLLAPSE: separate; orphans: 2; widows: 2">
<DIV>
<DIV style="FONT-SIZE: 12pt; MARGIN: 0px; COLOR: rgb(127,0,63); FONT-FAMILY: arial, helvetica, sans-serif">
<DIV style="MARGIN: 0px"></DIV>
<DIV style="MARGIN: 0px">Can someone suggest ways to improve the performance?</DIV></DIV></DIV></SPAN></BLOCKQUOTE><BR></DIV>
<DIV>First thing to do: indexes on the radioactivity, print_page_counter, print_mail_page_counter and (possibly) votingapi_cache tables. I say possibly on that one because I can't remember the behavior of that table and if indexing it might decrease performance elsewhere.</DIV><BR>
<DIV>hth,</DIV>
<DIV>D</DIV></DIV></DIV></div></body></html>