I suggest not to index the votingapi tables, they are usually written very often and there adding an index will create an overhead. <div><br></div><div>The join clauses have a lot of string comparison. If you have a good index size, then you should index the string fields first, else this query is always going to be a bottleneck. </div>
<div><br></div><div>8000 nodes is actually not a lot, try removing the WHERE and ON clauses one by one and you might find the solution. <br clear="all"><br>Cheers,<br>Mukesh<br>
<br><br><div class="gmail_quote">On Mon, Jul 19, 2010 at 11:25 PM, Domenic Santangelo <span dir="ltr"><<a href="mailto:domenics@gmail.com">domenics@gmail.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex;">
<div style="word-wrap:break-word"><div class="im"><br><div><div>On Jul 19, 2010, at 10:18 AM, nan wich wrote:</div><br><blockquote type="cite"><span style="border-collapse:separate;font-family:Helvetica;font-style:normal;font-variant:normal;font-weight:normal;letter-spacing:normal;line-height:normal;text-indent:0px;text-transform:none;white-space:normal;word-spacing:0px;font-size:medium"><div>
<div style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0px;font-family:arial, helvetica, sans-serif;font-size:12pt;color:rgb(127, 0, 63)"><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">Can someone suggest ways to improve the performance?</div></div></div></span></blockquote><br></div></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></blockquote></div><br></div>