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.

Imagine that I want to populate the last 10 questions of stackoverflow. I have a table that keep the questions list, a table to keep the question info, a table to list question's asker.

Now imagine that I want to get the last ten questions, so I have to join some tables and get the last items by ORDER BY ... DESC LIMIT 0 , 10. This gets me the last ten questions without any problems, but I think that there may be one issue.

MySQL may be first joining all tables and then pick the last ten items, so if I have to join 1 million rows to 1 million rows, it gets really bad performance.

I want to know if MySQL does do this, and if yes, how can I go around this issue?.

share|improve this question
2  
Why haven't you tried yet? –  dezso Feb 21 '13 at 11:51
    
if I did it , I did not asked here :D –  Ata Feb 21 '13 at 11:58
    
If you'd did it, you didn't get a downvote from me. –  dezso Feb 21 '13 at 12:01
2  
But, you'll get an upvote from me. +1 !!! This is actually not a bad question. –  RolandoMySQLDBA Feb 21 '13 at 12:02
1  
@RolandoMySQLDBA do you consider this a good question? The latter part is a real problem, but this question clearly shows no sign of research... –  dezso Feb 21 '13 at 12:03

2 Answers 2

up vote 3 down vote accepted

The only way around it is to refactor the query so as to alter the order of the EXPLAIN plan.

EXAMPLE : On May 16, 2011, I once answered a very rough question on StackOverflow

Fetching a Single Row from Join Table

Basically, you have to construct the query so that JOIN clauses are done last. Consequently, this implies that you must get the LIMIT executed beforehand.

share|improve this answer
    
I also think of using two select like here : SELECT ... JOIN ... JOIN ... ON (SELECT ... FROM ... ORDER BY date DESC LIMIT 0,10 ) JOIN ... . Is it true ? –  Ata Feb 21 '13 at 14:14

http://dev.mysql.com/doc/refman/5.1/en/limit-optimization.html

If you use LIMIT row_count with ORDER BY, MySQL ends the sorting as soon as it has found the first row_count rows of the sorted result, rather than sorting the entire result. If ordering is done by using an index, this is very fast. If a filesort must be done, all rows that match the query without the LIMIT clause must be selected, and most or all of them must be sorted, before it can be ascertained that the first row_count rows have been found. In either case, after the initial rows have been found, there is no need to sort any remainder of the result set, and MySQL does not do so.

This is also true for queries with a simple JOIN.

EXPLAIN shows you the affected row count.

share|improve this answer
    
Let me add that in the case of a JOIN, it must be performed first to get the output rows to be sorted. –  dezso Feb 21 '13 at 12:17
    
The JOIN performed first, does not necessarily mean that all the trillion rows will be fetched into memory and then only the 10 transmitted. –  ypercube Feb 21 '13 at 12:45

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.