The effect of a LIMIT clause on performance in MyS

Question

I suspect I know the answer to this already, but...

Given the following 3 MySQL statements:

SELECT SQL_CALC_FOUND_ROWS title FROM STUFF LIMIT 1000000, 1;
SELECT SQL_CALC_FOUND_ROWS title FROM STUFF LIMIT 1;
SELECT title FROM STUFF;

Is there a significant difference in performance between the three?

Answer 1

SQL_CALC_FOUND_ROWS needs to iterate over all lines to compute the answer, so there is no time defference between the 1 and 2 (except for the first query might not return anything if you have less than 1000000 rows in the table)

The third query will output the whole table, not just count its rows.

Answer 2

That depends, if you add an ORDER BY and index the column that is being ordered it can be significantly faster.

This is because it will be executed without scanning and sorting full result set. Using an index will product an index range scan which will be started and stopped as soon as soon as the required amount of rows are generated.