We would like to introduce FastPage, a new gem for ActiveRecord that applies the MySQL “lazy join” optimization to your offset/limit Inquire.
This is the slow paging query in Rails:
We add .fast_page to the query, now It’s 2.7 times faster!
Benchmark#
and we wanted to see how much faster it could be with a delayed connection. We took a table with about 1 million records and ran a standard ActiveRecord offset/limit query with a query using FastPage benchmarked.
Here is the query:
owner and created_at is indexed.
As you can see in the image above, the further we paginate, the faster it gets.
How this works#
The most common forms of paging are implemented using LIMIT and OFFSET.
In this example, each page returns 50 blog posts. For the first page, we crawl the first 50 posts. On page 2, we scrape 100 posts and discard the first 50. As OFFSET increases, the cost of the database service becomes higher for each additional page.
This pagination method works well until you have a lot of records. Later pages get very expensive. Therefore, applications often must limit the maximum number of pages they allow users to view or switch to cursor-based paging.
Delayed connection technology
High performance MySQL “Delayed joins” are recommended to improve pagination for LIMIT/OFFSET large tables.
Note that we first select the IDs of all the rows we want to display, then the data rows. This technique works “because it allows the server to examine as little data in the index as possible without accessing the row.”
The FastPage gem makes this easy Optimization applies to any ActiveRecord::Relation using offset/limit.
To learn more about how it works, check out this blog post: Efficient paging with lazy joins.
When should I use this? #
fast_page is best for A paginated query containing ORDER goes through . It becomes more effective as the page number increases. You should test it on your application’s data to see how it improves your query time.
Because fast_page runs 2 queries instead of 1, it’s likely that early pages will be slower. The benefits start when users go deeper into the page. It’s worth testing on which page your application becomes faster with fast_page before applying it to your query.
Thank you ❤️#
This gem is inspired by Hammerstone’s fast-paginate for Laravel and @aarondfrancis’ excellent blog post: Using lazy joins Efficient paging. We were so impressed with the results that we had to bring it to Rails as well.
