SQL Query for Pagination — LIMIT, OFFSET, and Keyset Pagination

Almost every list view in an application — search results, product catalogs, order history — needs pagination. SQL makes basic pagination easy with LIMIT and OFFSET, but that same simplicity becomes a performance trap once a table grows into the millions of rows. This guide covers the standard LIMIT/OFFSET syntax, explains exactly why OFFSET slows down at scale, and shows the keyset pagination pattern that fixes it. If you're still writing the base SELECT query itself, the free AI SQL query generator can draft the SELECT and WHERE clauses from plain English — this guide picks up where that leaves off, adding LIMIT, OFFSET, and keyset pagination on top.

Basic Pagination with LIMIT and OFFSET

LIMIT caps the number of rows returned, and OFFSET skips a number of rows before starting to return results. Together they implement "page N of the results":

-- MySQL / PostgreSQL: page 1, 20 rows per page
SELECT id, title, created_at
FROM articles
ORDER BY created_at DESC
LIMIT 20 OFFSET 0;

-- Page 2 (skip the first 20 rows)
SELECT id, title, created_at
FROM articles
ORDER BY created_at DESC
LIMIT 20 OFFSET 20;

-- Page 3 (skip the first 40 rows)
SELECT id, title, created_at
FROM articles
ORDER BY created_at DESC
LIMIT 20 OFFSET 40;

The general formula for page p with pageSize rows is:

LIMIT pageSize OFFSET (p - 1) * pageSize

An ORDER BY clause is required for reliable pagination — without it, the database gives no guarantee that rows come back in the same order across requests, and pages could show duplicate or missing rows.

Why OFFSET Gets Slow on Large Tables

OFFSET is deceptively expensive. The database cannot jump straight to row 500,000 — it has to walk through and discard every row before that position, every single time you request that page:

• Page 1 (OFFSET 0) — fast, no rows to skip
• Page 1,000 (OFFSET 20000) — scans and discards 20,000 rows before returning 20
• Page 25,000 (OFFSET 500000) — scans and discards 500,000 rows before returning 20

The deeper the page, the more wasted work — query time grows roughly linearly with the offset value, even though the result set size never changes. On a table with millions of rows, deep pages can take seconds instead of milliseconds, and the cost lands on every single page request as users click "next."

Keyset (Cursor-Based) Pagination

Keyset pagination replaces OFFSET with a WHERE condition based on the last row the client already saw. Instead of counting rows, it uses an indexed column to jump directly to the right position:

-- First page
SELECT id, title, created_at
FROM articles
ORDER BY id ASC
LIMIT 20;

-- Client remembers the last id from the previous page, e.g. 20
-- Next page: no OFFSET needed
SELECT id, title, created_at
FROM articles
WHERE id > 20
ORDER BY id ASC
LIMIT 20;

-- Next page after that, last id was 40
SELECT id, title, created_at
FROM articles
WHERE id > 40
ORDER BY id ASC
LIMIT 20;

Because id is indexed (typically the primary key), the database seeks directly to WHERE id > 40 using the index instead of scanning from the start. Page 25,000 costs the same as page 1 — performance stays flat regardless of how deep you page.

For pagination ordered by a non-unique column like created_at, add the primary key as a tiebreaker to keep the cursor stable when timestamps repeat:

-- Cursor is (last_created_at, last_id) from the previous page
SELECT id, title, created_at
FROM articles
WHERE (created_at, id) < ('2026-07-01 10:15:00', 583)
ORDER BY created_at DESC, id DESC
LIMIT 20;

More Pagination Examples: Filters and Off-by-One Safety

Keyset pagination works fine alongside a WHERE filter — just add the filter condition with AND, keeping the cursor comparison on the indexed column:

-- Keyset pagination combined with a status filter
SELECT id, customer_id, total, created_at
FROM orders
WHERE status = 'active'
  AND id > 4820
ORDER BY id ASC
LIMIT 20;

The most common LIMIT/OFFSET bug in application code is an off-by-one page-size error — mixing up whether page 1 means "skip zero rows" or "skip one page":

// Correct: page 1 => OFFSET 0
const offset = (page - 1) * pageSize;

// Bug: page 1 => OFFSET pageSize (silently skips the first page)
const offset = page * pageSize; // WRONG

LIMIT/OFFSET vs Keyset Pagination — When to Use Each

1. Use LIMIT/OFFSET when the table is small to medium sized, or users need to jump to an arbitrary page number (like "go to page 47")
2. Use keyset pagination for large tables, infinite scroll, or API feeds where users only move forward/backward sequentially
3. Keyset limitation — you cannot easily jump to an arbitrary page number, since there is no concept of "skip N rows"
4. Hybrid approach — some products show page numbers using an approximate/cached count, but fetch actual rows with keyset pagination underneath

Practical Tips for Reliable Pagination

• Always index the column(s) used in ORDER BY for pagination — without an index, both LIMIT/OFFSET and keyset queries force a full sort
• Avoid SELECT COUNT(*) on every page load for large tables — cache the total count or estimate it periodically
• Use a stable, unique tiebreaker column (usually the primary key) so rows never get skipped or duplicated across pages
• MySQL and PostgreSQL both use LIMIT ... OFFSET ...; older SQL Server uses OFFSET ... ROWS FETCH NEXT ... ROWS ONLY instead

OFFSET performance is just one piece of large-table performance — see our full guide to SQL optimization techniques for large tables for indexing and query-plan tips beyond pagination. If you're generating the base MySQL query itself, our free MySQL query generator guide shows how to produce the SELECT and WHERE clauses from plain English before you add pagination on top.

Common Pagination Mistakes

• Using OFFSET on a large, frequently-paged table. Deep OFFSET values force a full scan-and-discard of every earlier row — if a table is likely to grow past a few hundred thousand rows, plan for keyset pagination early instead of retrofitting it later.
• Off-by-one page-size math. Page 1 should use OFFSET 0, not OFFSET pageSize — a common bug computes OFFSET = page * pageSize instead of OFFSET = (page - 1) * pageSize, which silently skips the first page of results.
• Forgetting ORDER BY entirely. Without an explicit ORDER BY, the database gives no guarantee of row order between requests — pages can return duplicate rows or skip rows entirely as the underlying data or query plan changes.
• Sorting on a column with no index. Both LIMIT/OFFSET and keyset pagination need an index on the ORDER BY column(s) — without one, every page request triggers a full table sort, which gets slower as the table grows.
• Using a non-unique sort column alone for keyset pagination. If two rows share the same created_at timestamp, a cursor based only on that column can skip or repeat rows — always add a unique tiebreaker like the primary key.

Frequently Asked Questions