5 replies to this topic

[webcodez]

Hi there,

Wondering what's better for, for example, forum systems:

to either have a

• counter field of amount of posts of a specific forum/thread inside the database => use mysql query to select that single field that indicated the amount of posts inside the specific forum or thread

OR to

• not have a counter field but use mysql queries to realtime track the amount of posts inside a specific forum or thread ( selecting/counting multiple rows, all rows, for calculating the amount of posts inside the thread at that moment, live )

Any suggestions would be appreciated.

My doubts are because I see pros and cons for both options: first option is faster, and only uses one query to select all fields values, while the 2nd option needs to calculate it by executing multiple queries ( for amount of posts, replies, etc. ) but however is realtime results and doesn't need update queries for updating counters.

[Orjan]

for a minor forum, the realtime version is no problems, but in larger scales, I'd say the counter style is to be preffered, you can always make a counter update routine that is ran for eample every hour or day or whatever to make sure the counters are correct.

that is best done with a cron system, but you could do it as you write "last ran time" into the databasem and if it's not ran within x period of time, run it and update last ran time... that can be done at last thing you do when the page is done loaded, and then the user won't be affected of it...

[webcodez]

Thank you for the response, think I'll go for counter fields then. Was in doubt because some people told me, updating a field ( like counter field ) takes way more mysql resources than selecting rows or inserting rows. Is that true?

[John]

If your MySQL database is using MyISAM and not Innodb doing a SELECT count(*) FROM table is instant because table sizes are cached. Keeping a counter or using a cron is unnecessary overhead.

[webcodez]

Thank you very much!

Was wondering why vBulletin e.g. use counters but reading this it seems not needed when using MyISAM as for database type, thank you for clarifying =].

[Orjan]

John said:

If your MySQL database is using MyISAM and not Innodb doing a SELECT count(*) FROM table is instant because table sizes are cached. Keeping a counter or using a cron is unnecessary overhead.

What about when you do count(*) when you have a where condition then?