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?

I'm using mySQL.

Thanks

Sounds like your in a pickle. ;)

I've looked at the invision debug stats and from observation I would say why not start LIMITing the COUNT() once process time exceeds a specific duration.

Of course, I've never built a forum before and I'm sure doing a LIMIT is completely wrong but its my 2-cents.

interesting...
some thoughts, though not what you are looking for:

1) Don't show a count when the user hides some of 'em.

2) Turn your count into a link.... e.g. "show all 412"

3) Have a counter table instead of 1 counter field.

4) Do your forum with real software, not browser-based?
(whoops... did I say that out loud? lol )


Dan

hehe.
Real Software

Count() is still going to be the fastest way to count the number of results to a query... it's relatively efficient itself, and it's hard to beat the efficiency of mysql's coders. There's a reason vB has that "please wait while your search.." when you do a custom search.

it will help if you have a user setting that defines how far back to display posts from so that if they choose only view a week back, you can put that in your query and speed it up even if the total number of posts in the table is huge...

I've decided to do something like the following:

Counter field in user table, with a serialized array of forums and various counters for each one.

OR

Counter table, same system as above, but with fields for '1 day, 2 days, 3 days, 1 week, fortnight, month, year etc.'

I think i'll go for the latter - it'll be much faster than the COUNT() system :) I'm currently already twice as fast as IPB - maybe i can get even faster?

Originally posted by Informity
I've decided to do something like the following:

Counter table, same system as above, but with fields for '1 day, 2 days, 3 days, 1 week, fortnight, month, year etc.'



You are welcome ;-)

How will you make a counter table with the different periods...

the number of posts in the last week would change every minute.