• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 897
  • Last Modified:

Efficent Forum System...

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`)
) TYPE=MyISAM AUTO_INCREMENT=5 ;


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`)
) TYPE=MyISAM AUTO_INCREMENT=1 ;


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`)
) TYPE=MyISAM AUTO_INCREMENT=1 ;
0
neester
Asked:
neester
  • 4
  • 3
1 Solution
 
sciwriterCommented:
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.
0
 
Eddie ShipmanAll-around developerCommented:
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:

http://www.phpbb.com
http://www.vbulletin.com
0
 
neesterAuthor Commented:
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.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
sciwriterCommented:
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!!
0
 
sciwriterCommented:
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.
0
 
neesterAuthor Commented:
I dont quite understand what the last part you said meant...
lol...

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...
0
 
neesterAuthor Commented:
lol.
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 :)

easyyy!
:)
0
 
sciwriterCommented:
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....
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now