Re: Pagination count strategies

On 3 April 2014 15:34, Leonardo M. Ramé <l(dot)rame(at)griensu(dot)com> wrote:
> Hi, in one of our systems, we added a kind of pagination feature, that
> shows N records of Total records.
>
> To do this, we added a "count(*) over() as Total" field in our queries
> in replacement of doing two queries, one for fetching the records, and
> other for getting the count. This improved the performance, but we are't
> happy with the results yet, by removing the count, the query takes
> 200ms vs 2000ms with it.
>
> We are thinking of removing the count, but if we do that, the system
> will lack an interesting feature.
>
> What strategy for showing the total number of records returned do you
> recommend?.

Assuming your results are unique (and what would be the point of
showing duplicate results?) in a specific order, it should be possible
to (uniquely) identify the last record shown on a previous page and
display n results from there on.

To add a result counter for displaying purposes to that, since you
need to remember the last displayed result anyway, you might as well
store the relative record number of that result with it and count from
there on.

This works well if you only have a next/previous results link, but not
so well when people can pick arbitrary page numbers. It can work
relative to the current page regardless of which page the user chose
to navigate to next, but you'll have to go through all the results in
between that page and the current page... That should still be faster
than always counting from the start though (and you can be smart about
from which end you start counting).

--
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.