5.4 Limiting a Selection Using LIMIT

MySQL supports a LIMIT clause in SELECT statements that tells the server to return only some of the rows selected by the statement. This is useful for retrieving records based on their position within the set of selected rows.

LIMIT may be given with either one or two arguments, which must be integer constants:

LIMIT row_count

LIMIT skip_count, row_count

When followed by a single integer, row_count, LIMIT returns the first row_count rows from the beginning of the result set. To select just the first 10 rows of a table t, use the following query:

SELECT * FROM t LIMIT 10;

When followed by two integers, skip_count and row_count, LIMIT skips the first skip_count rows from the beginning of the result set, and then returns the next row_count rows. To skip the first 20 rows and then return the next 10 rows, do this:

SELECT * FROM t LIMIT 20,10;

The single-argument form of LIMIT is applicable only when the rows you want to retrieve appear at the beginning of the result set. The two-argument form is more general and can be used to select an arbitrary section of rows from anywhere in the result set.

When you need only some of the rows selected by a query, LIMIT is an efficient way to obtain them. For an application that fetches rows from the server, you get better performance by adding LIMIT to the query than by fetching all the rows and discarding all but the ones of interest. By using LIMIT, the unwanted rows never cross the network at all.

It's often helpful to include an ORDER BY clause to put the rows in a particular order when you use LIMIT. When ORDER BY and LIMIT are used together, MySQL applies ORDER BY first and then LIMIT. One common use for this is to find the row containing the smallest or largest values in a particular column. For example, to find the row in a table t containing the smallest id value, use this statement:

SELECT * FROM t ORDER BY id LIMIT 1;

To find the largest value instead, use DESC to sort the rows in reverse:

SELECT * FROM t ORDER BY id DESC LIMIT 1;

The two-argument form of LIMIT is useful in conjunction with ORDER BY for situations in which you want to process successive sections of a result set. For example, in Web applications, it's common to display the result of a large search across a series of pages that each present one section of the result. To retrieve sections of the search result this way, issue a series of statements that all specify the same number of rows to return in the LIMIT clause, but vary the number of initial rows to skip:

SELECT * FROM t ORDER BY id LIMIT  0, 20;

SELECT * FROM t ORDER BY id LIMIT 20, 20;

SELECT * FROM t ORDER BY id LIMIT 40, 20;

SELECT * FROM t ORDER BY id LIMIT 60, 20;

...

It's possible to abuse the LIMIT feature. For example, it isn't a good idea to use a clause such as LIMIT 1000000, 10 to return 10 rows from a query that normally would return more than a million rows. The server must still process the query to determine the first million rows before returning the 10 rows. It's better to use a WHERE clause to reduce the query result to a more manageable size and use LIMIT to pull rows from that result. This also makes the use of ORDER BY with LIMIT more efficient because the server need not sort as large a row set before applying the limit.

In addition to their uses with SELECT, the UPDATE and DELETE statements also support a LIMIT clause, to cause only a certain number of rows to be updated or deleted. See Chapter 7, "Update Statements."