MySQL self-join with order by optimization

Question

I have such a query:

SELECT va.value, vc.value
FROM votingapi_cache va
LEFT JOIN votingapi_cache vc ON vc.content_id = va.content_id
WHERE va.content_type = 'node' AND va.value_type = 'percent' AND va.tag = 'vote' AND va.function = 'average' AND vc.content_type = 'node' AND vc.tag = 'vote' AND vc.function = 'count'
ORDER BY va.value DESC, vc.value DESC LIMIT 0, 10

EXPLAIN tells me that this query uses temporary and filesort. It runs nearly 10s on table with 500k rows. How it can be optimized?

Schema:

enter image description here

Indexes:

enter image description here

After suggestions given by Joachim Isaksson, no performance improvements, EXPLAIN:

enter image description here

I'm going to assume there is a good reason why average and count aren't computed by aggregate functions. — mechanical_meat, Mar 27 '12 at 15:26
It's actually a Drupal's votingapi module table (there is another table votingapi_vote which contains all votes - aroud 18 million rows), which contains already agregated and cached results. — breethe, Mar 27 '12 at 15:41
Please add the execution plan. Also the output from `PROCEDURE ANALYSE()` for these two tables would be useful. You could also start by removing content_type from each of the indices. — user1191247, Mar 27 '12 at 16:08

ajreal · Accepted Answer · 2012-03-27T16:17:00.003

My suggestion is to break the join into two queries ...

First, build an index on column function, value,

Your first query should be getting the best average,
because this is the first sorting value,
such as :

SELECT average.value, average.content_id
FROM votingapi_cache average
WHERE average.function = 'average' /* plus other filter *
ORDER BY average.value DESC LIMIT 0, 30;

Then, loop thru the 30 rows to get the content_id,
and your second second query is to get the 30 rows of count for each content_id,
that's mean :

select count.value, count.content_id
FROM votingapi_cache `count`
WHERE `count`.function = 'count'
and content_id in(...30 content_id);

loop thru the 2nd results and combine with the first result to get best 10 average + count desc

this can avoid massive join

It will indeed avoid the join, but beware that 30 is a sort of 'adjust to a value sufficiently above 10 until it works for your dataset' limit. If set too low, it may not give the same result as the original. — Joachim Isaksson, Mar 27 '12 at 16:58

score 0 · Answer 2 · answered Mar 27 '12 at 16:57

Based on @ajreal's answer, you could do it like -

SELECT averages.value, counts.value
FROM (
    SELECT *
    FROM votingapi_cache
    WHERE function = 'average'
    AND content_type = 'node'
    AND tag = 'vote'
    AND value_type = 'percent'
    ORDER BY value DESC
    LIMIT 0, 30
) AS averages
LEFT JOIN votingapi_cache counts
    ON averages.content_id = counts.content_id
    AND averages.content_type = counts.content_type
    AND averages.value_type = counts.value_type
    AND averages.tag = counts.tag
WHERE counts.function = 'count'
ORDER BY averages.value DESC, counts.value DESC
LIMIT 0, 10;

MySQL self-join with order by optimization

2 Answers2