Efficient Pagination in MySQL

Pagination looks harmless while every request stays near the beginning of a result set. The familiar LIMIT offset, count form is easy to implement and gives exact page numbers, but a deep offset still has to be located and skipped. The response may contain only 20 rows while MySQL examines thousands or millions of earlier index entries first.

For feeds, activity logs, search results, and other ordered streams, the durable solution is keyset pagination, also called cursor or seek pagination. Instead of asking for “page 5000,” the next request asks for rows after the last ordering key already returned. The cost then depends mainly on the page size, not on the page number.

Why Large Offsets Become Expensive

Consider a table ordered by publication time:

CREATE TABLE article (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    status VARCHAR(20) NOT NULL,
    published_at DATETIME(6) NOT NULL,
    title VARCHAR(200) NOT NULL,
    body MEDIUMTEXT NOT NULL,
    PRIMARY KEY (id),
    INDEX article_feed (status, published_at DESC, id DESC)
) ENGINE=InnoDB;

A shallow request can be perfectly reasonable:

SELECT id, published_at, title
FROM article
WHERE status = 'published'
ORDER BY published_at DESC, id DESC
LIMIT 20 OFFSET 0;

The same shape becomes progressively more expensive as the offset grows:

SELECT id, published_at, title
FROM article
WHERE status = 'published'
ORDER BY published_at DESC, id DESC
LIMIT 20 OFFSET 100000;

The index lets MySQL avoid sorting the entire result, but it does not make the first 100,000 matching entries disappear. They still have to be traversed before the requested rows can be returned. Selecting wide rows makes matters worse because discarded entries may also cause table lookups and buffer-pool traffic.

Use a Total, Deterministic Order

A cursor is meaningful only when the order is stable and unambiguous. Ordering by published_at alone is insufficient because several rows can share the same timestamp. Add a unique tie-breaker and use the same column sequence everywhere:

ORDER BY published_at DESC, id DESC

The matching index begins with equality-filtered columns, followed by the complete ordering key:

INDEX article_feed (status, published_at DESC, id DESC)

Keep cursor columns non-null. A nullable ordering key introduces three-valued comparison semantics and makes boundary predicates unnecessarily fragile. The primary key is a good final tie-breaker because it is unique and immutable.

The First Page

Fetch one row more than the visible page size. For a 20-row page, request 21 rows:

SELECT id, published_at, title
FROM article
WHERE status = 'published'
ORDER BY published_at DESC, id DESC
LIMIT 21;

If row 21 exists, remove it from the response and expose a next cursor. This answers the useful question “is there another page?” without counting the entire result set.

The Next Page

Suppose the last visible row has the values :last_published_at and :last_id. The next page contains rows with an earlier timestamp, plus rows at the same timestamp with a smaller ID:

SELECT id, published_at, title
FROM article
WHERE status = 'published'
  AND (
      published_at < :last_published_at
      OR (published_at = :last_published_at AND id < :last_id)
  )
ORDER BY published_at DESC, id DESC
LIMIT 21;

The predicate is the lexicographic meaning of “strictly after this row” for a descending order. Because it is a range condition on the indexed key, MySQL can seek to the boundary instead of walking from the beginning on every request.

MySQL also supports row-constructor comparisons such as (published_at, id) < (:time, :id). The expanded predicate above makes the boundary logic explicit and is easier to inspect across MySQL versions and query plans.

The Previous Page

For backward navigation, use the first row currently displayed as the boundary, invert the comparisons and index order, then reverse the returned rows in the application:

SELECT id, published_at, title
FROM article
WHERE status = 'published'
  AND (
      published_at > :first_published_at
      OR (published_at = :first_published_at AND id > :first_id)
  )
ORDER BY published_at ASC, id ASC
LIMIT 21;

The query finds the nearest preceding rows efficiently. Reversing that short result restores the public newest-first order. Do not replace the strict comparisons with inclusive ones, or the boundary row will appear twice.

Designing the Cursor

A cursor should carry every value needed to reconstruct the boundary and the query shape. A decoded payload might look like this:

{
  "published_at": "2026-08-23 10:15:32.184921",
  "id": "912734",
  "direction": "next",
  "version": 1
}

Encode the payload as an opaque base64url token rather than exposing it as several editable query parameters. Encoding is not authentication: if cursor integrity matters, append a server-side HMAC. Always bind decoded values as query parameters and reject malformed timestamps, directions, versions, or identifiers. In JavaScript, preserve a BIGINT UNSIGNED identifier as a decimal string or BigInt; a regular Number cannot represent every 64-bit integer exactly.

