So I have this MySQL InnoDB query here:
SELECT s.vid, s.body, s.timestamp, COUNT(v.id) AS votecnt FROM stories s LEFT JOIN votes v ON s.vid = v.vid WHERE s.lv = 1 AND s.status = 1 GROUP BY s.vid ORDER BY votecnt DESC
and profiling shows that over 93% of the required time to run this query is needed to copy to the results temporary table for further ordering by votecnt. What can be done to make it faster?
| 1 | SIMPLE | s | ref | newest | newest | 2 | const,const | 19873 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | v | ref | votes | votes | 4 | sikna_ci.s.vid | 1 | Using index |