Kaminari without COUNT

Question

Can Kaminari work without hitting the DB with a COUNT(*) query?

My app's database is huge and counting the items takes much much longer than getting the items itself, leading to performance issues.

Suggestions for other pagination solutions with large datasets are also welcome.

Answer 1

Paginating Without Issuing SELECT COUNT Query

Generally the paginator needs to know the total number of records to display the links, but sometimes we don't need the total number of records and just need the "previous page" and "next page" links. For such use case, Kaminari provides without_count mode that creates a paginatable collection without counting the number of all records. This may be helpful when you're dealing with a very large dataset because counting on a big table tends to become slow on RDBMS.

Just add .without_count to your paginated object:

User.page(3).without_count

In your view file, you can only use simple helpers like the following instead of the full-featured paginate helper:

<%= link_to_prev_page @users, 'Previous Page' %>
<%= link_to_next_page @users, 'Next Page' %>

Source: github.com/kaminari

Answer 2

We have a case where we do want a total count, but don't want to hit the database for it — our COUNT query takes a couple of seconds in some cases, even with good indexes.

So we've added a counter cache to the parent table, keep it up to date with triggers, and override the total_count singleton on the Relation object:

my_house = House.find(1)
paginated = my_house.cats.page(1)
def paginated.total_count
    my_house.cats_count
end

... and all the things that require counts work without making that query.

This is an unusual thing to do. Maintaining a counter cache has some costs. There may be weird side effects if you do further relational stuff with your paginated data. Overriding singleton methods can sometimes make debugging into a nightmare. But used sparingly and documented well, you can get the behavior you want with good performance.