Great tip for optimization, vbteam, consider this for 3.1

[Nombie Wan]

One of the issues with large boards is large threads, with a lot of replies. Heck, even threads with just 100 replies. The overhead is the count(*) select. So, I was thinking, why not include the reply count (+1) in the query string? Thus, reducing the need for a select count(*) from post.

This will reduce queries in showthread, and also, that specific query can get slower as the number of posts increase.

The only problem I see with this is the new deletion log … think about it though

[Nombie Wan]

For the deletion log issue with non mods/admins you could just add a column to the thread table that listed the reply count not including deletes. :)

Just for clarification, a thread url would look like this:

http://my.forum.com/showthread.php?t=1234&c=28

EDIT: OK, the deletes aren't figured into the reply count anyway, I just figured out.

[Nombie Wan]

Gah, I'm kinda thinking as I post here, one of those nights. :)

Anyway, I was just trying to come up with a "cached" way of eliminating counts if you will. Really, you could do this with thread counts too, when doing page nav for a forum. The nature of forums is a big post and thread table and if selects can be eliminated and cached somehow it would help scaling. I think this should be considered ....

It's probably more of a grabbing that count in another query you're already performing anyway, rather than adding it to the query string.

[Brad.loo]

$getpagenum = $thread['replycount'] + 1;

Not sure if that would work or not, real sleepy atm, but I am ganna be looking at this more tomorrow.

[Nombie Wan]

right, you have the idea.

Really, the bigger issue here is forum display. There's always a count query performed to check page count and that. This can be eliminated with cache. Also, I see a lot of joins on the delete log, which really shouldn't be there, if a cache was available.

Cache the counts on post and thread! :D

[Shining Arcanine]

If that would work, that would be great. :D

[Brad.loo]

right, you have the idea.

Really, the bigger issue here is forum display. There's always a count query performed to check page count and that. This can be eliminated with cache. Also, I see a lot of joins on the delete log, which really shouldn't be there, if a cache was available.

Cache the counts on post and thread! :D
Yea I see what you are getting at, if you could get rid of some of the count processes on most of the tables you could also be free to explor other table types for storage.

[Nombie Wan]

Yea I see what you are getting at, if you could get rid of some of the count processes on most of the tables you could also be free to explor other table types for storage.

:D Another place I was going with this!

It's very doable and just requires some new coding. Just have to make sure these "cached" counts on the post and thread tables are updated any time a post or thread is inserted or deleted.

[Nombie Wan]

OK, so I admit my first suggestion about adding it to the query string is pretty dumb, but having this cache in the right tables would work. You're already querying the db for other things in the same table, so just grab the cached count.