LIMIT and OFFSET
LIMIT caps the rows returned; OFFSET skips rows the server still has to produce, which is why deep paging needs keyset instead.
LIMIT {[offset,] row_count | row_count OFFSET offset}LIMIT truncates a result. With one argument it returns the first N rows. With two, the first is the offset and the second the row count, and the offset of the initial row is 0, not 1. LIMIT 20, 10 and LIMIT 10 OFFSET 20 are the same clause in two spellings — note that the arguments swap places between the forms, which is the usual cause of an off-by-one-page bug. Both arguments must be nonnegative integer constants, except that prepared statements may use ? placeholders and stored programs may use integer routine parameters or local variables.
What each half costs
LIMIT is a real optimization. Given a LIMIT and no HAVING, MySQL stops sorting as soon as it has found the first row_count rows rather than sorting the whole result, and aborts the query once those rows have been sent. OFFSET is not an optimization at all: the server still produces every skipped row and throws it away. LIMIT 20 OFFSET 100000 reads 100,020 rows to return 20, so page 5,000 costs five thousand times what page one cost.
Keyset paging
Rather than counting rows, remember the sort key of the last row on the page and ask for rows after it. The comparison becomes a WHERE clause the index can seek to, and every page costs the same. The sort keys must be unique as a set, so append the primary key.
Example
-- Offset paging: fine for the first handful of pages of an admin list.
SELECT id, title, published_at
FROM articles
WHERE status = 'published'
ORDER BY published_at DESC, id DESC -- unique as a pair, so pages are stable
LIMIT 20 OFFSET 40; -- page 3; identical to LIMIT 40, 20
-- Keyset paging: constant cost at any depth. Feed back the sort key of the last
-- row of the previous page as :last_published_at and :last_id.
SELECT id, title, published_at
FROM articles
WHERE status = 'published'
AND (published_at, id) < (:last_published_at, :last_id) -- row comparison
ORDER BY published_at DESC, id DESC
LIMIT 20;When to use it
- Paging an API list endpoint that must stay fast at page 5,000.
- Taking the top 10 of a ranking without sorting the entire table.
- Sampling a few rows of an unfamiliar table before writing the real query.
- Batching a large DELETE so each statement holds locks briefly.
More examples
The two forms, side by side
Worth pinning up. Most page-numbering bugs come from a code generator or a hand edit that switched between the comma form and the OFFSET form without switching the argument order.
SELECT * FROM tbl LIMIT 5; -- first 5 rows; same as LIMIT 0, 5
SELECT * FROM tbl LIMIT 5, 10; -- skip 5, take 10 -> rows 6 through 15
SELECT * FROM tbl LIMIT 10 OFFSET 5; -- identical to the line above
-- The trap: these two are NOT the same statement.
SELECT * FROM tbl LIMIT 10, 20; -- skip 10, take 20
SELECT * FROM tbl LIMIT 10 OFFSET 20; -- skip 20, take 10Parameterised LIMIT, and reading to the end
Use the prepared form rather than interpolating numbers into SQL strings. The large-constant idiom comes up in export scripts, where you want everything after a known cut-off.
-- LIMIT arguments are normally constants, but a prepared statement may use
-- placeholders -- which is how a driver should send page size and offset.
PREPARE page FROM 'SELECT id, title FROM articles ORDER BY id LIMIT ? OFFSET ?';
SET @count = 20, @skip = 40;
EXECUTE page USING @count, @skip;
DEALLOCATE PREPARE page;
-- There is no OFFSET without LIMIT. To read from row 96 to the end, the manual's
-- idiom is a maximum-sized row count.
SELECT * FROM articles ORDER BY id LIMIT 95, 18446744073709551615;Deep offset when keyset paging is not available
Still reads 100,000 index entries, but only 20 full rows, so the row lookups and the buffer pool churn collapse. Requires an index on (status, published_at, id) for the inner query to stay inside the index.
-- Sometimes the UI really does demand "jump to page 5000". Page over a narrow
-- index first, then fetch the wide columns for only the 20 ids that survive.
SELECT a.*
FROM articles AS a
JOIN (SELECT id
FROM articles
WHERE status = 'published'
ORDER BY published_at DESC, id DESC
LIMIT 20 OFFSET 100000) AS page ON page.id = a.id
ORDER BY a.published_at DESC, a.id DESC; -- the outer order must be restated
Discussion