10 Tips for Keeping a Squeaky Clean WordPress (and Multisite!) Database

If you’ve been using WordPress for a while, chances are your site is due for a clean up.

Your database may be full of unnecessary tables that, inevitably, make your site sluggish and bloated.

Doing regular cleanups allow you to reduce the size of your database, which means you backup files will be quicker and smaller.

In today’s post, we’ll look at several ways you can keep your database squeaky clean with simple SQL queries, as well as plugins. We’ll also look at tips for single and Multisite installations.

So grab your mop and bucket and let’s get started.

Basic Housekeeping

Before you get stuck into spring cleaning, it’s important to ensure you backup both your database and your site’s files.

Sometimes things can go wrong seemingly out of the blue. Keeping an extra copy of your site handy can help you restore everything in a flash in case things get messy.

If you’re not sure how to create a backup of your entire site, check out out posts: How to Backup Your WordPress Website (and Multisite) Using Snapshot and Backup Plugins Aren’t about Backing up, They’re about Restoring.

Deleting Unused Plugins

One of the easiest and often most effective ways to reduce the clutter in your database is to delete any plugins you aren’t using.

I have a secret shame: I like to test out plugins, but too often forget to delete them after I’m done. Oops!

I surely can’t be the only one who does this, can I? Perhaps you or one of your clients is guilty of this, too? Or else you install a plugin forgetting you already have a similar one installed. No matter what the case, it’s a good idea to look through your plugins list every now and again and determine which ones should stay and which ones can be tossed away.

Plugins often take up a lot of space in your database so eliminating any unnecessary ones is a great preliminary step toward cleaning house – or rather, database.

How to Enter SQL Commands in Your Database

You guessed it; you’ll need to access your database for a lot of these cleanup options. If you can’t remember your username and password that’s okay because they’re stored in your wp-config.php file.

The code you’re looking for should be similar to this:

In this example, yourusername would be replaced with your actual database’s username, and this-is-your-password will be replaced by your real password.

Once you’re in the phpMyAdmin control panel, click on the database you would like to clean up, then click on the SQL tab.

On this page, you can enter the queries you would like from below then click the Go button to run them.

If you end up seeing a message saying zero tables were affected, then congratulations – your tables were already in great condition for this particular task!

Now that you’re all set up let’s run some queries.

Delete Lingering Plugin and Post Data

After deleting all the plugins you aren’t using, it’s a great idea to purge your database from all the data that’s left over from those plugins. This is also where data from your posts is stored.

It’s all in the wp_postmeta table and running this query can help you get rid of all that unwanted clutter. Just remember to replace your-meta-key with the value you need to clear out.

If you’re using a Multisite installation, try this one instead:

In this query, you need to replace # with the ID of the site you wish to clean up and, again, your-meta-key should be changed to the value you need to delete.

Delete All Spam Comments

Comment spam has been around for what feels like ages and doesn’t seem to be letting up any time soon. If you’re finding an overwhelming amount of spam on your site, this query should clear out all of it.

For Multisite, here’s the code you would like to use as well for all your other sites:

Just replace # to the site ID you would like to purge of spam comments.

Clear Out All Comments Awaiting Moderation

If you have a lot of spam comments that you haven’t yet moderated, this query can help you clear it all out. Just be sure to look through the list and approve any genuine comments before going ahead with this query since all the comments awaiting moderation will be erased.

For Multisite installs, also try this code:

Just like the others, replace # with the applicable site ID.

Begone, Unused Tags!

If you’re like me, you have so many tags that are left unused because you change your mind and edit your tags a few times per post. It can be helpful to clear them out and start fresh. This query will delete all tags that aren’t associated with any posts.

For all your sites in your network, just replace # in the code below with the site ID:

Pingbacks, I Bid Thee Farewell

If you have turned off the option for accepting pingbacks and you would like to remove every single one from your database, this may very well be the query for you.

If you’re running Multisite and want to delete pingbacks on other sites other than your main one, use the following code.

Don’t forget: Be sure to change # to the site ID you would like to scrub clean.

