Re: Maintaining a counter up-to-date

On Monday 02 Jun 2003 4:47 pm, Samuel Tardieu wrote:
> I have an "ips" table with 100000+ records, each record having a
> "catid" field representing its category. "catid" references a row in a
> table called "categories".
>
> For statistics purpose (generation of images with the evolution of the
> number of rows by category), I am trying to reduce the load on the
> database.
>
> The request I was doing at the beginning was:
>
> SELECT catid, COUNT(*) FROM ips GROUP BY catid;
>
> I then added a "nentries" field to the "categories" table with some
> rules to maintain the counters up-to-date:
>
> CREATE RULE cat_ins AS
[snip]
> This works fine when inserting, deleting or updating one row in the
> "ips" table. However, when i/d/u several rows at a time with the same
> "catid", I only got an increment or decrement by one of the counter.

You want to use triggers not rules here (see the "server programming" and
"procedural language" manual sections). A trigger will be fired for each row
inserted/deleted/updated. Of course this means it will be fired 5000 times
for 5000 updated rows.

--
Richard Huxton