MySQL LIMIT and OFFSET Pagination
debt(d7/e5/b5/t7)
Closest to 'only careful code review or runtime testing' (d7). The detection_hints field notes 'automated: no' and only a code_pattern heuristic (LIMIT ? OFFSET ? with large offset values). No standard linter or SAST tool from the term's metadata flags this; it typically surfaces only during load testing or when users on deep pages report slowness in production.
Closest to 'touches multiple files / significant refactor in one component' (e5). The quick_fix describes switching from LIMIT/OFFSET to keyset pagination (WHERE id > :last_id), which requires changing query logic, likely updating API contracts (replacing page numbers with cursors), and potentially modifying frontend consumers. This is more than a single-line patch but less than a full cross-cutting architectural rework.
Closest to 'persistent productivity tax' (b5). Applies to web and CLI contexts broadly. Choosing OFFSET pagination for user-facing APIs shapes how data is fetched, how APIs expose pagination parameters, and how total-count queries are written. It doesn't define the entire system architecture but it imposes a recurring cost on any feature involving large dataset traversal.
Closest to 'serious trap — contradicts how a similar concept works elsewhere' (t7). The misconception field states explicitly that developers believe OFFSET pagination is fine for large datasets, when in reality it degrades linearly with offset depth. This is a well-documented gotcha but one that contradicts the intuitive expectation that page 1 and page 10,000 should have similar response times, making it a serious cognitive trap.
Also Known As
TL;DR
Explanation
LIMIT n returns the first n rows. LIMIT n OFFSET m skips m rows first. The problem: OFFSET requires MySQL to scan and discard m rows before returning results — OFFSET 10000 scans 10,000 rows. Keyset pagination (cursor-based) avoids this: WHERE id > last_seen_id ORDER BY id LIMIT n. Keyset pagination is O(log n) via the index rather than O(n) for OFFSET.
Common Misconception
Why It Matters
Common Mistakes
- Using OFFSET-based pagination on user-facing APIs without a page count limit.
- Not using ORDER BY with LIMIT — results are non-deterministic without explicit ordering.
- Calculating total pages with SELECT COUNT(*) on large tables — can be slow; use approximate counts.
Avoid When
- Do not use large OFFSET values on production tables — OFFSET 10000 scans 10,000 rows before returning results.
- Never use LIMIT without ORDER BY — results are non-deterministic.
When To Use
- Use LIMIT/OFFSET for shallow pagination (first few pages) where performance is acceptable.
- Use keyset/cursor pagination (WHERE id > :last_id) for deep pagination on large tables.
Code Examples
// OFFSET pagination — slow on large tables
$offset = ($page - 1) * $perPage; // page 1000 scans 10,000 rows
$stmt = $pdo->prepare('SELECT * FROM users ORDER BY id LIMIT ? OFFSET ?');
$stmt->execute([$perPage, $offset]);// Keyset pagination — consistent performance at any page
$stmt = $pdo->prepare(
'SELECT id, email FROM users WHERE id > ? ORDER BY id LIMIT ?'
);
$stmt->execute([$lastSeenId, $pageSize]);
$rows = $stmt->fetchAll();
$nextCursor = end($rows)['id'] ?? null;