Efficent Forum System...

Posted on 2005-02-27
Medium Priority
Last Modified: 2011-09-20
Hey Everyone,

I am developing my own forum system.
I have it pretty much setup and ready to rock.

I haven't too much experience with massive MYSQL databases.
So I am wondering how well they handle.

I am going to have the forum setup like this:

1 table for FORUMS
1 table for THREADS
1 table for POSTS

Each POST links to a thread & forum.
Each THREAD links to a forum.

The THREAD database stores the opening post as well as the title information, this allows easy searching.

How many entries in the database can I have before things get sluggish and cause issues...
I haven't had time to look at how PHPBB and IPB handle threads and posts...
I dont see how it can be done more efficently.
But anyway.

Let me know if this is a bad method.
OR if I am right on.

I would also love to know how many records I can have.

Here is a table dump of how its setup.

CREATE TABLE `forums` (
  `forum_id` int(255) NOT NULL auto_increment,
  `forum_name` char(255) NOT NULL default '',
  `forum_description` char(255) NOT NULL default '',
  `forum_locked` tinyint(1) NOT NULL default '0',
  `forum_invisible` tinyint(1) NOT NULL default '0',
  `forum_weight` int(1) NOT NULL default '0',
  `forum_lastpostby` char(255) NOT NULL default '',
  `forum_lastpostin` int(255) NOT NULL default '0',
  `forum_posts` int(255) NOT NULL default '0',
  `forum_type` int(1) NOT NULL default '0',
  PRIMARY KEY  (`forum_id`)

CREATE TABLE `forums_threads` (
  `thread_id` int(255) NOT NULL auto_increment,
  `thread_name` char(50) NOT NULL default '',
  `thread_author` int(20) NOT NULL default '0',
  `thread_posted` int(50) NOT NULL default '0',
  `thread_category` int(10) NOT NULL default '0',
  `thread_locked` tinyint(1) NOT NULL default '0',
  `thread_lockreason` char(255) NOT NULL default '0',
  `thread_deleted` int(100) NOT NULL default '0',
  `thread_deletereason` char(255) NOT NULL default '',
  `thread_replies` int(100) NOT NULL default '0',
  `thread_invisible` tinyint(1) NOT NULL default '0',
  `thread_lastreply` int(255) NOT NULL default '0',
  `thread_lastreplyby` char(255) NOT NULL default '',
  PRIMARY KEY  (`thread_id`)

CREATE TABLE `forums_posts` (
  `post_id` int(255) NOT NULL auto_increment,
  `post_author` int(100) NOT NULL default '0',
  `post_date` int(100) NOT NULL default '0',
  `post_deleted` tinyint(1) NOT NULL default '0',
  `post_deletereason` varchar(255) NOT NULL default '',
  `post_invisible` tinyint(1) NOT NULL default '0',
  `post_content` mediumtext NOT NULL,
  PRIMARY KEY  (`post_id`)
Question by:neester
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
LVL 23

Accepted Solution

sciwriter earned 2000 total points
ID: 13417641
Standard MySQL can easily handle in excess of 10,000 records without significant degradation.  Over 100K, it starts to show wear.  That is what I collected from dozens of sites over the last year or so.  For a forum, you should be fine, but you do need to clean out or archive the topics, as an integral part of the design.  That's linux based, BTW.
LVL 26

Expert Comment

ID: 13431350
What I would do is emulate what some of the other forums have done. Find one that suits your needs and
figure out how they did it.

Here are two very good ones:

LVL 11

Author Comment

ID: 13433830
Hey SciWriter,

Thanks for the input.
So you think that 100,000 posts would head towards the maximum I should keep??
I can prune posts overtime etc...
So thats not a problem.

I am expecting without pruning to get a few million posts, so I want to be careful.

>> EdditShipman <<
Thanks, I am aware of both systems.
I have used both before.
BUt this is my first time making my own system.

I feel that the premade systems must have a lot of overhead etc...
When I load some pages, it says 13 queries executed.
Where I can plan forums to use 3 queries for everything.

1 query for the current logged in user.
1 query for the stylesheet template.
1 query to load the forums posts...

so I figured I would be better off making my own system.
but yeah.
I am not as experienced as the open source coders.
Lets see how I go.

THanks for the input.

EASY points for anyone else who wants to contribute.
Don't Cry: How Liquid Web is Ensuring Security

WannaCry is just the start. Read how Liquid Web is protecting itself and its customers against new threats.

LVL 23

Expert Comment

ID: 13584522
If you get over 100K posts, it is going to clog almost anything, Linux, Btrieve, SQL, you name it, it is going like MOLAss.

The answer is clearly archives -- when you archive (automatically?) -- which you should consider each month over 10K input -- then you clear out that active indexable cache and spawn it to another database.

Now, let me tell you, when you find these "old hits" on google -- they all say -- that article is "no longer current".  So you are the PHP guru -- you figure out a way to redirect those links to the right page --

www.mysite.com/bbs/latest/topic07321.php  ---->>  /archives/june2003/topic070321.html

Now my boy, if you can figure that translation -- you have beat EVERY site on the internet, because all of them, even the BEST of them -- 90% of these BBS links are outdated about 1-2 weeks after Google indexes them -- so there is your challenge.  No one said you could not beat all the rest --all you have to do is prove it!!
LVL 23

Expert Comment

ID: 13584533
I should have said in the above -- figure out the translation so that the original indexed site auto redirects to the archive -- that is what no one has figured out yet -- but I have confidence, with the htaccess and others U can do it.
LVL 11

Author Comment

ID: 13584536
I dont quite understand what the last part you said meant...

a little htaccess work and you can link those two together :)
I am going to run this system and see how much i can get out of it.

i might have to "auto prune" - as they say - the forum...
LVL 11

Author Comment

ID: 13584549
when i posted i hadn't seen your post...

it says 2 minutes difference...
dunno, tahts weird.

but yeah.

use htacecess to simply strip out the 07321.html and that would be the unique iD for the article.
then check if the link is nested in archives, or in latest :)

LVL 23

Expert Comment

ID: 13584556
The problem with all dated links is that if google says an article is on /forum/d3243.php
and by the time a person looks for this, d3243.php has a totally different set of articles....

Then the original forum comment is not found.  Therefore, you have to somehow track in your database of forum articles, the real link to that comment, and how it migrates through your database, and into what archive it goes.

As long as you have a static archive of that forum topic, now you can use the server redirects (like htaccess, I did not mean that one in particular) to REDIRECT the original /forum/d3243.php to the now-located archive folder.

No one has figured out yet how to do this -- but in the MySQL database, you could easily create a link to the new database, once the article is archived -- 3 dimensional, if you will.  I can see how to do it better than explain it.

It might be more trouble than it is worth, but if you can get it working, you will have accomplished something no one else has done, not even the biggest of them....

Featured Post

WordPress Tutorial 3: Plugins, Themes, and Widgets

The three most common changes you will make to your website involve the look (themes), the functionality (plugins), and modular elements (widgets).

In this article we will briefly define each again, and give you directions on how to install them.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Part of the Global Positioning System A geocode (https://developers.google.com/maps/documentation/geocoding/) is the major subset of a GPS coordinate (http://en.wikipedia.org/wiki/Global_Positioning_System), the other parts being the altitude and t…
Many old projects have bad code, but the budget doesn't exist to rewrite the codebase. You can update this code to be safer by introducing contemporary input validation, sanitation, and safer database queries.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question