WP-Optimize: Speed Up Your WordPress Site with Database Optimization

If you’ve been using WordPress for any amount of time, you’ll undoubtedly generate some junk in your database. Especially during the building & design phase, if you’ve tried out lots of different plugins and then removed them, you could have a lot of junk left behind. This junk represents excess data that your WordPress site might have to crawl through every time it’s asked to serve up a website element. As you can imagine, the more junk to crawl through, the slower your site.

In this post I’ll go over some of the more common types of junk you’ll find in your WordPress database and how to get rid of them both manually and by using WP-Optimize.

While this post focuses on keeping your database optimized, there are plenty of other ways to speed up your WordPress website.

What’s Clogging up Your WordPress Database?

This is hardly an exhaustive list, but the items below are the most common things you’ll find in your WordPress database that will cause unnecessary bloat.

Unused Tables

Many plugins add their own tables to your WordPress database as soon as you activate them. And, just like software on your home computer, they don’t always clean up after themselves when you remove them from your site.

Sometimes, this is by design. It ensures that if you remove a plugin and then add it back later, you retain all the settings and configuration you did the first time around. But if you remove a plugin without any intention of using it ever again, these extra tables are just junk that clog up your database.

A good, and rather extreme, example is the WP ERP plugin. It’s a super-powerful plugin that adds a full accounting system to your site along with other modules such as HR management and CRM system. It’s a very good plugin, but overkill for most sites. I installed it on one of my sites once just to see what options it had for tracking expenses. Once I realized it wasn’t what I was looking for, I deactivated it and uninstalled it. So that’s it right? Well, as it turns out, that wasn’t the end of the story. You see, WP ERP created 38 new tables in my WordPress database and didn’t remove them when I uninstalled it!

Post Revisions

WordPress has this nifty little feature where every time you save a post that you’re working on, it creates an entry in the post revision history. You can use the revision history to go back to previous versions of the post.

I’ve been writing blogs for years and I can honestly say I’ve almost never gone back into my revision history to change an article to the way it was at a previous point in time. The times where I have gone back, I usually was just reverting back to a save just a few minutes in the past – 1 or 2 revisions ago.

But I save my posts as I’m writing them dozens of times. Right now, on this very post, I have 13 revisions. For longer posts, I might have 30 revisions by the time I hit ‘Publish’. WordPress doesn’t get rid of these revisions by default; it saves them essentially forever. And each one adds to the size of your core database tables.

Spam and Trash Comments

If you’re anything like me, I empty the trash comments all the time. But, a lot of folks aren’t like me and I have relatively few comments to parse through. If you have a blog that gets hundreds of comments every day, many of which automatically get filtered as spam, you might simply not have the time to go through them all.

Just like post revisions, each spam or trash comment takes up space in your core database and can slow down your site.

Expired Transient Options

WordPress websites aren’t like the old school static HTML websites. WordPress sites are highly dynamic, pulling data from external sources and refreshing data on the fly. But all those external calls can put a drain on your web server’s CPU and bandwidth.

To help mitigate processing overhead, many developers use code that introduces transient values/options into your database. For example, if you’ve ever used a plugin that adds a Facebook share counts to your posts, you might have noticed that it only updates every so often. A good plugin will do that in order to save the counter value in a database entry that expires after a certain amount of time. This ensures that your web server isn’t calling out to Facebook on every single page load for updated information – it’s sort of like caching at the database level.

Each transient value has an expiration time and when that time is reached, the value gets removed from the database and the next call goes out to the external resource for a fresh copy and a new expiration time. Sometimes, when these values expire, they don’t get removed from the database. It’s these stale entries that we refer to as ‘expired transients’.

Cleaning Your Database Manually

To clean some of these items from your database manually, we’ll start with the ones that require the least technical input and then move on to the ones that require more effort.

Easiest: Spam and Trash Comments

Cleaning up spam and trash comments is definitely the easiest cleanup you’ll perform because you can do it right from within your admin dashboard.

Once you’re logged into your admin dashboard, click ‘Comments’. Above the filter at the top, click ‘Spam’ and then click the ‘Empty Spam’ button. After that, click ‘Trash’ and then click the ‘Empty Trash’ button.

In order to keep your site clean, you’ll need to go through and perform the above steps periodically.

Easy: Disable or Limit Post Revisions

One way you can help keep your revision history from getting to crazy is to implement limits on how many revisions to keep of each post, or disable the revision feature altogether. You can accomplish this by adding some code to your wp-config.php file.

To disable the revision feature completely, add the following code snippet:

define('WP_POST_REVISIONS', false );

If you don’t want to disable post revisions entirely and you’d rather limit them, you can use this snippet instead:

define( 'WP_POST_REVISIONS', 3 );

Not Too Hard: Remove Existing Post Revisions

Disabling your post revisions in your wp-config.php file only affects the posts you make in the future. So what about all those posts and pages you’ve made up to now? Luckily you can remove them all with a single command. You’ll need to visit your phpMyAdmin and run this SQL query:

DELETE FROM wp_posts WHERE post_type = "revision";

More Technical: Removing Unused Tables

This is where things get a little more dicey. Because plugins don’t always name their tables as something easily readable, it could be difficult to know which table belongs to which plugin.

Log into phpMyAdmin and select your WordPress database so you can see the list of tables. It should look something this this (click the image to enlarge):

The image above is just a portion of the tables you might see listed (you could have 100 or more). While removing a table from your database is as simple as clicking the ‘Drop’ link in the row, the challenge is identifying which tables belong to which plugins so you know you’re removing the right tables. In most cases, a Google search for the table name should be enough to identify what plugin it belongs to.

Dropping a table is permanent. It’s strongly recommended that you take a database backup prior to dropping any tables.

Difficult: Removing Expired Transient Options

Unfortunately there’s very little you can do manually to remove expired transient options from your database if they don’t get removed by the system. Whenever WordPress upgrades your database, all transient options should be removed so as long as you’re updating each time a new version is released, you’re probably OK.

Luckily, if you want to search out transient options, they only really live in the wp_options table (one of the WordPress core tables). I’m not going to go into the details, but an easy to read write up can be found over on WPBeginner which includes a plugin made specifically for that purpose. Before you head over there though, I have a better solution…

Clean Your Database With WP-Optimize

It might seem pretty late in this post to finally talk about WP-Optimize (it’s in the title right?) but you really need to look at what goes into these cleaning tasks first before you can get a true appreciation for what this plugin does with just a few clicks.

What WP-Optimize Does

WP-Optimize is a lightweight plugin that does everything I’ve mentioned so far, and a lot more. What’s better is that it does it all without you having to know any code or MySQL commands.

When using a tool that modifies your database, it’s important to use one that you can trust. One of the reasons I choose to use and recommend WP-Optimize is because it’s made by the same people that make UpdraftPlus, my go-to plugin for backups on all my sites. Their coding skills have never let me down.

If you use UpdraftPlus (and I highly recommend you do), WP-Optimize will allow you to automatically take database backups via UpdraftPlus before every modification it makes. This ensures you can cleanly restore your database in case the unthinkable happens.

The feature list is is fairly extensive for a free plugin. They do also have a premium one, but the free one should really be all most folks need to keep their database running clean. Here’s a quick rundown on what it can do and how to use it.

Removing Unused Tables

WP-Optimize takes the guesswork out of finding and removing any unused tables left behind by old plugins. Not only does it tell you whether a table belongs to something that’s no longer installed, it’ll also tell you if it’s an inactive plugin. In most cases, it’ll even provided you a link under each of those tables that you can click to take you right to the plugin’s WordPress.org page for more information. A simple click of the “Remove” button gets rid of the table forever.

Post Revisions, Spam & Trash Comments, & Expired Transient Options

On the main optimizations menu, you’ll find a list of various optimizations you can run. They can be run individually or all at once. In addition to the items I’ve mentioned above, the first selection for table optimization is one of your biggest space-saving operations. It’s kind of like a defrag for your database.

Schedule Cleanups

Finally, another really nice feature provided by WP-Optimize is the ability to schedule all these optimizations to happen on a regular basis. You can even set it to keep a particular time block of items that would normally be cleaned on schedule. For example, you could set WP-Optimize to remove spam and trash comments every week, but only those older than 2 weeks.

Final Thoughts

Keeping your WordPress database clean and free of junk will help keep your site running as fast as it can. While many of the tasks can be done manually, using a trustworthy plugin like WP-Optimize will make the job much more efficient and less prone to mistakes.