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?
Explain output:
| 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 |
Best Answer
You may need to bump up the following variables as well:
If these are too small, the tmptable goes to disk quickly.
If these are too large, the tmp table goes to quickly when the limit is surpassed but creates intermittency due to moving the large in-memory tmptable to disk before completing the tmptable's usage.
So, you need to perform a tight balancing act with these tmp table variables. These variables also have the same per-thread constraint that @DTest mentioned in this answer.
BTW in theory a sort buffer is a tmptable in itself, though governed by a different session option (sort_buffer_size). Since you are sorting with a group-by column and not a pure table-established column, temptables are somewhat unavoidable.