You may ultimately wish to consider the option of grabbing at least some of the data in straightforward single table lookups into PHP hashes and then operating on those using PHP array operations, liberating yourself from so many MySQL joins and string ops.<div>
<br></div><div>Victor Kane</div><div><a href="http://awebfactory.com.ar">http://awebfactory.com.ar</a><br><br><div class="gmail_quote">On Mon, Jul 19, 2010 at 3:05 PM, Mukesh Agarwal <span dir="ltr"><<a href="mailto:mike4u4ever2001@gmail.com">mike4u4ever2001@gmail.com</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex;">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><font color="#888888">Mukesh</font><div><div></div><div class="h5">
<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" target="_blank">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><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></div></div>
</blockquote></div><br></div>