Filters and sort mode are part of the cursor contract. A cursor created for status = 'published' and newest-first order must not silently be reused for a different filter or ranking. Include a query version or a compact filter fingerprint when an endpoint supports several modes.

Behavior Under Concurrent Changes

Keyset pagination avoids the classic offset anomaly where inserting one new row at the front shifts every later page and causes a reader to see a row twice. A new row before the stored boundary does not change the next-page predicate. Deletions also leave no empty positional slot to compensate for.

This stability assumes that ordering keys do not change while a user traverses the result. If published_at is edited, a row can move across a cursor boundary and be skipped or repeated. Prefer immutable ordering values. When an endpoint needs a frozen snapshot, include a high-water mark from the first request and constrain every later query to the same snapshot boundary. A long-running database transaction is rarely appropriate for a user who may browse for minutes.

Counting Is a Separate Decision

Most interfaces need only previous/next controls, not an exact total. Fetching page_size + 1 rows is the cheapest way to determine whether another page exists.

If an exact total is genuinely required, run a separate count with the same filters:

SELECT COUNT(*)
FROM article
WHERE status = 'published';

InnoDB does not store one universally correct row count because concurrent transactions can see different snapshots. It must count rows visible to the current transaction, usually by traversing a suitable index. Cache the result when modest staleness is acceptable, but define the invalidation policy and do not turn one counter row into a write hotspot.

For a rough administrative estimate, TABLE_ROWS can be read from INFORMATION_SCHEMA.TABLES, but InnoDB statistics are approximate. The next AUTO_INCREMENT value is not a row count: rollbacks, failed inserts, deleted rows, and manually assigned IDs all create gaps.

Avoid SQL_CALC_FOUND_ROWS and FOUND_ROWS(). MySQL has deprecated this pair, and SQL_CALC_FOUND_ROWS prevents the server from stopping after the requested limited result. A focused page query plus a separate, optional COUNT(*) gives the optimizer more freedom and lets the application decide when a count is worth its cost.

When Offset Pagination Is Still Appropriate

Offset pagination remains useful for small result sets, shallow administrative screens, and interfaces where jumping to an exact page number matters more than deep-page latency. It is also stateless and easy to cache by page number.

If exact deep-page jumps are mandatory, the trade-off cannot be removed by a clever LIMIT expression. Maintain a separate rank or page-boundary structure, periodically materialize stable snapshots, or accept work proportional to the offset. A deferred join can reduce row-fetch cost while retaining offset semantics:

SELECT a.id, a.published_at, a.title
FROM article AS a
JOIN (
    SELECT id
    FROM article
    WHERE status = 'published'
    ORDER BY published_at DESC, id DESC
    LIMIT 20 OFFSET 100000
) AS page USING (id)
ORDER BY a.published_at DESC, a.id DESC;

The inner query can walk a narrow index before fetching full rows, but it still scans and discards the offset. This is a mitigation, not constant-time deep pagination.

Verify the Query Plan

Indexes are design hypotheses until measured against production-shaped data. MySQL 8 provides EXPLAIN ANALYZE to execute a query and report actual iterator timing and row counts:

EXPLAIN ANALYZE
SELECT id, published_at, title
FROM article
WHERE status = 'published'
  AND (
      published_at < '2026-08-23 10:15:32.184921'
      OR (published_at = '2026-08-23 10:15:32.184921' AND id < 912734)
  )
ORDER BY published_at DESC, id DESC
LIMIT 21;

Check that the intended composite index is used, examined rows stay close to the requested page size, and no avoidable filesort or large temporary result appears. Test the first page, a representative deep cursor, realistic filters, and both warm and cold cache conditions. Timing one tiny development table says little about the eventual access pattern.

Practical Checklist

• Choose a deterministic order and append a unique tie-breaker.
• Index equality filters first, then the complete ordering key.
• Use strict keyset boundaries and fetch one extra row.
• Keep cursor values typed, validated, opaque, and optionally signed.
• Keep ordering columns immutable or define snapshot semantics.
• Count separately only when the interface truly needs an exact total.
• Use offsets deliberately for shallow or random-access workflows.
• Confirm every claim with EXPLAIN ANALYZE on realistic data.

References

• MySQL-LIMITMySQL 8.4 Reference Manual. “LIMIT Query Optimization.”
• MySQL-COUNTMySQL 8.4 Reference Manual. “Aggregate Function Descriptions: COUNT().”
• MySQL-EXPLAINMySQL 8.4 Reference Manual. “Obtaining Information with EXPLAIN.”