Search code examples
mysqllimit

The effect of a LIMIT clause on performance in MySQL?

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?

Solution

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