Re: counting rows

From: Murdoc <murdoc_0_at_hotmail.com>
Date: Sat, 1 Apr 2006 22:38:07 +0000 (UTC)
Message-ID: <xn0ekhvdk10e6x003_at_news-south.connect.com.au>


Tony Rogerson wrote:

> When somebody asks this...
>
> > > Consider the tables USERS and PICTURES. When retrieving the number of
> > > pictures from a user there are two ways:
>
> > > 1) I count them from pictures table.
> > > 2) I pick it directly from a field "numPictures" in table users.
>
> > > How are you used to do this? Which is better?
>
> I give an answer to THAT question and not add in other stuff, if the OP wanted min, max, sum
> etc... then I'd answer differently.
>
> Given 10 concurrent users all wanting the number of pictures and the number of rows is 10,000,
> which in your opinion is better?
>
> -- The engine may need to read 20MBytes of memory per user to realise the result.
> SELECT COUNT(*) FROM yourtable WHERE user = 'x'

Wouldn't it be more efficient to do:

SELECT COUNT(user_picture_id) FROM yourTable WHERE user = 'x'

ensuring of course that

>
> or
>
> -- The engine only needs to read a single row per user, a couple of KBytes at most.
> SELECT num_pict FROM user_details WHERE user = 'x'
>
> num_pict is kept up-to-date using a before (instead of) trigger
>
> The trigger simply does a +1 or -1 each time a row is added/removed.

We actually do that with several types of information: # of transactions in a batch; # of members in a fund; running account balance, etc. However, we do it with code, and not with database-level triggers.

-- 
Received on Sun Apr 02 2006 - 00:38:07 CEST

Original text of this message