Re: COUNT & Pagination

On Tue, 13 Jan 2004, David Shadovitz wrote:

> > We avert the subsequent execution of count(*) by passing the
> > value of count(*) as a query parameter through the link in page
> > numbers.
>
> Mallah, and others who mentioned caching the record count:
>
> Yes, I will certainly do this.  I can detect whether the query's filter has
> been changed, or whether the user is merely paging through the results or
> sorting* the results.
>
> I'd love to completely eliminate the cost of the COUNT(*) query, but I guess
> that I cannot have everything.
>
> * My HTML table column headers are hyperlinks which re-execute the query,
> sorting the results by the selected column.  The first click does an ASC
> sort; a second click does a DESC sort.

another useful trick is to have your script save out the count(*) result
in a single row table with a timestamp, and every time you grab if, check
to see if x number of minutes have passed, and if so, update that row with
a count(*).  You can even have a cron job do it so your own scripts don't
incur the cost of the count(*) and delay output to the user.