UBB.threads PHP Forum Software Community Forums General Suggestions and Feedback New database structure for Posts: new table for each calendar year

As data capacity is skyrocketing nowadays (blue ray, even email accounts are now given limits in gigabytes and not megabytes, pendrives with 8 gigs, etc.), purging posts to create space is no longer an issue.

The Posts table is by far the largest in the database, and this can bog the server down as the table shoots past 1.5GB worth of posts.

Wouldn't a good way forward be for the forum software to create a separate posts table for each calendar year?

This way the entire board wouldn't be based on a single posts table which can reach massive proportions over time, but would instead be neatly organized by year: ubbt_POSTS_2008 , ubbt_POSTS_2009 , ubbt_POSTS_2010 , etc.

The search engine would also have it easy in terms of search dates and which table to sift through.

Sorry, but for some of us, it's still an issue. We are on sponsored webspace with themepark.nl, and our limit is basicly our limit.

So don't write of auto purging psots of yet

I gave it some more thought, and there is a very easy way to almost half the size of the posts table.

The USERS tables are all split up to maximise performance, and you could do the same for the POSTS table. If you would split of the actual content of the post with the UBB Markup code in it, you would end up with 2 smaller tables. The marked up version is only needed when quoting messages and editing posts.

Keep discussing this guys, sounds very interesting...

the posts table is split off from the topics table for performance. the markup version vs the parsed version of the table are also split off into seperate fields in the same table.

In the long run, with a board that keeps growing and growing, the posts should be split up as sooner or later you end up with a huge 1.5 GB table that still continues to expand and is less effective to deal with.

Splitting up and arranging them according to time/date (ubbt_POSTS_2008 , ubbt_POSTS_2009 , etc.) seems to be the most logical way of doing it, especially when it comes to the search function.

time/date looks handy, but in practise it's not too nice, especially in januari where you have to display posts from both this year and last year.

If you divide up the posts you have to use some criteria, and if it's to be done automatically then some sort of time-based division seems to be the most logical way. Unless it makes sense to create new tables due to size, so as soon as one table reaches say 500 megs then a new one is formed?