Does MYSQL join before LIMIT?

Question

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?.

Answer 1

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

• Question : I had to join tables together and return only the 40 most recent results.
• My Answer : I proved that you can do a JOIN on tables with large rows and get results fast.
• How I did it : I had applied principles of refactoring queries I learned from a Youtube Video.

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.

Answer 2

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.