speed/storage compromise

[vindor]

Hi,

I have been creating a discussion room using PHP/MySQL and on several occasions, i.e. when the system must display the list of threads to the user, a count of the number of replies to a specific thread is required:

SELECT COUNT(*) FROM message WHERE parent = $x

I was thinking that perhaps in situations such as this, it would be faster to actually store the number of posts in the message table, instead of counting the number of replies each time a list of threads is displayed. I am aware of the extra storage space that would then be required (I would probably opt for speed and i would be willing to loose the extra storage space).

Have other people had to make similar compromises and what do people think would be better. I would like to hear from some people who have some more experience than me.

Thanks

[planetsim]

Ive actually tried both. Depending on how much visitors you are getting, size of database. There really isnt much of a difference. Itd be slower the way you want to do it when updating but marginally faster when selecting the data.

[Weedpacket]

But only marginally, because if you indexed the table appropriately (i.e,. on the parent field), the dbms would be tracking that sort of information already.

Also, don't forget to take into account the extra amount of time you'd have to spend keeping the field up to date.