WordPress Database Cleanup: Steps and Plugins To Keep Your Site Unpolluted and Fast

I’ve been managing (and mis-managing) SQL databases for almost 37 years now. SQL was the second programming language I mastered and to this day it is still one of my favorites.

In all that time of wrangling databases, one thing has remained constant, you have to actively manage your database and at times, that means cleanup and maintenance. WordPress databases are no different.

Why Clean Your WordPress Database?

One of the most important reasons to do WordPress database cleanup is because your database is the second slowest part of your entire system. The only thing slower is the file system itself. So making sure that your database is operating at optimal speed is important not only to your site health but to your search engine rankings. Most of the major search engines use page speed as a factor in ranking a page. For optimum WordPress performance, you want to keep the database clean. Cleaning your WordPress database is part of an overall process of WordPress performance tuning.

The vast majority of WordPress installs use a MySQL (or MariaDB) Relational Database Management System (RDBMS). That is what we will focus on in this article. Even so, most of the advice will apply to whatever datastore you are using for your WordPress database.

Steps to Take for an optimal WordPress Database Cleanup

Back Up First

Before you embark on this journey, make sure you create a fresh backup. Yes, you should have nightly backups from the past 30 days nearby but things may have changed since your backup. You want to make sure that if you mess something up, you can easily restore things to the point where you started.

If your hosting partner offers an on-demand backup system like SiteGround does, make one now.

If not, before you go any further, install a plugin that will allow you to create and restore database backups.

There are many good plugins out there that will do the job. I’ve worked with several of them and looked at others. For this job, backing up and restoring, I like UpdraftPlus WordPress Backup Plugin because it has a very easy to understand restore feature. The free version will get the job done for our purposes but the paid version adds a lot of nice to have things. (like support for WP-CLI… that is really important to me in most plugins.)

Once you get the plugin installed, go ahead and make a backup. Unless you are deleting files, there is no reason to backup everything, just backup the database.

After you make your backup, go ahead and restore it so you can see what it’s like before you get in an emergency situation. Remember, if you have an untested backup system, you don’t have disaster recovery, you have hopes and wishes.

Leftover tables in the WordPress Database

Now that you’ve got your backup system ready, this first item is one that you may want to bring a developer in on. I’ma  big fan of bringing in professional help when I’m in over my head and this task may put a lot of people in over their heads.

If your site has been around a while, the chances are good that you have installed and uninstalled a lot of plugins. My personal site has been running WordPress since 2005. Many plugins, especially the older ones, did not clean up after themselves. This means that during install, they may have created tables in your database that they did not delete when you uninstalled them. Look at your list of tables – if you don’t know how, you don’t need to be doing this without help – and try and identify any that are no longer in use. Remove those tables from your database.

Once you’ve removed them, test EVERYTHING. It is these kinds of operations that make me recommend having a staging environment up and running and test these things in staging first. Still, if you have your backup, you should be good to go. 

I looked at the database for my personal site a year or so ago. There are over 500 tables in it. The standard WordPress install has around 40. I really need to take the time to clean things up myself.

There are plugins that will help you with this task if you do not have a developer available. One of them is mentioned below, WP-Optimize. 

Don’t be alarmed if when you try and remove a table it fails. The database user that your WordPress install runs under may not – probably SHOULD NOT – have table delete privileges. Still, you can show this list to your developer and they will know how to safely remove these tables.

Revisions

Next up, let’s talk about Post revisions. As you are working on a Post or Page, WordPress is constantly saving it in the background. To allow you to go back and restore one of these versions at a later date, each revision is stored as a separate record in the posts database. Once you have finished editing your Post and pushed it live, these revisions serve no purpose. 

If you have a developer on staff, they can easily delete these records from the table without having to install any plugins. If you don’t have a developer, or you want to make a change that will keep this problem solved for you, check out WP Revisions Control. This simple plugin will allow you to set how many revisions to keep and it will keep your database clean for you, automatically.

Draft Posts

Another source of “cruft” in your database is old posts that you started but never released. Again, using my personal site as an example, I looked one time and I had well over 50 posts in DRAFT status. This means not only did I have 50 records in my database I didn’t need but I also had revisions for each of them just hanging around. Get rid of those old posts that you are never going to release.

