I suggest not to index the votingapi tables, they are usually written very often and there adding an index will create an overhead.
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.
8000 nodes is actually not a lot, try removing the WHERE and ON clauses one by one and you might find the solution.
Cheers,
Mukesh
On Mon, Jul 19, 2010 at 11:25 PM, Domenic Santangelo
<domenics@gmail.com> wrote:
On Jul 19, 2010, at 10:18 AM, nan wich wrote:
Can someone suggest ways to improve the performance?
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.
hth,
D