Performance question sql

Question

I'm making a forum.

And I'm wondering if i should store the number of replies in the topic table or count the posts of the topic?
How much slower will it be if i use sql and count them? Lets say i have a billion posts. Will it be much slower? Im not planning on being that big but what if? How much slower would i be compared to stroing the num in topics?

Thanks

Answer 1

It will be slower as your db grows in size. If you are planning on having a large post table, store the value in the topic table

Answer 2

I just ran some tests on a MySQL 4.0 box we have using a table with over 1 million records.

SELECT COUNT(*) FROM MyTable; ~1 million took 22ms

SELECT COUNT(*) FROM MyTable WHERE Role=1; ~800,000 took 3.2s

SELECT COUNT(*) FROM MyTable WHERE Role=2; ~20 took 12ms

The Role column in this case was indexed and this was connecting to the MySQL remotely.

I think your posts table will have to get very large for the query times to really become an issue. I also think it is a pre-optimization to put the cache of the count in your topics table. Build it without it for now and if it becomes a problem its a pretty easy update to change it.