Web Development

Keyset Pagination in MariaDB - Continue After the Last Row, Not an Offset

Written by Adam Muiz Updated: 17 Oct 2026 4 min read
Keyset Pagination in MariaDB - Continue After the Last Row, Not an Offset

A list can look stable while someone reads it and still move underneath the pagination controls. Suppose page one shows the newest 20 articles. Before the reader opens page two, another article is published. If page two means “skip 20 rows,” the new row shifts that boundary. One old row can appear again, even though neither the reader nor the database made an obvious mistake.

This is the problem keyset pagination tries to solve. Instead of asking MariaDB to discard a row count, the application says, “continue after the last ordered row I received.” The idea is small, but implementing it correctly requires a deterministic order, a matching comparison, a useful index, and honest expectations about what happens while data changes.

This article develops a next-page pattern for a small PHP application using MariaDB. It assumes a newest-first list with non-null published_at and id columns. It does not claim that keyset pagination is always preferable, nor that it turns several requests into one immutable database snapshot.

An offset is a count, not a position

A familiar query for page four looks like this:

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

The MariaDB LIMIT reference defines the behavior directly: with an offset and a row count, the first offset rows are ignored and the following rows are returned. That makes numbered pages easy to express, but a distant page still describes all the preceding rows as work to pass over. MariaDB's pagination guidance therefore warns about the work imposed by large offsets.

There is a separate correctness issue when requests observe a changing list. Imagine that page one returns IDs 5, 4, 3. A new row, ID 6, is inserted at the front. The next request uses OFFSET 3, so the current first three rows are now 6, 5, 4. Page two begins with ID 3, which the reader already saw. A deletion before the boundary can move a row in the other direction and cause it to be skipped.

This does not mean offset pagination is broken. It means the offset describes a position in the result at the time each query runs. It carries no memory of which row ended the previous response.

First create a total order

Keyset pagination needs an unambiguous answer to “what comes after this row?” Ordering only by published_at is insufficient when two articles can share the same timestamp. The MariaDB ORDER BY documentation says that later ordering expressions resolve ties in earlier ones. Appending a unique primary key creates a deterministic order:

ORDER BY published_at DESC, id DESC

The pair (published_at, id) is now the row's position for this list. If page one ends at timestamp 2026-10-17 10:00:00 and ID 3, the next page must select rows with an earlier timestamp, plus rows at the same timestamp with a smaller ID:

SELECT id, title, published_at
FROM articles
WHERE status = 'published'
  AND (
    published_at < :before_time_lt
    OR (published_at = :before_time_eq AND id < :before_id)
  )
ORDER BY published_at DESC, id DESC
LIMIT 21;

Both the comparison signs and their order follow the descending sort. For an ascending list they must point the other way. Mixing a descending ORDER BY with an ascending continuation test does not merely reduce performance; it asks for the wrong rows.

The limit is 21 when the page displays 20. The application renders the first 20 and uses the extra row only to decide whether a next-page link is needed. This avoids a separate count query for that decision. It does not reveal the total number of pages.

A compact PDO implementation

The following example uses transparent query parameters rather than an opaque cursor so the boundary is visible. It assumes published_at is a MariaDB DATETIME stored at second precision, id is a positive integer, and $pdo already uses exception mode. Authentication, authorization, output escaping, and connection setup belong to the surrounding application.

<?php
declare(strict_types=1);

$pageSize = 20;
$beforeTime = $_GET['before_time'] ?? null;
$beforeIdInput = $_GET['before_id'] ?? null;

if (($beforeTime === null) !== ($beforeIdInput === null)) {
    http_response_code(400);
    exit('Both cursor fields are required.');
}

$parameters = [];
$cursorWhere = '';

if ($beforeTime !== null) {
    $time = DateTimeImmutable::createFromFormat(
        '!Y-m-d H:i:s',
        (string) $beforeTime
    );
    $beforeId = filter_var(
        $beforeIdInput,
        FILTER_VALIDATE_INT,
        ['options' => ['min_range' => 1]]
    );

    if ($time === false
        || $time->format('Y-m-d H:i:s') !== $beforeTime
        || $beforeId === false) {
        http_response_code(400);
        exit('Invalid cursor.');
    }

    $cursorWhere = <<<'SQL'
        AND (
          published_at < :before_time_lt
          OR (published_at = :before_time_eq AND id < :before_id)
        )
    SQL;
    $parameters = [
        'before_time_lt' => $beforeTime,
        'before_time_eq' => $beforeTime,
        'before_id' => $beforeId,
    ];
}

$sql = "
    SELECT id, title, published_at
    FROM articles
    WHERE status = 'published'
    {$cursorWhere}
    ORDER BY published_at DESC, id DESC
    LIMIT " . ($pageSize + 1);

$statement = $pdo->prepare($sql);
$statement->execute($parameters);
$rows = $statement->fetchAll(PDO::FETCH_ASSOC);

$hasNextPage = count($rows) > $pageSize;
if ($hasNextPage) {
    array_pop($rows);
}

