{"id":142618,"date":"2015-06-25T11:00:48","date_gmt":"2015-06-25T15:00:48","guid":{"rendered":"http:\/\/premium.wpmudev.org\/blog\/?p=142618"},"modified":"2016-12-13T21:32:07","modified_gmt":"2016-12-13T21:32:07","slug":"squeaky-clean-database","status":"publish","type":"post","link":"https:\/\/wpmudev.com\/blog\/squeaky-clean-database\/","title":{"rendered":"10 Tips for Keeping a Squeaky Clean WordPress (and Multisite!) Database"},"content":{"rendered":"

If you\u2019ve been using WordPress for a while, chances are your site is due for a clean up.<\/span><\/p>\n

Your database may be full of unnecessary tables that, inevitably, make your site sluggish and bloated.<\/span><\/p>\n

Doing regular cleanups allow you to reduce the size of your database, which means you backup files will be quicker and smaller.<\/span><\/p>\n

In today\u2019s post, we\u2019ll look at several ways you can keep your database squeaky clean with simple SQL queries, as well as plugins. We\u2019ll also look at tips for single and Multisite installations.<\/span><\/p>\n

So grab your mop and bucket and let\u2019s get started.<\/span><\/p>\n

Basic Housekeeping<\/h3>\n

Before you get stuck into spring cleaning, it’s important to ensure you backup both your database and your site’s files.<\/p>\n

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.<\/p>\n

If you’re not sure how to create a backup of your entire site, check out out posts:\u00a0How to Backup Your WordPress Website (and Multisite) Using Snapshot<\/a> and\u00a0Backup Plugins Aren\u2019t about Backing up, They\u2019re about Restoring<\/a>.<\/p>\n

Deleting Unused Plugins<\/h3>\n

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.<\/p>\n

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

\"The
If you have a lot of inactive plugins listed on your plugins page, like me, you should consider deleting them.<\/figcaption><\/figure>\n

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.<\/p>\n

Plugins often take up a lot of space in your database so eliminating any unnecessary ones is a great preliminary step toward cleaning house\u00a0\u2013 or rather, database.<\/p>\n

How to Enter SQL Commands in Your Database<\/h3>\n

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<\/em> file.<\/p>\n

The code you’re looking for should be similar to this:<\/p>\n

Loading gist 813f8e7f65492bf70c0c251862a8c6eb<\/a>

Please update your cookie preferences<\/a> to enable preference cookies to view this gist.<\/p><\/div><\/div>\n

In this example, yourusername<\/code>\u00a0would be replaced with your actual database’s username, and this-is-your-password<\/code> will be replaced by your real password.<\/p>\n

Once you’re in the phpMyAdmin control panel, click on the database you would like to clean up, then click on the SQL<\/strong> tab.<\/p>\n

\"The
If you’re feeling adventurous, you can also create your own queries by clicking on the Query<\/strong> tab and filling out the applicable fields.<\/figcaption><\/figure>\n

On this page, you can enter the queries you would like from below then click the Go<\/strong> button to run them.<\/p>\n

If you end up seeing a message saying zero tables were affected, then congratulations\u00a0\u2013 your tables were already in great condition for this particular task!<\/p>\n

Now that you’re all set up let’s run some queries.<\/p>\n

Delete Lingering Plugin and Post Data<\/h3>\n

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.<\/p>\n

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

Loading gist 1c44e4b69266cf7cd38eef5e1cb8ed2d<\/a>

Please update your cookie preferences<\/a> to enable preference cookies to view this gist.<\/p><\/div><\/div>\n

If you’re using a Multisite installation, try this one instead:<\/p>\n

Loading gist 98acb9bdff42924ddd948e3f65928199<\/a>

Please update your cookie preferences<\/a> to enable preference cookies to view this gist.<\/p><\/div><\/div>\n

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

Delete All Spam Comments<\/h3>\n

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.<\/p>\n

Loading gist 30181fe05f9b45f7a2e73e6b750e8b3c<\/a>

Please update your cookie preferences<\/a> to enable preference cookies to view this gist.<\/p><\/div><\/div>\n

For Multisite, here’s the code you would like to use as well for all your other sites:<\/p>\n

Loading gist cdab08adb2ae95dbf8be1e0f33752414<\/a>

Please update your cookie preferences<\/a> to enable preference cookies to view this gist.<\/p><\/div><\/div>\n

Just replace #<\/code> to the site ID you would like to purge of spam comments.<\/p>\n

Clear Out All Comments Awaiting Moderation<\/h3>\n

If you have a lot of spam comments that you haven’t\u00a0yet 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.<\/p>\n

Loading gist 5450cc8ec305c761c56e5b0d81168e43<\/a>

Please update your cookie preferences<\/a> to enable preference cookies to view this gist.<\/p><\/div><\/div>\n

For Multisite installs, also try this code:<\/p>\n

Loading gist 9481cf38f4078213ccb1ffbad7ca0bfa<\/a>

Please update your cookie preferences<\/a> to enable preference cookies to view this gist.<\/p><\/div><\/div>\n

Just like the others, replace #<\/code> with the applicable site ID.<\/p>\n

Begone, Unused Tags!<\/h3>\n

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.<\/p>\n

Loading gist 0638959560b159bcdae4ad7f616b44ed<\/a>

Please update your cookie preferences<\/a> to enable preference cookies to view this gist.<\/p><\/div><\/div>\n

For all your sites in your network, just replace #<\/code> in the code below with the site ID:<\/p>\n

Loading gist eeef2cda78847f6718712eae882f0043<\/a>

Please update your cookie preferences<\/a> to enable preference cookies to view this gist.<\/p><\/div><\/div>\n

Pingbacks, I Bid Thee Farewell<\/h3>\n

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.<\/p>\n

Loading gist a77befd345c153af1860da0301ff913d<\/a>

Please update your cookie preferences<\/a> to enable preference cookies to view this gist.<\/p><\/div><\/div>\n

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

Loading gist 8bcf4e21f74bc230501d09a138f82bc0<\/a>

Please update your cookie preferences<\/a> to enable preference cookies to view this gist.<\/p><\/div><\/div>\n

Don’t forget: Be sure to change #<\/code> to the site ID you would like to scrub clean.<\/p>\n

Getting Rid of All Your Trackbacks<\/h3>\n

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.<\/p>\n

Loading gist fbbdce5f8fc277583a13026ef0b8f523<\/a>

Please update your cookie preferences<\/a> to enable preference cookies to view this gist.<\/p><\/div><\/div>\n

For Multisite, just replace # with the appropriate site ID. Here’s the code you need:<\/p>\n

Loading gist 33719f263d9edff21ee42284caae5fcf<\/a>

Please update your cookie preferences<\/a> to enable preference cookies to view this gist.<\/p><\/div><\/div>\n

Say Sayonara<\/em> to Post Revisions<\/h3>\n

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

Thanks to programmer\u00a0Joseph Michael Ambrosio, this code ensures all revisions are removed without unintended data loss.<\/p>\n

Loading gist b6c0e935a4d8f64a68e0dc23ca797208<\/a>

Please update your cookie preferences<\/a> to enable preference cookies to view this gist.<\/p><\/div><\/div>\n

For your network, this code can be adapted to use on other sites besides your main one:<\/p>\n

Loading gist f4c7dbcee6a2f77ce88c35bc13f8d440<\/a>

Please update your cookie preferences<\/a> to enable preference cookies to view this gist.<\/p><\/div><\/div>\n

Just be sure to replace #<\/code> with the relevant site ID and you’re all set to use this along with the original for your main site.<\/p>\n

Dumping All Your Shortcodes When You Fall Out of Love<\/h3>\n

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.<\/p>\n

Loading gist e521910da1427155de7a1612e41b9178<\/a>

Please update your cookie preferences<\/a> to enable preference cookies to view this gist.<\/p><\/div><\/div>\n

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

Loading gist d366b130ff892a6b97191e6233cda4ba<\/a>

Please update your cookie preferences<\/a> to enable preference cookies to view this gist.<\/p><\/div><\/div>\n

Scrap All Your Old Posts<\/h3>\n

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.<\/p>\n

Simply replace #-of-days<\/code> 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\u00a0\u2013 I mean, five years\u00a0\u2013 you would enter 1825.<\/p>\n

Loading gist c6f203460a675243eadb6824ade775aa<\/a>

Please update your cookie preferences<\/a> to enable preference cookies to view this gist.<\/p><\/div><\/div>\n

For Multisite, here’s the code you would need for other sites in your network:<\/p>\n

Loading gist 1c5f94e4e44a6059d8aaf7bb71d4c908<\/a>

Please update your cookie preferences<\/a> to enable preference cookies to view this gist.<\/p><\/div><\/div>\n

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

Got Junk? Deleting All Scrap Comments<\/h3>\n

Sometimes, your wp_commentsmeta<\/code> table becomes full of irrelevant approved comments that aren’t stored in your wp_comments<\/code> 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<\/code> table.<\/p>\n

Loading gist b9cae59327f252dff9477a5fa227cf01<\/a>

Please update your cookie preferences<\/a> to enable preference cookies to view this gist.<\/p><\/div><\/div>\n

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

Loading gist a257633d9f95d7399c305cd2252b7358<\/a>

Please update your cookie preferences<\/a> to enable preference cookies to view this gist.<\/p><\/div><\/div>\n

Just don’t forget to replace #<\/code> with the appropriate site ID.<\/p>\n

Optimize Your Tables<\/h3>\n

In just a few clicks, you can also optimize your tables without the use of plugins.<\/p>\n

\"The
You don’t necessarily need to check all of the tables, just the ones that are large that you can see under the Size<\/strong> column.<\/figcaption><\/figure>\n

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

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.<\/p>\n

You should see a success message eventually.<\/p>\n

\"The
You may see messages on the page that loads with a “success” message that notes that your tables don’t support optimize and a recreate has been executed. This is usually fine.<\/figcaption><\/figure>\n

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.<\/p>\n

Database Optimizing Plugins<\/h3>\n

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\u00a0for good reasons.<\/p>\n