Development
Cursor Pagination vs OFFSET in PHP and MySQL
OFFSET pagination is easy to explain and easy to ship. Cursor pagination takes more care, but it usually behaves better when a PHP and MySQL application must page deeply through a large or frequently changing result set.
The important distinction is not that one syntax is modern and the other is old. They solve different navigation problems. OFFSET supports arbitrary numbered pages. A cursor supports efficient movement from a known position. Choose based on the product behavior you need, then make the ordering deterministic.
The examples target PHP 8.2 or newer with pdo_mysql, MySQL 8.0 or newer, and an InnoDB articles table whose id is a positive primary key and whose published_at is a non-null UTC DATETIME. Configure PDO to throw exceptions, return associative rows, and use native prepares:
$pdo = new PDO($dsn, $username, $password, [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
PDO::ATTR_EMULATE_PREPARES => false,
]);Native prepares matter here because PDO requires a unique placeholder for each position in a prepared statement. The examples therefore bind equal cursor values under separate names.
The two query shapes
Assume an articles table with a public feed ordered by publication time, newest first. The id column breaks ties when multiple rows have the same timestamp.
CREATE INDEX idx_articles_feed
ON articles (status, published_at DESC, id DESC);The composite index follows the equality filter first, then the exact sort order. Confirm the plan against your real schema and data with EXPLAIN. An index that looks correct on paper can still lose to a different access path when selectivity or data distribution changes.
An OFFSET query is direct:
SELECT id, title, published_at
FROM articles
WHERE status = :status
ORDER BY published_at DESC, id DESC
LIMIT :limit OFFSET :offset;Page 1 reads the first group of matching rows. Page 1,000 must find and discard all preceding matches before returning the requested group. The result may still be quick on a small table, but work grows with the offset.
A cursor query starts after the last row already seen:
SELECT id, title, published_at
FROM articles
WHERE status = :status
AND (
published_at < :cursor_before
OR (published_at = :cursor_equal AND id < :cursor_id)
)
ORDER BY published_at DESC, id DESC
LIMIT :limit;The database can seek near the cursor and continue through the index. That makes the amount of work depend more on the page size than on the page depth.
A secure OFFSET implementation with PDO
Validate page inputs before calculating the offset. Bind integers as integers, and never interpolate request values into SQL.
<?php
declare(strict_types=1);
/**
* Reads a positive integer from a query-string value.
*/
function positiveInteger(mixed $value, int $default, int $maximum): int
{
$validated = filter_var(
$value,
FILTER_VALIDATE_INT,
['options' => ['min_range' => 1, 'max_range' => $maximum]]
);
return $validated === false ? $default : $validated;
}
$page = positiveInteger($_GET['page'] ?? null, 1, 100000);
$perPage = positiveInteger($_GET['per_page'] ?? null, 25, 100);
$offset = ($page - 1) * $perPage;
$statement = $pdo->prepare(
'SELECT id, title, published_at
FROM articles
WHERE status = :status
ORDER BY published_at DESC, id DESC
LIMIT :limit OFFSET :offset'
);
$statement->bindValue(':status', 'published', PDO::PARAM_STR);
$statement->bindValue(':limit', $perPage, PDO::PARAM_INT);
$statement->bindValue(':offset', $offset, PDO::PARAM_INT);
$statement->execute();
$articles = $statement->fetchAll(PDO::FETCH_ASSOC);This approach is appropriate when result sets are bounded, users need numbered navigation, or the interface must jump directly to a specific page. A separate COUNT(*) query can provide a total, but do not run it by habit on every endpoint. Exact counts can be a meaningful part of the request cost.
An opaque, authenticated cursor
A cursor is client-visible state. Do not trust a raw timestamp and ID merely because your application created the previous link. Validate its shape and authenticate it so a malformed token cannot alter query behavior.
<?php
declare(strict_types=1);
/**
* Encodes binary data for use in a URL without padding.
*/
function base64UrlEncode(string $value): string
{
return rtrim(strtr(base64_encode($value), '+/', '-_'), '=');
}
/**
* Decodes URL-safe Base64 data or rejects an invalid value.
*/
function base64UrlDecode(string $value): string
{
$padding = (4 - strlen($value) % 4) % 4;
$decoded = base64_decode(
strtr($value, '-_', '+/') . str_repeat('=', $padding),
true
);
if ($decoded === false) {
throw new InvalidArgumentException('Invalid cursor encoding.');
}
return $decoded;
}
/**
* Creates an authenticated cursor from the final row on a page.
*
* @param array{id:int, published_at:string} $row
*/
function encodeCursor(array $row, string $secret): string
{
$payload = json_encode(
['published_at' => $row['published_at'], 'id' => $row['id']],
JSON_THROW_ON_ERROR
);
$signature = hash_hmac('sha256', $payload, $secret, true);
return base64UrlEncode($payload . $signature);
}
/**
* Validates and decodes an authenticated pagination cursor.
*
* @return array{published_at:string, id:int}
*/
function decodeCursor(string $cursor, string $secret): array
{
$decoded = base64UrlDecode($cursor);
if (strlen($decoded) <= 32) {
throw new InvalidArgumentException('Invalid cursor length.');
}
$payload = substr($decoded, 0, -32);
$signature = substr($decoded, -32);
$expected = hash_hmac('sha256', $payload, $secret, true);
if (!hash_equals($expected, $signature)) {
throw new InvalidArgumentException('Invalid cursor signature.');
}
$data = json_decode($payload, true, 8, JSON_THROW_ON_ERROR);
$date = DateTimeImmutable::createFromFormat('!Y-m-d H:i:s', $data['published_at'] ?? '');
$id = filter_var(
$data['id'] ?? null,
FILTER_VALIDATE_INT,
['options' => ['min_range' => 1]]
);
if ($date === false || $id === false) {
throw new InvalidArgumentException('Invalid cursor payload.');
}
return ['published_at' => $date->format('Y-m-d H:i:s'), 'id' => $id];
}Keep the signing secret outside source control. Rotate it deliberately because rotation invalidates outstanding cursors. The token is authenticated, not encrypted, so do not put sensitive data in it.
Fetch the next cursor page
Fetch one extra row to learn whether another page exists. This avoids a separate count query.
<?php
declare(strict_types=1);
/**
* Fetches the next stable page of published articles.
*
* @return array{items:list<array<string,mixed>>, next_cursor:?string}
*/
function fetchArticlePage(
PDO $pdo,
?string $cursor,
int $perPage,
string $secret
): array {
$perPage = max(1, min($perPage, 100));
$limit = $perPage + 1;
$parameters = [':status' => 'published'];
$cursorClause = '';
if ($cursor !== null && $cursor !== '') {
$position = decodeCursor($cursor, $secret);
$cursorClause =
' AND (published_at < :cursor_before'
. ' OR (published_at = :cursor_equal AND id < :cursor_id))';
$parameters[':cursor_before'] = $position['published_at'];
$parameters[':cursor_equal'] = $position['published_at'];
$parameters[':cursor_id'] = $position['id'];
}
$statement = $pdo->prepare(
'SELECT id, title, published_at
FROM articles
WHERE status = :status' . $cursorClause . '
ORDER BY published_at DESC, id DESC
LIMIT :limit'
);
foreach ($parameters as $name => $value) {
$type = $name === ':cursor_id' ? PDO::PARAM_INT : PDO::PARAM_STR;
$statement->bindValue($name, $value, $type);
}
$statement->bindValue(':limit', $limit, PDO::PARAM_INT);
$statement->execute();
$rows = $statement->fetchAll(PDO::FETCH_ASSOC);
$hasMore = count($rows) > $perPage;
if ($hasMore) {
array_pop($rows);
}
$last = $rows === [] ? null : $rows[array_key_last($rows)];
return [
'items' => $rows,
'next_cursor' => $hasMore && $last !== null
? encodeCursor(
['id' => (int) $last['id'], 'published_at' => $last['published_at']],
$secret
)
: null,
];
}PDO::prepare() placeholders represent complete data literals. They cannot safely stand in for a table name, column, or sort direction. Any dynamic identifier needs a server-owned allowlist.
Test both the first page, which has no cursor placeholders, and a subsequent page with native prepares enabled. Use fixtures with duplicate timestamps so both cursor comparisons execute and the unique id boundary is proven.
What happens when rows change
With OFFSET, a row inserted at the beginning between requests shifts every later offset. A user can see the same row twice or skip one. Deletes cause similar movement.
A cursor tied to the last (published_at, id) pair remains anchored. Newer inserts appear before that position and do not shift the next page. Cursor pagination is not a database snapshot, however. Updates that change a row's sort key can still move it, deletes can remove unseen rows, and a newly inserted row with an older published_at can still appear later.
An as_of predicate such as published_at <= :as_of is only a publication-time insertion boundary. It excludes normally dated newer rows, but it does not freeze edits, deletes, or backdated inserts. An immutable historical export needs persisted versioned data or a materialized result set identified by the cursor. A database consistent-read transaction can also provide a snapshot, but it must remain open across reads and is usually a poor fit for separate stateless HTTP requests.
Use the same ordering columns in the cursor predicate and ORDER BY. A cursor containing only published_at is incomplete because duplicate timestamps make the boundary ambiguous.
Measure before and after
Run the actual query with representative data and compare shallow and deep navigation:
EXPLAIN ANALYZE
SELECT id, title, published_at
FROM articles
WHERE status = 'published'
ORDER BY published_at DESC, id DESC
LIMIT 25 OFFSET 250000;Then compare it with a representative cursor predicate. Look at rows examined, chosen key, actual time, and whether MySQL performs a filesort. The MySQL ORDER BY guidance explains when an index can satisfy sorting.
Which one should you choose?
Choose OFFSET when users need numbered pages or direct jumps, the maximum depth is modest, and exact totals are already inexpensive. It is also a reasonable administrative interface default when simplicity matters more than extreme depth.
Choose a cursor for feeds, activity streams, APIs, exports, infinite scrolling, and large tables where users normally move forward or backward from their current position.
A safe migration is reversible. Add cursor navigation behind a feature flag, compare query timings and result consistency, keep the old endpoint available, and switch clients gradually. If monitoring reveals an unexpected access pattern, roll back the client without changing the stored data.
The best pagination design is the one whose navigation contract matches the interface and whose query plan stays predictable under real load.