$nextUrl = null;
if ($hasNextPage && $rows !== []) {
    $last = $rows[array_key_last($rows)];
    $nextUrl = '/articles?' . http_build_query([
        'before_time' => $last['published_at'],
        'before_id' => $last['id'],
    ]);
}

The dynamic SQL here chooses between two fixed query shapes: first page or continuation page. User values remain bound parameters. The numeric LIMIT is constructed from a server-side constant, not copied from the request.

An API may instead encode the timestamp and ID into one cursor string. Encoding can make URLs tidier and preserve room for more ordering fields, but base64 is not encryption and opacity is not integrity. The server must still decode, validate, and bind every value. If changing a cursor could expose another tenant's records or bypass a filter, authorization and query scoping must prevent that; a signed cursor can detect tampering but cannot replace those controls.

The index must follow the query, not the word “pagination”

For the example query, a plausible index is:

CREATE INDEX idx_articles_status_published_id
ON articles (status, published_at, id);

The equality-filtered status comes first, followed by the columns used for the ordered boundary. This follows the shape described in MariaDB's pagination guidance. It is still a candidate, not a universal prescription. Selectivity, additional filters, selected columns, table size, storage engine, write rate, and other queries all affect the useful index set.

MariaDB's index guide recommends matching indexes to WHERE and ORDER BY, checking plans with EXPLAIN, and remembering that extra indexes consume storage and add work to writes. Inspect both query shapes:

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

EXPLAIN
SELECT id, title, published_at
FROM articles
WHERE status = 'published'
  AND (
    published_at < '2026-10-17 10:00:00'
    OR (published_at = '2026-10-17 10:00:00' AND id < 3)
  )
ORDER BY published_at DESC, id DESC
LIMIT 21;

A plan is evidence about one schema, data distribution, and server version; it is not a benchmark. Test representative early and deep boundaries with production-like data before claiming an improvement. GitLab's implementation guidance makes the same practical point: verify the first query and the cursor query, because success on page one does not prove that continuation is well indexed.

What happens when the list changes?

A new row inserted before the saved boundary does not shift that boundary, so it will not make a previously displayed row reappear on the next page. That is the central advantage over counting from the current front of the list.

Keyset pagination does not promise that every browsing session sees an immutable historical list. If an unseen row is deleted, it cannot later appear. If an ordering value is edited, the row can move across the saved boundary and may be missed or encountered differently. Separate HTTP requests normally run as separate database reads; a cursor stores an ordering position, not a transaction snapshot.

Filters are part of that position's meaning too. A cursor created for status=published&author=7 should not silently continue a different query. An application can keep filters explicitly in the URL, include normalized filter state in a signed cursor, or reject a cursor when its context does not match. Whichever design is used, all filters must still be enforced by the server.

Nullable ordering columns need an explicit null-ordering policy, and mutable or non-unique fields need careful tie-breakers. The two-column example avoids those complications by requiring a non-null timestamp and unique non-null ID.

The tradeoffs are part of the design

Keyset pagination naturally supports “next” and, with reversed comparisons and ordering, “previous.” It does not naturally answer “go directly to page 417,” because page numbers are defined by counts rather than known row keys. Exact “items 61–80 of 38,412” displays may also require a separate count whose cost and freshness need their own decision.

Those losses matter in some interfaces. An administrative table with a few hundred mostly stable records may be clearer with numbered offset pages. A public activity feed, article archive, or API collection that grows continuously may fit cursor-based navigation better. Markus Winand's discussion of the seek method emphasizes both the lower discard work and the application-wide requirement to carry the keyset, while GitLab's engineering documentation explicitly treats the lack of page numbers as a limitation.

There is no need to replace every offset merely because keyset pagination exists. The useful question is whether the interface needs arbitrary numbered jumps, or whether it needs reliable continuation through a large, changing ordered set.

A review checklist

  • The query has an explicit, deterministic order ending in a unique tie-breaker.
  • The cursor carries every value needed to reproduce that ordering boundary.
  • The continuation comparison uses the same columns and directions as ORDER BY.
  • Cursor values are parsed, validated, and bound as data.
  • Authorization and filters are reapplied on every request.
  • The application fetches one extra row only when it needs a next-page signal.
  • Indexes are chosen for the complete query shape and checked on both first and later pages.
  • Tests cover tied timestamps, inserts before the boundary, deletions, invalid cursors, and the final partial page.
  • The interface does not promise page-number or snapshot semantics that the implementation cannot provide.

Conclusion

Offset pagination asks MariaDB to find a current ordered result and throw away a count from its front. Keyset pagination remembers a row boundary instead. For a newest-first MariaDB list, that boundary can be the pair (published_at, id), with the primary key resolving timestamp ties and a matching comparison selecting what follows.

The result is not magic consistency. Rows can still be edited or deleted, cursors still need validation, and indexes still need evidence from the actual schema and plans. It also trades arbitrary page jumps for directional navigation. When that trade fits the interface, keyset pagination gives the application a more honest instruction than “skip however many rows used to be before this page”: continue after the last row the reader actually received.

References