3

The problem with your typical rails pagination gem is that it does 2 queries: one for the page you're on and one for the total count. When you don't care about how many pages there are (e.g. in an endless scroll), that 2nd query is unnecessary (just add 1 to your LIMIT clause in the 1st query and you know if there are more or not).

Is there a gem that'll do pagination without the 2nd query? The 2nd query is expensive when applying non-indexed filters in my WHERE clause on large datasets and indexing all my various filters is unacceptable because I need my inserts to be fast.

Thanks!

Brian
  • 2,499
  • 3
  • 24
  • 26
  • You realize by adding a limit of one you will be hitting the database every time someone scrolls to a new record, that's most likely a bad idea if you care about speed at all. – Devin M Aug 12 '11 at 17:08
  • @Devin M "add 1 to your LIMIT clause". So if your per_page is 30, you do LIMIT 31 instead of LIMIT 30. This will let you know if there are more. – Brian Aug 12 '11 at 17:16
  • I figured it out but can't post for 7 more hours: Supply a total_entries option to AR:Base.paginate using the will_paginate gem. – Brian Aug 12 '11 at 17:18

2 Answers2

1

Figured it out. When using the will_paginate gem, you can supply your own total_entries option to AR:Base.paginate. This makes it so the 2nd query doesn't run.

This works for sufficiently large datasets where you only care about recent entries.

This isn't necessarily acceptable if you actually expect to hit the end of your list because if the list size is divisible by per_page you're going to query an empty set on your last query. With endless scroll, this is fine. With a manual "load more" button, you'll be displaying "load more" at the very end when there are no more items to load.

Brian
  • 2,499
  • 3
  • 24
  • 26
  • I endorse this solution. In the next version of will_paginate there will be a documented option of performing exactly what you need here, sans COUNT query – mislav Aug 16 '11 at 18:41
0

The standard approach, as you've identified, is to fetch N+1 records when you need N and if you get more than N records in the response, there is at least one additional page of results you can display.

The only reason you'd want to do an explicit COUNT(*) call is if you need to know specifically how many more records you will need to fetch. On some engines this can take a good chunk of time to compute so it is best avoided especially if the value is never directly used.

Since this is so simple, you really don't need a plugin to do it. Plugins like will_paginate is more concerned with the number of pages available so it does the count operation.

tadman
  • 208,517
  • 23
  • 234
  • 262
  • I dunno. One could make the exact same argument for all pagination as a whole. It is, after all, a relatively simple comment. – Brian Aug 13 '11 at 06:09