9 Steps to Clean Your WordPress Database

Is your WordPress database a mess? Have you been complaining about it since time immemorial? Well, then your WordPress site might be as bloated as you were after that marvelous Thanksgiving dinner at your mom’s. If your site has been live for a while now, chances are that its database has acquired quite a bit of garbage and is demanding a spring clean.

The trick to maintaining a database is to clean it regularly to prevent the accumulation of unnecessary data. However, we are guessing that you have been skimping on the cleaning bit, which is why you are here today. Therefore, without wasting time, we will tell you how to clean up that database in a few easy steps.

#1. Basic Ground Rules

Before you take on the massive task of spring-cleaning, you must ensure that your valuables are safe and sound. Losing a single system file can mess up your website entirely and can cause you to lose thousands of dollars in revenue.

So start by creating copies of important files and create a backup of your entire website. You should definitely check out Backupbuddy from our friends at iThemes. It's a great plugin that allows you to schedule automatic full or database-only backups, and we highly recommend it.

#2. Screen Through the Clutter

Do you remember how yard sales used to work before Craigslist and eBay came along? Just like separating the valuables from trash, you need to pick out the plugins you use and the ones you don’t.

You can easily get started by going through your complete list of plugins in your WordPress dashboard. Go to your admin panel and click on Plugins. Next, go to Installed Plugins and click on Inactive at the top of the screen.

Eliminating unused plugins should easily free up a couple of bytes and is the first step towards cleaning up your WordPress database.

#3. Time For a Purge

This might sound very ominous (esp. to the ones familiar with the popular movie franchise), but it is the best way to free up space. After deleting all unused plugins you should start cleaning out post data.

If you have been using WordPress from a while, you might not be as surprised to know that all your post data is being stored in your website database. You can find it all in the wp_postmeta table, and you can run this query to get rid of all superfluous data:

DELETE FROM wp_postmeta WHERE meta_key = ‘your-meta-key

Always remember to replace the last part with the value that has to be cleared out.

#4. Take Care of Spam

Spam is no longer restricted to your inbox. If you look through your database carefully, you will find tons of spam comments as well. These include promotional features and unscrupulous backlinks that other sites use to eat their way into your SEO. Monitoring comments is an essential practice that is currently underrated.

The easiest way to manage spam comments on your site is to execute this query:

DELETE FROM wp_comments WHERE comment_approved = ‘spam';

If you are currently using a multi-site installation of WordPress, you may like to try the following query:

DELETE FROM wp_#_comments WHERE comment_approved = ‘spam';

#5. What About Comments Waiting on Moderation?

As a WordPress website/blog admin, you have the power to delete all comments that are awaiting moderation. This eliminates the need of unnecessarily going through each and every spam comment that you haven’t yet moderated.

Just a word of advice: go through the list once to approve all genuine comments before running this query:

DELETE FROM wp_comments WHERE comment_approved = ‘0';

#6. Taking Care of Unused Tags

This has happened to almost all of us. When we were new at blogging, we created a few hundred tags thinking we would use them from time to time. And now that we post regularly, we hardly ever change tags. We use the most common ones and stick to those.

Don’t be surprised if you have completely forgotten about a score of tags that you exist on your website database (thanks to your wild imaginative powers). Fortunately, we have a query that will take care of all unused tags, but be sure to check the ones that you do use before running this query:

DELETE FROM wp_terms wt
INNER JOIN wp_term taxonomy wtt ON wt.term_id = wtt.term_id WHERE wtt.taxonomy = ‘post_tag' and wtt.count = 0;

Check out some of our earlier posts if you are unsure about the differences between categories & tags and how to use them.

#7. Bid Farewell to Pingbacks

If you are currently using a setting that has turned off the option of accepting pingbacks, then you may want to use the following code. This will remove every pingback ever made to your website from your database.

DELETE FROM wp_comments WHERE comment_type = ‘pingback';

Again, if you are currently using a multisite installation, you may try using the next one. just remember to replace # with your current site id.

DELETE FROM wp_#_comments WHERE comment_type = ‘pingback';

#8. No more Post Revisions

Post revisions are indeed necessary for blogs, but once you see the space they take up on your database, you won’t think twice about bidding them farewell. They grow fast and exponentially. You can clear them out at one go using the next query:

DELETE a, b, c FROM wp_posts a LEFT JOIN wp_term_relationships b ON (a.ID = b.object_id)
LEFT JOIN wp_postmeta c ON (a.ID = c.post_id) WHERE a.post_type = ‘revision'

You can even chose to disable all post revisions for your website using the following SQL code:

define(‘WP_POST_REVISIONS'), false);

#9. Getting Rid of Trackbacks

Your final step should be getting rid of all old trackbacks. To make sure that nothing goes wrong, double check so that all your pingbacks and trackbacks have been disabled before trying the next SQL code:

DELETE FROM wp_comments WHERE comment_type = ‘trackback';

For multisite users, the next query should be more profitable. You can use this one to clean out all your trackbacks at one go. Like previous ones, replace # with your current site ID to remove all of your site's trackbacks.

DELETE FROM wp_#_comments WHERE comment_type = ‘trackback';

Besides these, you can also scrap your old posts once your audience has fallen out of love with them. This might be difficult at first, but many old posts are nothing but unwarranted burden for WordPress databases.

If you are new and quite lost with the new set of codes, queries and rules we were talking about right here, leave your maintenance to seasoned plugins like Optimize. They work great with multisite installations as well, and include extensive options for managing and removing unused tables.

This article has been written by Derek Iwasiuk from www.engagethecrowd.com.

Derek Iwasiuk

Derek Iwasiuk is a name to be trusted in the world of SEO marketing. He runs a leading national SEO research firm that is highly recommended by successful businesses in the country.

See Full Bio