The effect of a LIMIT clause on performance in MySQL?

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?

Sure, they're different queries ;) I guess every query will produce the same result? — JHollanti, Mar 27 '13 at 20:23
possible duplicate of [Why does MYSQL higher LIMIT offset slow the query down?](http://stackoverflow.com/questions/4481388/why-does-mysql-higher-limit-offset-slow-the-query-down) — Brendan Long, Mar 27 '13 at 20:25
if you have 1 million rows, `SQL_CALC_FOUND_ROWS` is going to take a while, regardless. — castis, Mar 27 '13 at 20:26

score 2 · Accepted Answer · answered Mar 27 '13 at 20:31

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.

score 0 · Answer 2 · answered Mar 27 '13 at 20:25

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.

The effect of a LIMIT clause on performance in MySQL?

2 Answers2