If you're building a web app with ruby on rails, there's a pretty good chance you're using the will_paginate gem to make it easy to split a long list of items across multiple pages. If you have a couple hundred items, will_paginate works great out of the box. However, once you start paginating thousands and thousands of items, you'll start to run into performance issues. I'll show you how to fix them in this post.
When you make a database query with will_paginate, it makes two queries: one to grab the set of items on the current page, and one to count the total number of items. It checks for the total number of items so it can calculate how many pages there are in total.
The problem with this is that doing a COUNT on a complicated query can be pretty slow. The database might have to load a lot of different items to check if they should be included in the count or not.
Fortunately, the way to fix this is super easy: just use the "total_entries" option in your .paginate() call and set it to a reasonable value. For my apps, I found that setting this to ten times the number of items per page (for a total of ten pages) worked out. It was fast and allowed users to access the first couple hundred entries easily. (Most people don't really need to access anything after the tenth page anyway.)
I found this solution in a Github issue. In practice, the code in your controller would look something like this:
@cheap_cars = Car.where("price <= :cutoff", {cutoff: 10000})
.order(price: :asc)
.paginate(page: params[:page],
per_page: 30,
total_entries: 300)
Finally, you should avoid using .size on any relations returned by .paginate(). This will trigger a database COUNT that will also be slow. Instead use .length to check just the number of elements in the current batch.
Did you find this post helpful? Let me know in the comments!
Photo by Roman Trifonov