Avoiding temporary tables while sorting by a column in a different table

Question

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 |

• Derek Downey: can you post the EXPLAIN output? (just add 'EXPLAIN' to the front of the query)

Answer 1

You may need to bump up the following variables as well:

• tmp_table_size
• max_heap_table_size

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.

Answer 2

The manual page for order by optimization might help you, specifically the suggestion summary at the end of the article. Be careful on increasing sort_buffer_size and read_rnd_buffer_size, as I believe they are 'per thread' values, which means each mysql connection thread will get allocated the memory associated with each value. If too large, you can find your server running out of memory fast.

• RolandoMySQLDBA: The 'order of optimization' link you mentioned provides a nice jumpoff point for those who want to get their feet wet in optimizing, especially for those who don't see a clear path to do so. +1 on this one.

Answer 3

If 93% of the execution of the query is used for the temporary tables, the this is not necessarily a bad thing. Temporary tables are managed internally by the DB OS, and will be pretty fast. To speed up the query, you could loose the ORDER BY clause.

• RolandoMySQLDBA: You are right on this one in the sense that the ORDER BY forces an additional sort and, of course, and additional temp table. +1 on yours, Sir !!!