Empty the Trash

As an administrator of a WordPress installation, you have deleted old posts, pages, and other things. WordPress doesn’t actually remove these old entries from your system, it moves them into “the trash”. (From a technical perspective it changes the status of these items.)

The Trash is great if you need to go back and retrieve something later on but after a certain amount of time, you can be fairly confident that you aren’t going to need what you trashed. So empty “The Trash”. When you do this, you actually delete the items that WordPress has marked as trash. This gets them out of your database and keeps them from hanging around and cluttering things up.

Things Not To Worry About

There is a lot of advice on what you should do to clean your WordPress database out on the Internet. Some of it is well meaning but not all that useful. Here are a couple of things that you don’t need to worry about.

Cleaning Transients

Transients are little pieces of information that WordPress and plugins need to store temporarily. Transients have an expiration date. Some people will advise you to make sure you cleanup expired transients every once in a while. Don’t bother. WordPress’ cron system will do this for you every night. At best, you may delete a few hundred records a few hours early. The effort you put into managing transients is effort you could better spend making your site pretties, faster, or more useful to your readers. 

Optimizing Tables

A lot of places will tell you to “optimize your tables”. Some might even explain what this means while others just used it as a magic incantation that is supposed to make you feel better about your database. The truth is that there was a time when optimizing database tables was important. Thankfully those days are long gone. These days most MySQL databases use tables that don’t require optimization. Even my crusty old personal site’s database no longer needs to be optimized. Technically, it is no longer necessary to do this for 99.9% of WordPress installations out there. If you are part of the other .01%, instead of optimizing your tables, move your site to a modern version of MySQL with a table type that does not need optimization

Plugins that do all of this for you

Yes, there are things you need to do on a regular basis to keep your database clean and healthy. Yes, you or a programmer working for you can do these things manually on a regular basis. However, if you are like most of us, you don’t like having to remember to clean things up. For those cases, there are plugins that can do all of this and much more for you, automatically.  Think of them as the Roomba for your site. These plugins will come out while you aren’t around and take care of cleaning up the dusty corners of your site for you on a regular basis.

Here are four options you can consider:

Advanced Database Cleaner

This plugin is available in both free and premium. The free version will do all of the basics you need done. You can even schedule it to run on a regular basis so that your database stays clean. The Pro version will give you many more options. I recommend starting with the free until you understand what it does and then if you feel the need, move to the Pro.

Optimize Database after Deleting Revisions

This is another good choice although it does offer “tabel optimization” that I just got through telling you was unnecessary. It will keep your database clean of old posts, comments, revisions, and a bunch of other things. There aren’t as many options as Advanced Database Cleaner but the ones you really need are there. This plugin is free and there is a link to donate to the author if you use it and find it useful. (and I always suggest you donate to the authors of free plugins if you use their plugins) 

If you do choose to analyze the tables, it will attempt to run analysis on them but if you are using a table type that doesn’t require this feature, it will tell you that. It won’t harm your database or any of your data.

WP-Optimize – Cache, Clean, Compress

Probably my favorite option though (and the one I have loaded on my personal site) is WP-Optimize. This performs all the functions we’ve discussed here plus a lot of other things. Like the previous two, you can schedule this plugin to run automatically. However, since this is written by the same team that wrote the very first plugin I recommended, UpdraftPlus WordPress Backup Plugin, it integrates with that seamlessly and gives you the option to automatically back your database up before it performs maintenance. This is a nice feature. Like Updraft Plus, WP-Optimize comes in a free and paid version. I’ll leave it to you to decide whether you need the features present in the paid version.

Speed Optimizer

If SiteGround is your hosting partner then make sure you have the Speed Optimizer plugin installed. This is a MUST INSTALL plugin for every site hosted with SiteGround because it’s free speed. This is an easy plugin to install and while the database is not it’s primary focus, it does do routine database maintenance. It also does a LOT more to speed your site up and keep it fast. For the full details on this great plugin, check out this blog post.

Wrap Up

Keeping your WordPress database clean is an important step to making sure that your site always runs quickly. Either invest the time to manually clean your site on a regular basis or install and configure one of the recommended plugins to do it for you. Either way, it is time well spent.