MySQL LIMIT and OFFSET Pagination

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;

References

• ↗ https://dev.mysql.com/doc/refman/8.0/en/select.html

Tags

Related Terms