Let's say I have a forum. A small forum with maybe 100 visitors a day.
Would the best way be to store the number of posts a topic has by just creating a column num_posts and each time a user makes a post in that topic I just increase that number by one. And the other way when a user deletes a post. Or just make a query?
SELECT COUNT(*)
FROM posts
WHERE topic_id = thetopicid
I prefer the second. But off course I guess it affect performance. But how much? Is this bad practice?
Use count(*). Having that extra column requires you to maintain it yourself, i.e. update on new and deleted posts. You need to add something extra to do this which definitely requires extra resource, whereas using count(*) you are using something already built in to DMBS.