Does anyone know why the following subquery is using filesort and scanning all rows in the table according to EXPLAIN? I am trying to get the total of the top ten scores and the result is fine.
EXPLAIN SELECT SUM( score ) AS top10_total FROM ( SELECT score FROM answers WHERE question_id = '4' ORDER BY score DESC LIMIT 10 ) AS subquery
id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 9 2 DERIVED answers ALL question_id question_id 5 547 Using filesort
Is there any way to get the same result (a sum of the top 10 votes) without the query scanning all rows in the table?