This post is about scalable pagination with MySQL. OFFSET pagination breaks at scale. I share offset and cursor patterns I use for fast listing pages on large MySQL tables.
Avoid Expensive OFFSET on Big Tables
OFFSET pagination breaks at scale. I share offset and cursor patterns I use for fast listing pages on large MySQL tables.
- indexed sorting — applied directly to scalable pagination with MySQL.
- cursor pagination — applied directly to scalable pagination with MySQL.
- count caching — applied directly to scalable pagination with MySQL.
- URL design — applied directly to scalable pagination with MySQL.
How I Built It
The working version of Building Pagination That Scales With MySQL centred on indexed sorting, cursor pagination, count caching, and URL design. I avoided copying patterns from other modules unless they solved a problem this feature actually had.
Design URLs for Deep Pages Too
Representative code from the implementation — simplified for readability, but structurally what I deploy.
Cursor pagination for large MySQL tables
SELECT id, news_title, published_date
FROM news
WHERE status = 'Published' AND id < :cursor_id
ORDER BY id DESC
LIMIT 20;
CREATE INDEX idx_news_status_id ON news (status, id);Practical Outcome From the Work
Shipping scalable pagination with MySQL cleanly meant the next developer could extend it without untangling hidden coupling.
If I repeated this, I would write the regression checks earlier — especially around the failure paths users hit once, not the happy path.
If You Are Tackling Something Similar
- Start with the exact problem statement for scalable pagination with MySQL — one sentence, no buzzwords.
- Prioritise indexed sorting before polishing secondary UI details.
- Validate cursor pagination under realistic data volume, not demo rows.