Getting Rid of All Your Trackbacks

While we’re clearing out pingbacks, why not trackbacks as well? This query will help you do just that. It’s probably best to make sure you have disabled pingbacks and trackbacks before trying this one.

For Multisite, just replace # with the appropriate site ID. Here’s the code you need:

Say Sayonara to Post Revisions

For older sites with tons of posts and pages, the revisions can really stack up quickly in your database. To clear them all out, run this query.

Thanks to programmer Joseph Michael Ambrosio, this code ensures all revisions are removed without unintended data loss.

For your network, this code can be adapted to use on other sites besides your main one:

Just be sure to replace # with the relevant site ID and you’re all set to use this along with the original for your main site.

Dumping All Your Shortcodes When You Fall Out of Love

If you’re like me and have installed and tested out many plugins without deleting them after, you may have a lot of stray shortcodes that don’t actually do anything anymore. Maybe you even stopped using a theme that included shortcodes. After deactivating it, you’re left with a mess of unusable shortcodes. Here’s a quick way to clear all instances of your shortcode with an SQL query.

For Multisite networks, here’s the code you can use for your other sites. Just change # to your site ID for things to run smoothly.

Scrap All Your Old Posts

If your site has been around for more years than you care to count, you may have a lot of outdated posts that you would feel more comfortable with if they were just removed altogether. That’s not a problem with this query where you can do just that.

Simply replace #-of-days for the amount of time in days where you want posts to start being deleted. For example, if you would like to delete all posts that are older than time – I mean, five years – you would enter 1825.

For Multisite, here’s the code you would need for other sites in your network:

In addition to changing #-of-days just as the previous code example, you would also need to change # to the ID of the site you would like to clean up.

Got Junk? Deleting All Scrap Comments

Sometimes, your wp_commentsmeta table becomes full of irrelevant approved comments that aren’t stored in your wp_comments table. This may not be a query that’s suitable for everyone, but sometimes this can really be a problem, especially if you deleted a plugin that used the wp_commentsmeta table.

If you would also like to clear out the wp_commentsmeta table for other sites in your network, use this code as well:

Just don’t forget to replace # with the appropriate site ID.

Optimize Your Tables

In just a few clicks, you can also optimize your tables without the use of plugins.

In phpMyAdmin, click on your database and scroll down to the bottom of the page that loads to the right. Click the Check All checkbox at the bottom and then select Optimize table from the drop down list beside it. If you have multiple pages of tables, you’ll need to repeat this step for them.

You don’t need to press the Go button as things will start moving automatically. It may take a while particularly if your site is large so don’t panic if this is the case for you.

You should see a success message eventually.

Now that we have covered all the DIY spring cleaning tips, let’s take a look at some fabulous plugins to help you make cleaning your databases even easier.

Database Optimizing Plugins

These plugins are updated regularly and compatible with single installations of WordPress, although, I have tested them out on Multisite and they worked great. These plugins are fantastic, they make optimizing your database easy and they’re also very popular for good reasons.

• WP-Optimize

  

  This plugin has been around for a long time and it has remained one of the best plugins to help you optimize your WordPress site for free.

  It includes extensive and advanced options for removing unnecessary tables and can even schedule optimizations regularly to help keep your site in tip-top shape. It’s also a breeze to install and set up.

• Revision Control

  

  I really like this plugin because you can limit the number of revisions that are stored in your database rather than deleting them all such as when you use the SQL query in this post. It’s also easy to install and use.

Conclusion

That’s it, you’re all set to clean out your database and even for your entire network. If you would like a list of even more plugins to help you replicate the SQL queries that were covered, check out our post 10 Quick Ways to Clean Up and Optimize Your WordPress Site.

If you would like more information on how you can improve the overall performance of your site, take a look at some of our other posts: How a WordPress Site Becomes Unusable and How to Prevent It, Top CDN Services to Make Your WordPress Site Blazingly Fast (and More Reliable) and The Top 3 WordPress Caching Plugins Compared and Choosing the Best One for Your Site.