MySQL LIMIT: Syntax, OFFSET, and Pagination Examples

Use MySQL LIMIT to return at most a specified number of rows from a query. Combine it with ORDER BY when you need a predictable top-N result or consistent pagination; without ORDER BY, MySQL does not guarantee which rows appear first.

LIMIT syntax

MySQL accepts these equivalent ways to skip rows and return a page:

-- First 10 rows in the chosen order
SELECT film_id, title FROM film ORDER BY film_id LIMIT 10;

-- Skip 20 rows, then return up to 10
SELECT film_id, title FROM film ORDER BY film_id LIMIT 10 OFFSET 20;

-- MySQL's comma form: LIMIT offset, row_count
SELECT film_id, title FROM film ORDER BY film_id LIMIT 20, 10;

row_count is the maximum number of rows to return. offset is the number to skip, starting at zero; omitting it is equivalent to OFFSET 0. Both arguments must be nonnegative integers. In the comma form, offset comes first; in the OFFSET form, row count comes first.

Return the longest films

The following examples use the film table in the Sakila sample database. To get the ten longest G-rated films:

SELECT film_id, title, length
FROM film
WHERE rating = 'G'
ORDER BY length DESC, film_id ASC
LIMIT 10;

The second sort key (film_id) makes ties in film length deterministic. Sorting only by length leaves the relative order of equal-length films unspecified.

Paginate with LIMIT and OFFSET

To fetch the first page of ten films ordered by their unique IDs:

SELECT film_id, title
FROM film
ORDER BY film_id
LIMIT 10;

For page two, skip the first ten rows:

SELECT film_id, title
FROM film
ORDER BY film_id
LIMIT 10 OFFSET 10;

On the standard Sakila film table, the first query covers film_id 1–10 and the second covers 11–20. The equivalent MySQL comma syntax for page two is LIMIT 10, 10, not LIMIT 10 10.

To find the total number of rows separately:

SELECT COUNT(*) AS total_films FROM film;

The standard Sakila sample contains 1,000 films, so ten rows per page means 100 pages. The count is not returned automatically by LIMIT.

Always choose a stable sort order for pagination. If you sort by a non-unique column, append a unique tie-breaker such as the primary key. Even with a stable order, inserts and deletes between requests can shift which rows an offset-based page contains.

Large offsets: consider keyset pagination

For large tables, LIMIT 10 OFFSET 100000 may require scanning or skipping many rows. If users move forward through records sorted by an indexed unique key, seek from the last seen value instead:

-- After a page ending at film_id = 10
SELECT film_id, title
FROM film
WHERE film_id > 10
ORDER BY film_id
LIMIT 10;

This returns the next available IDs without counting past an ever-growing offset. Keyset pagination is not a drop-in replacement for arbitrary page numbers, and the cursor condition must match the complete sort order for multi-column sorting.

See the official MySQL SELECT syntax, LIMIT optimization notes, and the ORDER BY guide for more details.