free counter
Tech

Faster offset pagination for Rails apps

We want to introduce FastPage, a fresh gem for ActiveRecord that applies the MySQL “deferred join” optimization to your offset/limit queries.

This is a slow pagination query in Rails:

We add .fast_page to the query and today it’s 2.7x faster!

Benchmark#

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 offset/limit query vs the query with FastPage.

This is actually the query:

Both owner and created_at are indexed.

Graph showing deferred join benchmarks for Activerecord vs with FastPage over 2000 pages, FastPage almost linear line with a small fraction of Activerecord

As you can plainly see in the chart above, it’s significantly faster the further in to the table we paginate.

How this works#

The most typical type of pagination is implemented using LIMIT and OFFSET.

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 LIMIT/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 ActiveRecord::Relation using offset/limit.

To find out more on what this works, have a look at this website post: Efficient Pagination Using Deferred Joins.

When must i utilize this?#

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.

Because 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.

Many thanks #

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.

Read More

Related Articles

Leave a Reply

Your email address will not be published.

Back to top button

Adblock Detected

Please consider supporting us by disabling your ad blocker