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.

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;

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

Frequently Asked Questions