Re: counting rows

Tony Rogerson schrieb:
> I'll answer that question with another question - do you want it accurate?
> In which case you'll need to serialise anyway.
>
> Locking will be there anyway because of the inserting of the picture row
> data.
>
> The update of count is simply modifying 4 bytes on a single row which would
> be accessed via a unique index (very little IO), do you really think the
> contention will be with the update?
>
> By doing COUNT(*) and serialising the query will cause considerable
> contention because the COUNT(*) query will be a lot longer.
The count has to be protected from modification by accident, code bugs or someone who doesn't understand the schema. Stuff like this makes a system more fragile.

I'd consider that trigger stuff only after some serious profiling. And even then I'd first look what else the database has to offer in order to solve that problem.

Here's a discussion on a related subject (summing up rows in a child table): http://tinyurl.com/ktc2l

Lots of Greetings!
Volker Received on Sat Apr 01 2006 - 22:58:05 CEST