Paginating Very Large Tables with Keyset Pagination

Keyset pagination uses a last-seen ordering key instead of skipping millions of rows with OFFSET.

What It Does

Keyset pagination uses a last-seen ordering key instead of skipping millions of rows with OFFSET. The following examples distinguish a normal use case, a boundary case and a practical application.

Requirements and Compatibility

PHP 8.x and a local test environment. Apply input validation, correct error handling and the requirements of any optional libraries.

The code focuses on the core concept. Production implementation may also need routes, validation, persistence, authorization or deployment tooling.

Example 1: Basic Behavior

Use a last-seen key instead of ever-increasing OFFSET values.

<?php
$sql = "SELECT id, title FROM articles WHERE id > :last_id ORDER BY id ASC LIMIT :size";
echo $sql;

Example 2: Another Case

Fetch one extra row to detect whether another page exists; the database needs an appropriate index.

<?php
$stmt = $pdo->prepare('SELECT id, title FROM articles WHERE id > :cursor ORDER BY id ASC LIMIT :size');
$stmt->bindValue(':cursor', $afterId, PDO::PARAM_INT);
$stmt->bindValue(':size', 51, PDO::PARAM_INT);
$stmt->execute();
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
$hasMore = count($rows) > 50;
$rows = array_slice($rows, 0, 50);

This example depends on an installed extension, third-party library or configured service and is not a standalone local test.

Practical Example: Putting It to Use

Encode cursor state safely; add authentication if the cursor contains data your server trusts.

<?php
$cursorData = ["last_id" => 450];
$cursor = rtrim(strtr(base64_encode(json_encode($cursorData, JSON_THROW_ON_ERROR)), "+/", "-_"), "=");
echo $cursor;

Important Behavior and Edge Cases

The ORDER BY keys must be stable and unique; encode and validate cursors carefully.

Comparison with Related Functions or Approaches

Common Beginner Mistakes

Key Takeaways

• Keyset pagination uses a last-seen ordering key instead of skipping millions of rows with OFFSET.
• The ORDER BY keys must be stable and unique; encode and validate cursors carefully.
• LIMIT/OFFSET is simpler for small data sets but grows costly for deep pages.

Before adapting these examples, verify the return values, test the failure paths and confirm that your application meets its security and environment requirements.