Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 |

share|improve this question
    
can you post the EXPLAIN output? (just add 'EXPLAIN' to the front of the query) –  Derek Downey Apr 12 '11 at 16:22

3 Answers 3

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.

share|improve this answer

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.

share|improve this answer
    
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 !!! –  RolandoMySQLDBA May 16 '11 at 15:09

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.

share|improve this answer
    
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. –  RolandoMySQLDBA May 16 '11 at 15:03

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.