Pagination Strategies
debt(d7/e3/b5/t7)
Closest to 'only careful code review or runtime testing' (d7). The detection_hints list laravel-debugbar, mysql-slow-query-log, and clockwork — these are profiling/observability tools, not default linters. OFFSET misuse is only visible under load; page 1 looks identical to page 1000 in normal development testing. You need slow-query logs or a debugbar showing query time to catch it, and only when exercising deep pages.
Closest to 'simple parameterised fix' (e3). The quick_fix is a targeted SQL pattern swap — replace LIMIT x OFFSET y with WHERE id > :last_id LIMIT x. This is a localised change per query/repository method, but may require updating API contracts (removing page-number parameters, adding cursor tokens) and adjusting frontend pagination controls, making it slightly more than a one-liner but still within one component.
Closest to 'persistent productivity tax' (b5). applies_to covers web and api contexts broadly. Once OFFSET pagination is baked into an API contract and frontend components, every list endpoint carries the performance debt and any migration to keyset pagination must be coordinated between backend query logic, API response shape, and client-side navigation UI — a persistent tax across multiple work streams without being fully architectural.
Closest to 'serious trap' (t7). The misconception is explicit: developers assume LIMIT/OFFSET is efficient because page 1 is fast. The O(N) scan behaviour at depth contradicts the expectation that later pages cost the same as earlier ones. This is a documented gotcha that contradicts how developers reason about indexed queries, scoring t7 — it doesn't reach t9 because keyset pagination is well-documented and the failure mode is performance degradation rather than silent correctness bugs.
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;