Using will_paginate without :total_entries to improve a lengthy query

Question

I have a current implementation of will_paginate that uses the paginate_by_sql method to build the collection to be paginated. We have a custom query for total_entries that's very complicated and puts a large load on our DB. Therefore we would like to cut total_entries from the pagination altogether.

In other words, instead of the typical pagination display of 'previous 1 [2] 3 4 5 next', we would simply like a 'next - previous' button only. But we need to know a few things.

1. Do we display the previous link? This would only occur of course if records existing prior to the ones displayed in the current selection
2. Do we display the next link? This would not be displayed if the last record in the collection is being displayed

From the docs

A query for counting rows will automatically be generated if you don’t supply :total_entries. If you experience problems with this generated SQL, you might want to perform the count manually in your application.

So ultimately the ideal situation is the following.

• Remove the total_entries count because it's causing too much load on the database
• Display 50 records at a time with semi-pagination using only next/previous buttons to navigate and not needing to display all page numbers available
• Only display the next button and previous button accordingly

Has anyone worked with a similar issue or have thoughts on a resolution?

Answer 1

There are many occasions where will_paginate does a really awful job of calculating the number of entries, especially if there are joins involved that confuse the count SQL generator.

If all you need is a simple prev/next method, then all you need to do is attempt to retrieve N+1 entries from the database, and if you only get N or less than you're on the last page.

For example:

per_page = 10
page = 2

@entries = Thing.with_some_scope.find(:all, :limit => per_page + 1, :offset => (page - 1) * per_page)

@next_page = @entries.slice!(per_page, 1)
@prev_page = page > 1

You can easily encapsulate this in some module that can be included in the various models that require it, or make a controller extension.

I've found that this works significantly better than the default will_paginate method.

The only performance issue is a limitation of MySQL that may be a problem depending on the size of your tables.

For whatever reason, the amount of time it takes to perform a query with a small LIMIT in MySQL is proportional to the OFFSET. In effect, the database engine reads through all rows leading up to the particular offset value, then returns the next LIMIT number rows, not skipping ahead as you'd expect.

For large data-sets, where you're having OFFSET values in the 100,000 plus range, you may find performance degrades significantly. How this will manifest is that loading page 1 is very fast, page 1000 is somewhat slow, but page 2000 is extremely slow.