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 ON INSERT TO ips DO UPDATE categories SET nentries = (categories.nentries + 1) WHERE (categories.catid = new.catid); CREATE RULE cat_del AS ON DELETE TO ips DO UPDATE categories SET nentries = (categories.nentries - 1) WHERE (categories.catid = old.catid); CREATE RULE cat_upd AS ON UPDATE TO ips WHERE old.catid <> new.catid DO (UPDATE categories SET nentries = (categories.nentries - 1) WHERE (categories.catid = old.catid); UPDATE categories SET nentries = (categories.nentries + 1) WHERE (categories.catid = new.catid); ); 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. I have not found an easy way to maintain the counter up-to-date. I have found a complex solution: I created a "counter" table with two fields, "catid" and "value". The idea is to put 1 in "value" for every insertion or new value for update, or -1 for every deletion or old value for update. CREATE RULE counter_ins AS ON INSERT TO ips DO (INSERT INTO counter (catid, value) VALUES (new.catid, 1); UPDATE categories SET nentries = nentries + (SELECT sum(*) FROM counter WHERE counter.catid = categories.catid) WHERE (categories.catid = counter.catid); DELETE FROM counter; ); (I do not show the equivalent "ON DELETE" and "ON UPDATE" rules) I have two questions: 1) Is this way of doing things correct? Do I have the guarantee that all the commands in the "DO" part will be executed in a transaction even if the initial insertion into "ips" isn't? 2) What is the simplest way of doing this? I guess doing stats in a database is quite a pretty usual operation. Thanks in advance. Sam PS/ the real problem is more complex, as we need to do those statistics on several fields, not only "catid" -- Samuel Tardieu -- [EMAIL PROTECTED] -- http://www.rfc1149.net/sam ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]