LIMIT Clause

You can use the LIMIT clause with an SQL statement to specify the maximum number of rows to be retrieved from the table.

To build applications that need to "page" through large amounts of data, an approach called "keyset pagination" must be used, which combines using the WHERE clause on a particular column, the ORDER BY clause on that column, and the LIMIT clause.

Syntax

<simple SELECT statement> [ORDER BY clause] [LIMIT <rowcount_expr>]

The LIMIT clause is optionally appended to a SELECT statement. If present, the LIMIT clause is the last clause applied to a query. The results for the query are calculated, optionally the results can be ordered, and then the LIMIT is applied to the results.

LIMIT Clause and Full Table Scan Property

When the WHERE clause of a SELECT statement does not refer to the primary key or secondary indexes of a table, the entire table must be scanned to find the rows of the table that is returned for a query. This process of scanning the entire table is known as a full table scan. When a LIMIT clause is appended to a SELECT statement, it does not affect whether or not a full table scan is performed. However, the LIMIT clause affects the number of results returned for the query, but not how many rows are scanned for a table. The rows resulting from the scan might then also need to be grouped with a GROUP BY clause and then sorted with ORDER BY clauses. The final set of result rows are then limited by the count specified for the LIMIT clause.