Hey there, I'm developing some forum software where the emphasis is on speed. Right now, and have so far been storing the current number of threads and posts in a single database record, which is updated when a new thread or post is made, and the number of pages to display for the forum is based upon those figures.
However, I now realise that I can no longer do this, with features such as 'display topics from last x days' and 'hide topics i didn't start'
I was thinking of doing a COUNT() with some WHERE clauses to get the total applicable topics. However it struck me that once the forum starts to get a few hundred thousand posts, this is going to take a while.
I did a little research and found that invision was using the same system i thought of using COUNT(), so I did a little more research and found an active invision forum and added the debug tag to the end of the URI to get the query data.
I found that this COUNT() query is using some serious CPU/DB time to complete.
http://idolforums.com/index.php?showforum=35&prune_day=5&debug=1
Most times you load it, that query is taking 10x longer than any other query, and is highlighted in red.
Can anyone think of a feasible way of doing this, without the impact on the server?
Thanks
-az
EDIT: I should mention i'm using mySQL.
