6

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 |

Improve this question
1
  • can you post the EXPLAIN output? (just add 'EXPLAIN' to the front of the query) Commented Apr 12, 2011 at 16:22

3 Answers 3

Reset to default
7

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.

Improve this answer
6

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.

Improve this answer
1
  • 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. Commented May 16, 2011 at 15:03
1

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.

Improve this answer
1
  • 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 !!! Commented May 16, 2011 at 15:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.