New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

How to avoid SELECT COUNT ? #447

Closed
Davidslv opened this Issue Jul 30, 2015 · 5 comments

Comments

Projects
None yet
5 participants
@Davidslv

Davidslv commented Jul 30, 2015

Hey guys, we the following code for pagination, which is pretty much trivial.

User.paginate(page: page, per_page: limit).order(id: :desc)

The problem is that it executes a second query to count the number of records in the table, and that's a very expensive operation, especially in a very big table (which is the case), is there any option to avoid doing that extra count? It's not necessary for us to have it.

D, [2015-07-30T13:34:11.370298 #3] DEBUG -- :   User Load (4.0ms)  SELECT  "users".* FROM "users" INNER JOIN "table_x" ...
D, [2015-07-30T13:34:11.407058 #3] DEBUG -- :    (34.9ms)  SELECT COUNT(*) FROM "users" INNER JOIN ...

Thank you

@wilg

This comment has been minimized.

Show comment
Hide comment
@wilg

wilg Aug 8, 2015

Curious about this as well!

Postgres counting is slow:

Would be great if will_paginate supported a mode where instead of using LIMIT/OFFSET it just let did where("whatever_i_am_sorting_by > ?", last_value_for_this_column_on_the_previous_page)

wilg commented Aug 8, 2015

Curious about this as well!

Postgres counting is slow:

Would be great if will_paginate supported a mode where instead of using LIMIT/OFFSET it just let did where("whatever_i_am_sorting_by > ?", last_value_for_this_column_on_the_previous_page)

@Davidslv

This comment has been minimized.

Show comment
Hide comment
@Davidslv

Davidslv Sep 3, 2015

We solved this with using offset

.offset((page - 1) * limit).limit(limit)

Davidslv commented Sep 3, 2015

We solved this with using offset

.offset((page - 1) * limit).limit(limit)
@epinault

This comment has been minimized.

Show comment
Hide comment
@epinault

epinault Sep 4, 2015

I don t see this when I use
Model.page(params[:page]).order(:id)

Maybe using ActiveRelation instead of forcing the query is better?

epinault commented Sep 4, 2015

I don t see this when I use
Model.page(params[:page]).order(:id)

Maybe using ActiveRelation instead of forcing the query is better?

@mislav

This comment has been minimized.

Show comment
Hide comment
@mislav

mislav Oct 15, 2016

Owner

will_paginate needs to perform the COUNT to know how many pages in total there are so it can generate pagination links. That's the sole purpose why the library was created.

Now, I understand that some COUNTs are slow. Yours, for instance, could maybe be replaced with a simple :total_entries => User.count parameter to paginate(), avoiding the expensive INNER JOIN when counting the users.

Owner

mislav commented Oct 15, 2016

will_paginate needs to perform the COUNT to know how many pages in total there are so it can generate pagination links. That's the sole purpose why the library was created.

Now, I understand that some COUNTs are slow. Yours, for instance, could maybe be replaced with a simple :total_entries => User.count parameter to paginate(), avoiding the expensive INNER JOIN when counting the users.

@mislav mislav closed this Oct 15, 2016

@rgaufman

This comment has been minimized.

Show comment
Hide comment
@rgaufman

rgaufman Apr 17, 2018

Kaminari has a mode without_count for those that just need simple "next page", "previous page" buttons: https://stackoverflow.com/questions/13284860/kaminari-without-count (Note: only for ActiveRecord, no implementation for Mongoid unfortunately)

rgaufman commented Apr 17, 2018

Kaminari has a mode without_count for those that just need simple "next page", "previous page" buttons: https://stackoverflow.com/questions/13284860/kaminari-without-count (Note: only for ActiveRecord, no implementation for Mongoid unfortunately)

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment