[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.