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?

[louis_chypher]

An approach I took on the matter of a forum gaining a large amount of post was to first install the mod [https://vborg.vbsupport.ru/showthread.php?t=113324]Auto Delete/Move Thread After X days [/url] and explore this mods capibilities.

A close inspection of the mod as orginally wrote allowed post(s) to be moved from forum(s) to a single destination forum.

What I wanted was to have the main forums on my site with the latest post and sub forums nested within each main forum holding posts from 120 days past. Using theAuto Delete/Move Thread After X days as a model I then changed the code to https://vborg.vbsupport.ru/showthrea...=113324&page=3 post #35. Which then allowed me to specify a souce forum to destination forum moves (main forum and 'archive' forum) as parameters. Now my main forums contian the latest topics of discussion (small db return result sets) and those main forums contain 'archived data' (large db result sets).

The mod runs as a cron job.

[Simetrical]

Here's a thought. Assume we know the number of posts in a thread. We do, it's in the thread table which we presumably have already queried to see whether the user can even view anything (thread deleted, etc.). First of all, to fetch the last page of posts (very common usage case):

SELECT postid, visible, userid
FROM post AS post
WHERE threadid = N AND visible IN (...)
ORDER BY dateline DESC
LIMIT ($numposts % $postsperpage);

I don't know about "reverse indices", but just reversing the sort will do fine. Basically you should sort ASC for the first half of the thread, DESC for the second half. That's already worlds better than the current status. (The funny LIMIT is necessary for the last page, of course.) But now the sneaky part. Say you want to go to the next page from the current one. Use:

SELECT postid, visible, userid
FROM post AS post
WHERE threadid = N
AND visible IN (...)
AND dateline > $datelineoflastpostonpage
ORDER BY dateline
LIMIT $postsperpage;

So instead of a link like showthread.php?t=N&page=77, you get links like showthread.php?t=N&after=1177986896. Exactly the number of needed rows get retrieved. Yeah, it will screw up if in the interim someone's deleted some posts earlier in the thread, but a) that's very unlikely and b) the user still wants to see the "previous page", i.e., the stuff before what he just finished reading, so in a way this is more expected behavior. The same can be done for "previous page".

The only bad spot is "jump straight to page" links, but using the DESC sorting for the highest page numbers will alleviate that, so with these two things combined, the only bad thing will be if someone wants to jump directly to a page in the middle of a thread. But any surviving page= URLs could be sharply limited by auto-redirecting them to appropriate &after= URLs (i.e., never have &page= URLs appear in the URL bar, even if it's useful to have them in links sometimes), so cut-and-paste URLs by users would still be efficient. Then there'd be basically no problem that I see with arbitrarily big threads. Maybe set up a cache in memory to store datelines for middle-of-thread page numbers to avoid the situation of a lot of people clicking a link that brings them directly to the middle of the thread and hasn't been optimized to &after=, just for completeness.


This was all inspired by a remark in a presentation by Domas Mitzuas on the infrastructure of Wikipedia, by the way. I realized that indeed, you'll always find "offset" in paged features of MediaWiki, never page numbers.

[orban]

SEO will like you

A possible 10 gazillion URLs for every thread

[Simetrical]

Heh, like we don't have that already? t=143297, or pick one of p=1213498, 1213537, 1213543, . . ., 1240736, [whatever this post number will be], add on any page=, perpage=, lastpost, you name it. You'll want to rely on appropriate nofollows/noindexes and site maps if you want proper indexing of vB regardless of this, I'm thinking. Threads don't lend themselves well to unique URLs (because who says post X will stay on page Y of the thread, or in the thread at all for that matter?).

[kmike]

The first part of your post, until the offset talk, is exactly what I was talking about in the post #4. You'll need an appropriate compound index ending in "dateline" to take advantage of that however, and scanning it in reverse order (DESC) is why it was called a "reverse index".

Now to the offsets idea, you're just shifting the problem to another place if you want to keep the traditional thread pagination. Now instead of finding a first post on the requested page *once* you need to find the first posts for every page surrounding current page in the navigation area.
For example, looking at the first page in a thread, we'll have these pages in the pagination nav area:
1 2 3 4 11 51 101 Last
and you'll need the dateline offsets for the first posts on the pages 2, 3, 4, 11, 51, 101, and the last page. Ok, getting them for the first 10 pages is easy (though less effective) - just fetch 150 posts instead of 15, but what about pages 51, 101?

Now let's load page 10, what do we have in the pagination nav area?
First Prev 7 8 9 10 11 12 13 20 60 110 Next Last
You'll need an additional query to fetch the navigational data for the preceeding pages 7,8,9, and you still have to handle the pages 60, 110 somehow.

And another thing, basing the navigation on the pure dateline offsets is wrong - for busy enough forums/threads, some posts in a thread may have the same datelines. You can ignore it of course, but to be consistent you have to sort on dateline DESC, postid DESC or dateline ASC, postid ASC everywhere, otherwise the two posts with the same dateline will switch positions when they gradually move in the first half of the thread.