Pagination Strategies

Also Known As

TL;DR

Explanation

Offset pagination (LIMIT x OFFSET y) is simple but degrades at large offsets — the database must scan and discard all preceding rows. Keyset (cursor) pagination (WHERE id > :last_id ORDER BY id LIMIT x) is O(log n) via the index regardless of page depth, making it suitable for deep pagination and infinite scroll. For PHP applications with large datasets, keyset pagination is the default recommendation. Total count queries for offset pagination are expensive — consider approximate counts or omitting page counts for large datasets.

Common Misconception

Why It Matters

Common Mistakes

• OFFSET-based pagination on large datasets — OFFSET 100000 still scans 100K rows before returning results.
• Not using cursor-based (keyset) pagination for high-volume feeds — use WHERE id > :last_id LIMIT 20.
• No total count query — either skip it (infinite scroll) or cache it; running COUNT(*) on every page is expensive.
• Allowing unlimited page sizes via API — ?per_page=999999 loads the entire table.

Avoid When

• Offset pagination on large tables — OFFSET 10000 scans and discards 10,000 rows; use keyset pagination instead.
• Paginating tiny result sets — if the total is always under 20 rows, returning all at once is simpler.
• Using page numbers in APIs that other services consume — page numbers are unstable when data is inserted or deleted between requests.

When To Use

• Any list endpoint that can return more than ~50 items — return all and you risk memory exhaustion and slow responses.
• Cursor or keyset pagination for feeds and timelines where consistent ordering and stability matter.
• User-facing tables and search results where navigation by page number is expected.
• Infinite scroll UIs where the next page is loaded as the user approaches the bottom.

Code Examples

// OFFSET pagination — slow on large tables (reads all preceding rows)
$page = (int) $_GET['page'];
$rows = $db->query('SELECT * FROM posts ORDER BY id DESC LIMIT 20 OFFSET ' . ($page * 20));

// Cursor/keyset pagination — constant time regardless of page depth
$cursor = (int) ($_GET['cursor'] ?? PHP_INT_MAX);
$rows   = $db->query(
    'SELECT * FROM posts WHERE id < ? ORDER BY id DESC LIMIT 21',
    [$cursor]
);
$hasMore = count($rows) === 21;
$items   = array_slice($rows, 0, 20);
$nextCursor = $hasMore ? end($items)->id : null;

References

• ↗ https://use-the-index-luke.com/no-offset
• ↗ https://www.citusdata.com/blog/2016/03/30/five-ways-to-paginate/

Tags

Related Terms