title: MySQL self-join with order by optimization
body: <p>I have such a query:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>EXPLAIN tells me that this query uses temporary and filesort. It runs nearly 10s on table with 500k rows. How it can be optimized?</p>
  
  <p>Schema:</p>
  
  <p><img src="https://i.stack.imgur.com/tQsCy.png" alt="enter image description here"></p>
  
  <p>Indexes:</p>
  
  <p><img src="https://i.stack.imgur.com/fxixQ.png" alt="enter image description here"></p>
  
  <p>After suggestions given by <a href="https://stackoverflow.com/a/9892994/706261">Joachim Isaksson</a>, no performance improvements, EXPLAIN:</p>
  
  <p><img src="https://i.stack.imgur.com/EL24A.png" alt="enter image description here"></p>