MySQL Pagination

2 posts · Aug 2022 – Apr 2023

What this trail covers

PlanetScale offers robust solutions for paginating results in MySQL and PostgreSQL. This includes detailed comparisons of offset/limit and cursor-based pagination, highlighting the performance drawbacks of offset/limit for deep pages. The introduction of the FastPage gem for Rails leverages the MySQL deferred join optimization to significantly improve the performance of offset/limit queries, especially for deeper pages. This gem makes it easy to apply this optimization to any ActiveRecord::Relation using offset/limit, offering a faster alternative for applications that rely on this pagination strategy.

All PlanetScale trails

Apr 2023Pagination in MySQLThis post introduces and explains two primary methods for paginating results in MySQL: offset/limit and cursor-based pagination. It emphasizes the necessity of deterministic ordering for effective pagination, elaborates on the strengths and weaknesses of offset/limit (including 'drifting pages' and performance degradation with deep offsets), and presents the deferred join technique as an optimization for offset/limit. It also introduces cursor-based pagination as an alternative approach.
Aug 2022Introducing FastPage: Faster offset pagination for Rails appsIntroduced the FastPage gem for ActiveRecord, which applies the MySQL deferred join optimization to `offset`/`limit` queries. This gem significantly improves the performance of deep pagination by reducing the database's workload. The post details how the deferred join technique works and provides benchmarks demonstrating its effectiveness compared to standard ActiveRecord pagination. It also offers guidance on when to use FastPage, suggesting its application for pages beyond a certain threshold to maximize performance benefits.
Advertisement