Is this a solution for large threads?

The topic of large threads being detrimental to mysql performance is known to all big board admins. But why is it this way with vbulletin? Do all forum software have this problem? Does Gaia online suffer from similar drawbacks?

vb stores the threadid and dateline in the post table. Whenever a bunch of posts needs to be selected, vb filters the post table by threadid and orders the results by dateline, and takes a few of the posts (of course there is an index involved). In other words the query is similar to

SELECT * FROM post WHERE threadid=$threadid ORDER BY dateline LIMIT 8575,15

which means that mysql will have to throw away the first 8575 results and then return the next 15. If my understanding is correct, this is the reason why large threads are bad. We need only 15 rows, but mysql has to analyse thousands.

The post table is read in this manner very frequently, every time showthread.php is run. Other accesses to the post table are far less frequent. They are showpost, newreply, newthread, edit post and miscellaneous moderator functions like delete post, move/copy post, merge posts and merge threads. vb's method of doing things means that the frequent requirement (showthread) is slow whereas the infrequent requirements are much simpler and faster.

Now consider an alternative.

What if the position of the post in the thread is stored in the post table? Lets call this field 'postpos'. The first post in a thread will have postpos=1, and the 19th post will have postpos=19.

Now, the query for showthread will be something like

SELECT * FROM post WHERE threadid=$threadid AND postpos BETWEEN 8576 AND 8590 ORDER BY postpos

This should be extremely fast, and should be equally fast for any page in a thread of any size.

Of course, this means that the postpos column must be updated by all relevant modules of vbulletin. It will need to be given a proper initial value when a new post is made, and it will need to be updated whenever a post is moved, copied, merged or deleted. It will also need to be updated if a thread is merged or split. The additional code on newreply will not be running any heavy query, so it will be quite fast. Other functions may be slow for large threads, but since all functions except adding a new post happen quite infrequently, it should not matter so much.

Am I making a fundamental mistake somewhere? Is this feasible? Can this be done using plugins and without code edits?

[orban]

Yes, this correct, MySQL has to skip the first 8574 posts, that's why those queries are "slow", but they aren't that slow, really, I think. 8000 rows isn't that much (and it's fully indexed).

Yeah, adding postpos, or "page", could be a solution, but the issue is that deleting posts/moving/merging is a bit of a pain.

Worst case is that you remove the second post of a thread with 10000 replies and then you have to update 9999 rows, which is veeeeery bad. Then again you will hardly delete/move old posts in a thread and just on the last pages, where it's not a big issue to update a few dozen posts....I guess...

Mainly making sure you don't miss any actions that modify thread positions is the issue here and the problem that in certain cases you have to update many many many rows with a new postpos value.

Probably could be done just with a plugin (maybe modcp/admincp post moving options might be a problem).

[amcd]

8000 was just an example. We have a thread with more than 700,000 posts. The admins did not like members doing off-topic chatting in any and all threads, so the members made a thread only for chit-chat. Now that thread takes 20 seconds or more to load, and we have quite a powerful dbserver. We keep that thread closed, and another 'current' thread is present for chatting. The current thread is merged with the main thread once it reaches 8000 posts or so. The members are not happy with this.

I read elsewhere that large forums auto-split threads larger than 1000 posts. We have hundreds (maybe thousands) of threads significantly larger than 1000 posts.

[kmike]

You can alleviate the need to retrieve all thread posts by using a reverse index on dateline when looking in the second half of a large thread. This solution was first introduced by the admin of the infamous www.Gaiaonline.com forum (which is currently at 979 million posts - simply incredible!). You can see it here:
http://www.phpbb.com/community/viewt...135383#p771664

Implementing it will greatly decrease the number of retrieved posts for sorting, since most of the post accesses are near the thread end (members reading last posts), and in the worst case of reading near the middle it'll require retrieving and sorting only through half of the thread posts.

In fact, I wonder if this solution should be brought to the vB devs' attention, many forums are suffering from the huge thread issue.

[amcd]

Thank you, kmike. That is an interesting approach. That thread also looks like a good read otherwise as well. Will read the whole thing when I get some time.

Do you see anything wrong with the approach I am planning. Should I go ahead and try it?

[kmike]

There's nothing wrong with your approach, but as orban already pointed out, there're too many places you have to track to keep the post positions updated, and renumbering the posts on every delete/merge/split is a costly operation. Also, you'd have to find the last postpos in a thread for every new post, which will be far more frequent operation if your forum is active.
Personally, I wouldn't bother with this approach.

[bigbadbob0]

I'm pretty sure MySQL is optimized to not do stupid things when given a LIMIT. So this query probably isn't wasting quite as much as you're imagining.

http://dev.mysql.com/doc/refman/5.0/...imization.html

[kmike]

There's no imagination involved. A simple explain on any showthread query for a large thread will show an estimated number of scanned rows. Heck, doesn't "LIMIT 10000" for a 10000+ replies thread ring the bell?
It may look like a stupid thing, but to fetch a post at the end of a thread MySQL has to sort through all the previous posts (ordered by dateline ASC).

[orban]

Originally Posted by kmike

You can alleviate the need to retrieve all thread posts by using a reverse index on dateline when looking in the second half of a large thread. This solution was first introduced by the admin of the infamous www.Gaiaonline.com forum (which is currently at 979 million posts - simply incredible!). You can see it here:
http://www.phpbb.com/community/viewt...135383#p771664

Implementing it will greatly decrease the number of retrieved posts for sorting, since most of the post accesses are near the thread end (members reading last posts), and in the worst case of reading near the middle it'll require retrieving and sorting only through half of the thread posts.

In fact, I wonder if this solution should be brought to the vB devs' attention, many forums are suffering from the huge thread issue.

How would you go about implementing a reverse index?

http://www.mysqlperformanceblog.com/...limit-queries/



Oh!

OH!

Does vB do JOINs on the LIMIT 10000000,10 queries?

[amcd]

no, apparently vB does not do that. vB first runs a simple query on a single table (post) and gets the relevant postids. Then it runs the complex query only for those postids.