Re: counting rows

I think that the important thing to keep in mind is the following:

We should design & test on a properly normalised database schema. IF (and it is a big if) we get to /real/ and /existing/ performance problems only then we should carefully trade off normalisation (and concequently data integrity) for performance.

The tradeoff depends very heavily on the /actual/ use patterns of our application.

Suppose that an insert to the picture table say takes 3ms, a select count(*) 150ms and
the trigger execution takes 5ms.

Then if every time we insert a picture we execute the count(*) then the trigger is faster (5ms vs 150ms).

If however the count(*) is a rare operation (say once every 1000 inserts) then we will have payed 1000*5 = 5000ms in order to gain 150ms!

So we should be very carefull in denormalizing and only with real world application loads & use patterns.

Let's not forget that premature optimization is the root of many evils.

Lefty Kreouzis Received on Sun Apr 02 2006 - 17:39:23 CEST