Introduction

MySQL's LIMIT offset, count gets slower as the offset increases because MySQL must read and discard all rows before the offset. A query with LIMIT 1000000, 10 reads 1,000,010 rows, throws away the first million, and returns only 10. This full-scan behavior happens even with indexes. The fix is to avoid large offsets entirely by using keyset pagination (WHERE id > last_seen_id), deferred joins, or covering indexes.

How LIMIT OFFSET Works Internally

SELECT * FROM posts ORDER BY created_at DESC LIMIT 100000, 20;

MySQL executes this as:

1. Find all matching rows using the index on created_at (or full table scan if no index)
2. Sort them if needed
3. Scan through 100,020 rows
4. Discard the first 100,000 rows
5. Return rows 100,001 through 100,020

The database cannot "jump" to row 100,000 — it must count through each row sequentially. At offset 0 this is instant. At offset 1,000,000 it becomes painfully slow.

Demonstrating the Slowdown

1-- Fast: offset 0
2SELECT * FROM posts ORDER BY id DESC LIMIT 0, 20;
3-- 0.001 sec
4
5-- Slow: offset 100,000
6SELECT * FROM posts ORDER BY id DESC LIMIT 100000, 20;
7-- 0.15 sec
8
9-- Very slow: offset 1,000,000
10SELECT * FROM posts ORDER BY id DESC LIMIT 1000000, 20;
11-- 1.8 sec
12
13-- Extremely slow: offset 10,000,000
14SELECT * FROM posts ORDER BY id DESC LIMIT 10000000, 20;
15-- 18+ sec

The time increases linearly with the offset because MySQL reads offset + count rows for every query.

Why Indexes Do Not Help

Even with a perfect index, MySQL still traverses index entries one by one:

1-- The index on `created_at` lets MySQL avoid a filesort,
2-- but it still walks 500,000 index entries to reach offset 500,000
3EXPLAIN SELECT * FROM posts ORDER BY created_at DESC LIMIT 500000, 20;
4-- type: index, rows: 500020

With SELECT *, MySQL also performs a lookup from the index back to the table data for each of those 500,000 rows — even the ones it discards.

Fix 1: Keyset Pagination (Recommended)

Instead of using an offset, remember where you left off and filter with WHERE:

1-- First page
2SELECT * FROM posts ORDER BY id DESC LIMIT 20;
3
4-- Next page: use the last id from previous page
5SELECT * FROM posts WHERE id < 12345 ORDER BY id DESC LIMIT 20;
6
7-- Next page again
8SELECT * FROM posts WHERE id < 12301 ORDER BY id DESC LIMIT 20;

This is O(1) regardless of how deep you paginate because MySQL seeks directly to id = 12345 in the index and reads 20 rows forward. No rows are discarded.

1-- Works with non-unique columns too (use a tiebreaker)
2SELECT * FROM posts
3WHERE (created_at, id) < ('2025-01-15 10:00:00', 50000)
4ORDER BY created_at DESC, id DESC
5LIMIT 20;

Fix 2: Deferred Join

Fetch only the IDs using the index, then join to get the full rows:

1-- Slow: reads 500,020 full rows
2SELECT * FROM posts ORDER BY id DESC LIMIT 500000, 20;
3
4-- Fast: reads 500,020 index-only entries, then fetches 20 full rows
5SELECT p.*
6FROM posts p
7INNER JOIN (
8    SELECT id FROM posts ORDER BY id DESC LIMIT 500000, 20
9) AS sub ON p.id = sub.id
10ORDER BY p.id DESC;

The subquery uses a covering index scan (index-only, no table lookups) for the 500,000 skipped rows. Only the 20 matching IDs trigger a table lookup.

Fix 3: Covering Index

If your query only needs indexed columns, MySQL never touches the table:

1-- Create a covering index
2ALTER TABLE posts ADD INDEX idx_cover (created_at, id, title);
3
4-- This query is served entirely from the index
5SELECT id, title FROM posts ORDER BY created_at DESC LIMIT 500000, 20;
6-- Much faster than SELECT * with the same offset

Fix 4: Pre-Computed Page Boundaries

Store the boundary values for each page:

1-- Precompute page boundaries
2CREATE TABLE post_pages (
3    page_num INT PRIMARY KEY,
4    min_id BIGINT,
5    max_id BIGINT
6);
7
8-- Query a specific page directly
9SELECT * FROM posts
10WHERE id BETWEEN 900001 AND 900020
11ORDER BY id;

This works well for static or slowly-changing datasets.

Comparison

Common Pitfalls

• Using OFFSET for infinite scroll: Infinite scroll is sequential — use keyset pagination. Each "load more" passes the last seen ID, making every page equally fast.
• Keyset pagination with non-unique columns: WHERE created_at < ? skips rows with the same timestamp. Add a tiebreaker: WHERE (created_at, id) < (?, ?).
• COUNT(*) for total pages: SELECT COUNT(*) FROM posts on large tables is slow in InnoDB. Cache the count or use an estimate: SELECT TABLE_ROWS FROM information_schema.TABLES WHERE TABLE_NAME = 'posts'.
• ORDER BY without index: Without an index matching the ORDER BY, MySQL must filesort the entire result set before applying LIMIT — slow at any offset.
• ORM default pagination: Most ORMs (Django, Rails, Laravel) use LIMIT/OFFSET by default. Switch to keyset pagination for large datasets.

Summary

• LIMIT offset, count reads offset + count rows and discards the first offset — linear time growth
• Keyset pagination (WHERE id > last_id LIMIT count) is O(1) and the best solution for sequential access
• Deferred join fetches IDs via index-only scan, then joins for full rows — good for random page access
• Covering indexes help by avoiding table lookups for discarded rows
• Avoid large OFFSET values in production — they are a performance trap that gets worse as data grows