WP-Sweep: Answering Your Questions About Database Cleaning

So what’s all this talk about optimizing my website’s database?

Why should I bother?

And what happens if I don’t clean up my database? How to Clean up a WordPress database?

They are great questions, my friend, and we’re going to answer them in this article.

Optimizing is a techie word for cleaning. We’re talking about cleaning up your database.

When you clean the unwanted junk out of your database, it’s going to work better. More efficiently.

Why?

Yep, that’s the way WordPress works.

Let me explain.

Each page on your site is a result of a lot of individual pieces all coming together. Different “pieces” (or files – or sets of files) control things like:

• the overall look of the site (the theme),
• the fonts you’re using (font face, size, color),
• widgets you’re using (and widgets you’re not using at the moment),
• published comments,
• pending comments,
• spam comments that were deleted,
• which version of WordPress you’re running and all the version updates that the good folks at WordPress continue to issue.

And a whole lot more, including the text and images associated with every post and every page, and any drafts you have.

The amazing thing is that all these separate fragments of code all come together flawlessly (well, most of the time). And so quickly.

So your web page is like an electronic jigsaw. When all the pieces have been assembled, you see the complete picture. Of course, instead of little pieces of printed cardboard, your site is made up of little bits of code.

When you click to open a page on your site, PHP (which is the programming language WordPress uses) fetches all the bits of code it needs from all the different places where they’ve been stored, and then it assembles them into the attractive-looking page you and your visitors see.

In the database.

That database is invisible to you and your site visitors. It’s behind the scenes.

[Warning — techie content ahead — skip the next paragraph]

Normal access to the MySQL database is by logging in to your CPanel and opening phpMyAdmin. Then you select the database for your site, and it opens to show you a list of tables. These tables are like charts. Inside those charts is all the information that PHP uses to dish up a web page. You will see a table for your posts content, another table for options and settings, another table for your author bio information, along with tables for every scrap of data that your website has collected.

And that’s the problem. Because inside those tables, along with all the good stuff you still want, there is also a lot of data that is no longer being used. It’s just clutter. It’s info-junk that has accumulated over time.

When you install a plugin, it will often create a table inside the database. But when you deactivate and delete that plugin, the removal process often does not include removing the database table. If you tried out dozens of plugins over the time (and most of us have), you’ve probably got a lot of redundant data that is no longer used or needed.

But plugins are not the only things that cause useless information to pile up in your database, slowing it down.

There are also those spam comments you’ve deleted. You’ve deleted them but they are still there, recorded inside the database.

And if you’ve ever revised a web post, or done it several times (how many times have we done that, eh?), there’s another pile of rubbish clogging up the innards of your website.

Even if you haven’t revised a post, there’s a nifty function called autosave which saves a copy every so often while you’re writing. And you guessed it, all those autosaved copies are filed away in the database, adding to the clutter.

Pingbacks are another cause. They’re all recorded.

Comments. You might have had to delete some spammy or obnoxious comments. They are still sitting there in the database too.

User profiles for users who’ve been deleted.

Deleted posts. We’ve all got rid of the ‘Hello world’ post (well, I hope you did!) and if you’re like many site owners, you’ve got other posts you wrote once but later deleted. Each of those deleted posts leaves behind its meta data. And that goes for drafts too.

Themes. Their info is stored in your database too. Have you ever installed a different WordPress theme and then didn’t like it, so you tried 3 or 4 others until you found one you liked? Odds are that every theme created its own new data fields to store all its information.

All this redundant information in your database is slowing down the performance of your website. Why? Because the server takes longer to find the resource it wants. Also your site is using more disk space on the server.

Smaller backups. When all the junk is removed, your backup files will be smaller and therefore backup times will be faster. If you’re on a really small hosting plan, the reduced file size might be a significant factor.

Efficiency. A smaller database means faster access times. In other words, your site will function more efficiently.

Load speed. We all hate slow-loading websites. Visitors begin to click away after waiting 2 seconds with nothing much to show for it. Source: Kissmetrics.

.

Bounce rate. When your site is a slow loader, visitors click back to where they came from or they just close the page. That’s called bouncing. By optimizing the database of a slow-loading site, you can get improved page load times which will decrease your site’s bounce rate. And Google sees a high bounce rate as a sign of a poor quality site, so it can affect your site’s indexing in Google. Not a good thing, to be sure.

There are two main ways to do it.

1, MANUALLY. You can optimize your site by hand using phpMyAdmin but you do need to be careful about which parts of which tables you manually delete. Why? Because the delete function will completely delete that table and there is no restore function. The table is gone, along with all the data in it.

No, this is not a good approach for the regular blogger, so we won’t cover it here.

2, USE A PLUGIN. In view of the riskiness of doing the cleanup by hand, it’s much better to use a purpose-built software tool… a plugin.

There are several possibilities.

