Re: [SQLObject] PostgreSQL: costly count

[Frank B. <fb...@fo...>]

Hallo,
Ian Bicking hat gesagt: // Ian Bicking wrote:

> My impression is that this is an internal value kept to do query 
> optimization (since table size effects that considerably).  It may not 
> be entirely accurate, and doesn't get updated immediately.  It can't be 
> used with any query.

Yes, you're right. A pity. ;)

> If you are doing some sort of paging, you might simply want to cache 
> the results of the count.  Also, if you are doing paging with ordering 
> (which you probably want), then paging and slicing will only save you 
> from fetching and constructing all the objects, but the database still 
> has to look at all the rows (because it can't sort them until it does 
> that).  So it might be faster to pull and cache the full results once, 
> on the assumption that the user will be looking at later results 
> eventually.

Thanks for this tip. I did something similar now for my shop
(normalmailorder.de). As I translate from URLs to SQL queries in a
constant way and as the product DB is never written partly, but always
changed by a single CSV file import as a whole once per week, I can be
sure, to always get the same count depending on the HTTP-request URL.
So I now introduced a simple CountCache table, that has two fields,
"url" and "count". If there's no CountCache.byUrl(some_url), the count
results are fetched from the DB and written to the CountCache. If the
URL already is in the Cache, this one gets used. On CSV-import the
table is emptied. I could also rebuild it from the URLs used so far,
maybe I'll do that later.

ciao
-- 
 Frank Barknecht                               _ ______footils.org__