Forum Admin: how to keep your forum database clean and fast

Running a forum will force you to learn A LOT of technical aspects, from installation, setup to backups, design and even database tweaks. When you are just starting out your community, you'll have a very small and nimble database, but this can really start taking space and put a strain on the server. Let's see how we can keep our database work fast and be as small as possible.

1. Don't fill your forums with too many plugins

Yep, I know you have just found 50 amazing plugins and they are all so cool. Never forget that 1. your members are there TO POST and 2. EVERYTHING you install, will most likely use your database too. So do choose few excellent plugins to help your forum and stay away from others that are not really that important.

2. Attachments as files, CSS as files - not in the database

For some forums (vBulletin comes to mind) you can set the CSS to be an external file and also the attachments to be set as files and not handled via the database. If you ask me, keep the attachments to a minimum, but, if you do need them, make sure they're not hogging your poor database.

3. Give members FINITE private message numbers. Small, if possible.

This is where I seem to be the mean ogre. So, my members don't have unlimited messages? I even give them ONLY 50? Phew, that's nasty. 'Rewind' 2 years ago. It's summer and my webmaster forums are giving a lot of database errors. MyBB seems to crash under too much strain, though the forums were 40K posts only, so nothing too fancy.

I then enter phpmyadmin to see what's the deal and almost fall off my chair. 'Sweet mother of Jesus, the private message table is HUGE'. It was almost as big as the forum posts. And we're talking 40K forum posts, please let's not forget this.

I go back to my own account and see 300+ private messages. Yes, I'm a lazy bum myself, don't worry. Next move? Announced the entire community to start pruning their old private messages and gave them all 1 week to delete the junk. After 1 week I set the private message numbers from 'unlimited' or an insane number anyway to 50.

The idea of a forum is to discuss IN THE COMMUNITY, not private chats. So 50 is a decent number so that people can still forge deals, but not double up my database.

4. Delete ZERO post members.

At the time my forums had around 7K members. Phew, who's cool? Me. I have such a huge community. And then, when I started looking at their post counts I was shocked: many didn't bother to post. And some of them were in my forums for 2-3 years.

Repeat after me: 'each forum account appears in the database'. This means it adds up information and increases the database space. The bigger the database, the bigger the chances for it to start showing errors and cause a high CPU consumption. And when that happens, your nice web host will invite you to buy a dedicated, even if you have a 400 visits/day forum.

So I selected all accounts that registered, NEVER posted and never logged in back in 30 days (the time can vary, you're the boss here). Then I clicked PRUNE and there we were: 1K members.

You'll tell me it's not a good idea to delete accounts. Let's look it this way: if you join a forum you're doing it to post (my forums have the information available to guests, so you don't need an account to read the stuff). If you can't be bothered in 3 years to post ONE SINGLE message, then please allow me to clean up the space. Sure, if you go back, you can just create the account (since it was deleted, you can use your actual username and email address, nothing blocks them). It takes 2 minutes.

After doing these 2 tricks my database was 20% of what it used to be prior to this. As you can imagine the errors STOPPED completely and we were actually able to use that forum again.

5. Prune those logs

Admin logs, moderator logs, spam logs etc. Many forum features will create 'logs' so that you can take a look at any time. While I don't tell anyone to delete everything, read your logs and delete the ones that are 30 days old for instance. If you're the sole admin/mod (as I am), you shouldn't need logs anyway, I'd presume you remember what you've done.

Run constant log pruning from your forum admin or, if you have the 'guts' go to your phpmyadmin and EMPTY the tables you need to clean. ALWAYS BACKUP THE DATABASE, before you go guerrilla on it

CONCLUSION: you can have a forum that's big and active and runs on a small database or you can hog it with useless junk. Try to keep it simple, make sure everything works OK and that you optimize your data as much as possible. Run pruning for members / logs, see what data can be used as files and don't bother keeping 2 year zero post members.

Do not touch your database if you haven't done a backup and run optimize/repair queries on a regular basis. Keeps the database 'clean' and your forums running nicely.

PS: feel free to add more ideas

Well, we talk about 'members' who have created accounts 3 years ago. That was my case (or similar). I really don't see any of them wanting to come back and post, since it's been 30-40 months since they joined and never posted ANYTHING. You don't need to do this in the first year probably, I haven't deleted inactives here either. As long as the database is still small and we don't have 4-6k members, I don't need to prune anything. Maybe, once we hit, I don't know few thousands members, I'll start cleaning some of the oldest unused accounts. But it's nothing you need to worry right now. Still, in the future, before you look for a dedicated to move the site onto, you can do this little trick and, by cleaning up 1-2K unused accounts you can see a huge improvement in the database.