WP-Optimize is one of the most popular ones. According to WordPress.org, it has over half a million active installations. But we are not recommending it.

Why?

Because another plugin is coded better. WP-Sweep uses the delete function that is built into WordPress, rather than direct SQL queries because the latter leaves some data behind. It doesn’t do a complete cleanup job, in other words.

Here’s what Lester Chan noticed about the other plugin…

Who is Lester Chan, you ask.

He’s a prolific web programmer and plugin developer from Singapore, with 25 plugins to his credit already.

Our only recommendation for cleaning your site’s database is to use Lester’s plugin called WP-Sweep. It’s free and you can download it from WordPress.org here or from inside your WordPress installation.

WP-Sweep has over 20,000 active installs and at time of writing this, it was updated just one week ago. Nice! Contrast that with the other plugin which was last updated 3 months ago. This is an additional reason we prefer Lester’s plugin.

What Lester’s WP-Sweep plugin does is this. It uses the proper delete functions that are built into the WordPress core, namely wp_delete_post_revision(), delete_post_meta(), wp_delete_comment() and others. You can see the full list at WP-Sweep’s home page.

By using the delete functionality that the team at WordPress have provided, WP-Sweep can clean out the following:

• Revisions
• Auto drafts
• Deleted comments
• Unapproved comments
• Spammed comments
• Deleted comments
• Orphaned post meta
• Orphaned comment meta
• Orphaned user meta
• Orphan term relationships
• Unused terms
• Duplicated post meta
• Duplicated comment meta
• Duplicated user meta
• Transient options

By way of an overview, it’s a WordPress management tool that checks, repairs, fixes and optimizes your blog.

First, it analyzes your database and then gives you a report of how much outdated and irrelevant information is stored there. This is clutter which can be safely deleted.

Then you can either get it to sweep all the trash out in one operation – by clicking one button, or you can choose individual items to be deleted one at a time if you want finely grained control.

Using this plugin does not require you to have any technical knowledge at all. Even a WordPress beginner can do this. And that’s great news.

But if you do want to control the process manually, WP-Sweep offers these individual options:

• Clean up post revisions
• Clean up duplicate meta-data
• One click delete spam & comments in trash
• Transient Options
• Unused terms
• Auto-drafts

STEP ONE. Before you start, check whether you have any drafts. If you do, they will be removed so you may want to either publish them or take a copy of the content of the draft posts and save it somewhere outside of WordPress.

STEP TWO. Install the plugin. The easiest way to do that is to login to your blog’s /wp-admin area, click on Plugins, click on Add New, type the word sweep into the keyword search box at the top right, and Lester Chan’s WP-Sweep will come up as the first item. Click the Install Now button then the Activate button.

STEP THREE. Open the page for WP-Sweep. It’s under Tools in your admin menu. The first thing you see at the very top of the page is an instruction to backup your database before you start sweeping. This is important, in case anything goes wrong — but it didn’t for me! Having a database backup (or better still, a full site backup) means you can restore your site to its former glory if anything goes wrong during the cleanup.

Fortunately, the same web programmer has provided a good backup tool and there is a link to it. Click the “backup your database” link to read about WP-DBManager by Lester Chan.

STEP FOUR. Install WP-DBManager. That’s a quick trip back to Plugins > Add New. You know the routine by now.

Once installed, you find it under Database in your admin menu. Click Backup Database. Hopefully, once the plugin has checked your site, it will say “Excellent. You Are Good To Go.”

Click the Backup button at the very bottom of the page.

This is a very simple operation, thank you Lester Chan!

If you want to email yourself a copy of that backup file, go to Manage Backup DB.

STEP FIVE. With your database safely backed up, it’s time to use WP-Sweep. So go back to Tools > Sweep.

For the first time you do this, I’m suggesting you use the individual “Sweep” buttons, one at a time. This way you get results faster than waiting for the entire database to be swept. The “Sweep” button changes to “Sweeping” while it does its work and then prints the results just above the section you just swept.

Later you’ll probably want to use the “Sweep All” button at the very bottom of the page.

To also optimize it, go Database > Optimize DB, and click the button at the bottom of the page. Easy peasy!

Now you really have finished your database housekeeping.

FINAL (OPTIONAL) STEPS:

1) If you are curious about how much disk space you just saved, go to Database > Manage Backup DB and it will show you. It’s fun to see how much redundant data you just got rid of.

2) I like to open my site again to confirm it’s working correctly. It was! (If it wasn’t, I’ve always got the backup file.)

3) Some folks who are concerned about disk space will delete the plugin after completing the optimization process, then next time they want to do a bit of website house cleaning, they install it again. Personally, I don’t bother with that.

4) Pop a note in your calendar to sweep and optimize your database once a month.

Your database, though invisible, is one of the most important parts of your website. Look after it and it will look after you.

Also read: 7 Common WordPress Issues and How to Fix Them