We want to introduce FastPage, a fresh gem for ActiveRecord that applies the MySQL “deferred join” optimization to your
This is a slow pagination query in Rails:
.fast_page to the query and today it’s 2.7x faster!
We wished to see the amount of faster utilizing the deferred join could possibly be. We took a table with about 1 million records inside it and benchmarked the typical ActiveRecord
limit query vs the query with FastPage.
This is actually the query:
created_at are indexed.
As you can plainly see in the chart above, it’s significantly faster the further in to the table we paginate.
The most typical type of pagination is implemented using
In this example, each page returns 50 blogs. For the initial page, we grab the initial 50 posts. On the next page we grab 100 posts and dispose of the initial 50. Because the
OFFSET increases, each additional page becomes more costly for the database to serve.
This technique of pagination is effective and soon you have a lot of records. The later pages become very costly to serve. For this reason, applications typically have to limit the utmost amount of pages they allow users to see or swap to cursor based pagination.
Deferred join technique
POWERFUL MySQL recommends utilizing a “deferred join” to improve the efficiency of
OFFSET pagination for large tables.
Observe that we first choose the ID of all rows you want to show, then your data for all those rows. This system works “since it lets the server examine only a small amount data as you possibly can within an index without accessing rows.”
The FastPage gem makes it simple to use this optimization to any
To find out more on what this works, have a look at this website post: Efficient Pagination Using Deferred Joins.
fast_page is most effective on pagination queries offering an
ORDER BY. It becomes far better because the page number increases. You need to test it on your own application’s data to observe how it improves your query times.
fast_page runs 2 queries rather than 1, it is extremely likely a little slower for early pages. The huge benefits begin because the user enters deeper pages. It’s worth testing to see of which page the application gets faster from using
fast_page and only deciding on your queries then.
This gem was inspired by Hammerstone’s
fast-paginate for Laravel and @aarondfrancis‘s excellent post: Efficient Pagination Using Deferred Joins. We were so impressed with the outcomes, we had to create this to Rails aswell.