5.2.9 How MySQL Optimises LIMIT

In some cases MySQL will handle the query differently when you are using LIMIT row_count and not using HAVING:

• If you are selecting only a few rows with LIMIT, MySQL will use indexes in some cases when it normally would prefer to do a full table scan.
• If you use LIMIT row_count with ORDER BY, MySQL will end the sorting as soon as it has found the first row_count lines instead of sorting the whole table.
• When combining LIMIT row_count with DISTINCT, MySQL will stop as soon as it finds row_count unique rows.
• In some cases a GROUP BY can be resolved by reading the key in order (or do a sort on the key) and then calculate summaries until the key value changes. In this case LIMIT row_count will not calculate any unnecessary GROUP BYs.
• As soon as MySQL has sent the first # rows to the client, it will abort the query (if you are not using SQL_CALC_FOUND_ROWS).
• LIMIT 0 will always quickly return an empty set. This is useful to check the query and to get the column types of the result columns.
• When the server uses temporary tables to resolve the query, the LIMIT row_count is used to calculate how much space is required.

User Comments

Here is an example of how to use Limit:

The first example will limit the result to the first 10 items.
<font color="blue">SELECT name FROM customers LIMIT 10</font>

This example will limit the result to 10 rows, starting with row 5.
<font color="blue">SELECT name FROM customers LIMIT 5 , 10</font>

If you only use one parameter, it will be the number of rows to return, if you use two
parameters, it will be where to start, and then the number to return.

Bill

I'm not going to hazard a guess at what exactly
is going on internally, but this

LIMIT 10, -1

will return the 11th record through end of table
(indexing starts at 0).

If you're retrieving your records in order of a unique indexed key column, it's much faster to paginate results using something like this:

select * from table1 where key>LAST_KEY order by key limit 10;

where LAST_KEY is the highest key value you retrieved in your last query, than to do

select * from table1 order by key limit LAST_RESULT_ROW_PLUS_ONE, 10;

where LAST_RESULT_ROW_PLUS_ONE is 0, then 10, then 20, as you step through pages of 10.

At least, it was in my MySQL 4 database.

If you write LIMIT -1 all records that meet your criteria will be returned - like without LIMIT. I found it useful when generating queries, like in this PHP code:
if(!isset($lim))$lim = -1;
$query = "select * from table limit $lim";
$result = mysql_query($query);

Do not use negative numbers in the LIMIT clause (as suggested in other comments). The current behaviour,
"LIMIT on negative numbers are treated as big positive numbers", is documented as an error and is marked as "The following problems are known and will be fixed in due time:" Therefore relying on the current situation is not safe as future revisions may fix this error. See "Open Bugs / Design Deficiencies in MySQL" in the current manual (28 Jul 03) for more info.

Add your own comment.