Re: counting rows

"paul c" <toledobythesea_at_oohay.ac> wrote in message news:h7%Wf.201992$sa3.129417_at_pd7tw1no...
> bucaliman_at_gmail.com wrote:
> > 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?
> > Thanks
> >
>
> In a way, it's like the riddles about changing light bulbs (the one I
> like best is 'how many psychiatrists?' - answer 'only one but the light
> bulb has to want to change'). When even mainframes took a second or two
> to count to a million it seemed the fastest way to do that was store
> (pardon me, I should have said 'memorize') the number in the database.
> I think it depends on what the number means to you and what if anything
> its production means to you. I don't believe that any DB dogma has
> anything useful in the sense of right or wrong to say about this.
>
> pc

It depends.

Number 1 is better from the point of view of managing redundancy. Number 2 stores a single fact in more than one place. In this case the "fact" is a count of rows and also the recorded count.

If you mismanage redundancy, unlikely in this case, you can end up with a database that contradicts itself.
(Where the recorded count doesn't match the actual count).

Number 1 is also better from the point of view of data capture. There is less work to do when inserting a new picture.

Number 2 is faster for retrieval. It will generally take more work to count the pictures than to read a recorded count.

If number 2 is too slow at picture insert time, you can sometimes defer adding one to the recorded count, but you have to make sure that the recorded count doesn't get used when it's out of date. (Again, redundancy mismanagement).

For small numbers, this will be a trivial difference. For millions of pictures, it will make a difference. Received on Fri Mar 31 2006 - 13:18:45 CEST