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.

Tendril

Answer 1

Well, Kaminari or will_paginate needs to count total somehow in order to determine total_pages to be rendered. This is inevitable. My solution was to look at the database query and try to optimize it. That's the way to go.

(this answer is outdated, see above answers)

Tendril

could you share your solution in more detail? – Malaise

This answer is outdated. See the other answer for a supported solution in Kaminari. – Alisaalisan

There is really no need to downvote my answer, which was answered in 2012 when without_count did not yet exist. By all means, be happy with the above and leave this alone. – Tendril

Answer 2

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

Blackout

Answer 3

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.

Ageold