Ok this is conception only now, but result would be instant access to every topic page/posts page on forum instead of slow pagination by LIMIT. We know that if limit become 10000, 40 its extremely slow especially on high load.
How change LIMIT? Lets take for example topics. We must add two fields to topics_table, topic_page and topic_count (which is described later). Now run simple script which filling topic_page field like this:
Code: Select all
topic_id: 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16
topic_page: 1,1,1,1,1,2,2,2,2, 2, 3, 3,3,3, 3, 4
This topic_page numeration is correct when you have board_config['topics_per_page'] = 5.
Then instead of main viewforum query which is:
Code: Select all
WHERE t.forum_id = $forum_id
AND t.topic_poster = u.user_id
AND p.post_id = t.topic_first_post_id
AND p2.post_id = t.topic_last_post_id
AND u2.user_id = p2.poster_id
AND t.topic_type <> " . POST_ANNOUNCE . "
ORDER BY t.topic_type DESC, t.topic_last_post_id DESC
LIMIT $start, ".$board_config['topics_per_page'];
we have
Code: Select all
WHERE t.forum_id = $forum_id
AND t.topic_page = $page_id <-- change here
AND t.topic_poster = u.user_id
AND p.post_id = t.topic_first_post_id
AND p2.post_id = t.topic_last_post_id
AND u2.user_id = p2.poster_id
AND t.topic_type <> " . POST_ANNOUNCE . "
ORDER BY t.topic_type DESC, t.topic_last_post_id DESC
<-- change here, cutted out LIMIT -->
With compound index on forum_id and page_id grabbing all topics have same speed on page 1 and page 999999.
But... not so simple to achieve it, first of all, new topics must somehow know which page_id write to self when created. This is when topic_count come to play, instead of page_id stored in it, it has always numbers from 1 to $board_config['topics_per_page'] which is our 5 right now. So if new topic is created it grabs max(topic_id) and if topic_count is 4 it write same page_id to self like grabbed record, if it is 1 it write same page_id to self but if it is 5 it write page_id +1.
Then ofcourse update topic_count.
So we have 2 queries more on topic create (select and update) but extremely fast viewing even each of 10.000.000 topics in one specific forum. Great on forums where SELECT rate is around 70-80%.
Some problems could arise when we want move topics from forum to forum. Then topic should wait to global resync of page_id at night or find other solution.
Im finding new problems when im writing it, because what if someone want post in topic which is on page 2, then topic would not come to page 1 (refreshed normally would go to top of first page), it would be just on top of page 2.
Whole idea comes from Lanzer, but he was using similiar method to archive posts to separate table (yes we missed that when we discussed spliting tables). Too bad he not mentioned more about it, its called paritioning or sharding which is not simple task involving much code and database schema changes, but its a must later on.
.
