Rails pagination - faster count of total pages?

Question

I'm using the Kaminari gem to paginate my searches:

@outfits = Outfit.where("description LIKE ?", "%warm%").page(params[:page]).per(20)

And using this code to decide whether or not to show more:

<% unless @outfits.current_page == @outfits.total_pages %>
 <p id="view-more">
  <%= link_to('View More', url_for(page: @outfits.current_page + 1), remote: true) %>
 </p>
<% end %>

The problem is, comparing whether or not the current page is the last page is expensive in terms of load time. I have millions of outfits in my database and so the activerecord count time is over 600ms on average:

   (616.9ms)  SELECT COUNT(*) FROM "outfits" WHERE (desc LIKE '%warm%') 

What can I do to speed this up?

Answer 1

The short answer is not really. If you're going to run LIKE queries based on user input, you're going to have to count them either way in order to tell whether or not a user is on the last page.

You could speed up your search time dramatically by leveraging a text search engine such as elasticsearch-rails. https://github.com/elastic/elasticsearch-rails. There's a bit of overhead setting it up, but it's probably worth figuring out if you have millions of records.

If you can get that up and running you can get the result count from your indexes, which should be lightning fast compared to hitting Postgres with a LIKE query.

Answer 2

Have you considered trying to use #last_page? or #next_page? instead?

For example

<!-- <% if @outfits.next_page?  %> -->
<% unless @outfits.last_page?  %>
  <p id="view-more">
    <%= link_to 'View More', path_to_next_page(@outfits), remote: true %>
  </p>
<% end %>

Additionally you can take a look at kaminari's suggestion for large datasets which is basically don't count and by default it will render nothing if there are no more results on the next page.

e.g.

@outfits = Outfit.where("description LIKE ?", "%warm%")
   .page(params[:page])
   .per(20)
   .without_count

And then in your view:

<p id="view-more">
    <%= link_to_next_page @outfits, 'View More', remote: true do %>
      <span> No More Pages </span>
    <% end %>
</p>