I would claim that among all the tasks involved with running WordPress websites, database maintenance is probably among the most neglected. A lot of people consider the MySQL database as just a necessary part of the WordPress install and then tend to forget about it.
However, taking care of your database is of great importance. Regular cleanups and optimization can help increase site speed, which is an important factor for SEO and bounce rate. Even a few seconds of loading time can make a big difference.
While there are lots of things you can do on the front end — compressing images, using a CDN, and installing caching plugins — it’s just as important, if not more, to take care of business on an even deeper level.
What does your WordPress database contain?
If you’re a newbie to WordPress, you might be surprised to hear that what you consider “your website” is really only the surface of it. What you see in your browser and the WordPress backend is merely a gateway, an interface for the database. It is there that the heart of your site lies.
Sure, WordPress contains all the styling, media files and everything else that makes your website look good to browsers and visitors. However, your content in its raw form — pages, posts, comments, and everything else — resides in the database. I know, mind equals blown, right?
Currently the standard WordPress database consists of 11 tables. If you have installed any plugins, your database is likely to contain more, however, a fresh install should only come with those core few.
Here are some examples of the WordPress core tables:
- wp_comments – Contains all comments on your website
- wp_options – Options set in the admin settings area are stored here
- wp_posts – The place for posts, pages, and navigation menu items
- wp_users – A list of all users registered on your WordPress site
You get the picture, right? It’s not rocket science.
Why does the database need cleaning?
If your WordPress website has been online and active for a while, your database will have accumulated a lot of information. Post revisions and drafts, and spam comments and data from uninstalled plugins increase its size quickly.
A large database can seriously affect your site loading speed because the bigger the database, the longer it takes for your server to find and retrieve information from it. No good for site performance.
In addition to that, having a large database makes backing up a long drawn-out process and migrating your website a lesson in patience. Trust me, I have been there. Plus, many hosting plans only offer limited web space so a reasonably sized database is also a matter of cost.
Consequently, if you want to shave a few precious seconds of your loading time, keep your database trim and neat and save it from ballooning out of proportion, you will not get around performing a regular cleanup.
What does a database cleanup consist of?
In short, cleaning up and optimizing your database means removing unwanted and unneeded data, information that is not necessary for running your WordPress site and is only taking up space and slowing things down.
One part of cleaning up your database, the part that is usually referred to as optimization, is deleting the so-called overhead. Overhead is just a fancy name for temporary disk space used by the database tables. If it’s not cleaned up regularly, it increases in size over time.
Reducing data bloat
Part two consists of taking care of dead information weight. In its standard configuration, WordPress accumulates a lot of data that is unneeded for day to day operations. Examples of this include:
- Post revisions
- Leftovers from plugins and themes
- Spam comments
- Deleted posts and pages
All of the above are stored in your database, taking up precious space. It’s time to shed that dead weight. In the following, I will show you how to trim the excess from your database, both via plugins and manually.
First things first: Back it up!
Before we go and tinker with the core of our website, we want to make sure that we can go back to the status quo in case something goes wrong.
Don’t skip this step! Deleting just one thing that should have remained untouched can break your entire site.
So whenever you mess with your database, the first order of the day is to perform a backup.
Do it now. Go ahead, I’ll wait.
Optimizing your WordPress database via plugin
Ok, so now that your data is safe and sound, let’s get optimizing! While it is possible to do everything manually (more on that below), most people do well opting for the plugin route. That way you don’t need to know the ins and outs of MySQL, write commands or do anything else but click buttons. Plus, there are some great alternatives to choose from.
Rating: 4.7 stars
With almost 1.5 million downloads, WP-Optimize is clearly one of the favorites of the WordPress community in its niche. And for good reason: The plugin makes it incredibly easy to keep your database in good shape.
With just one click you can both optimize your database and remove post revisions, spam comments, items in the trash, and other extraneous data. In addition to that it is possible to schedule automatic cleanups in regular intervals to avoid a bloated database in the future.
If you are looking for additional information on the state of your database, the plugin will also give you an insight into which table is taking up how much space and the amount that could be freed up by optimizing. The only thing it does not have is the ability to perform a backup so you will still have to do that part manually.
Rating: 4 stars
WP-DBManager is another community favorite and a plugin which I personally have had good experiences with. It needs some extra steps to set up but makes up for the hassle with an impressive array of features.
First and foremost is the ability to perform manual or automated backups of your database. The plugin lets you save a number of database images directly on your server or send them to an email address of your choosing. They can even be compressed to reduce files size.
Secondly, the plugin can repair parts of your database that have become corrupted. While phpMyAdmin offers the same functionality, it is nice to be able to perform this action from the convenience of the WordPress backend.
Other features include database restore, running MySQL queries, and dropping entire tables. However, because the plugin is so powerful it is imperative that you really know what you are doing there. Plus, if anyone ever manages to hack into your site, they will gain control over your entire database and can wreak some havoc.
What I am missing in WP-DBManager, however, is the ability to remove data bloat. The plugin only lets you take care of the overhead, not of other unnecessary data. That still have to be removed manually or by other means.
3. WP Clean Up Optimizer
Rating: 4.5 stars
Link: WP Clean Up Optimizer
Another plugin option for better database performance is WP Clean Up Optimizer. Besides optimizing your tables, it also deletes obsolete and orphaned data and offers database repair. A Pro version with additional features is available, which for example contains automatic scheduling.
What’s nice about this plugin is the fact that it gathers all the info about post revisions, drafts and other abandoned data in one place and lets you delete it selectively at the touch of a button. You can also optimize individual tables and WP Clean UP Optimizer will even highlight important WordPress functions so that you don’t mess up your site by accident.
Cleaning up the WordPress database manually
If you are loathe to install yet another plugin on your website (or if you just like to improve your skills as a website admin), all of the actions done by the above plugins can also be performed manually. The most common way to do so is via phpMyAdmin. Many hosting companies use this program for database administration but even if yours doesn’t, don’t despair, all of these tools are usually very similar.
Optimizing tables with phpMyAdmin
If you log into your database management tool and take a look at the tables stored there, you will see that there is a column on the right which states the size of the overhead. This will give you a clue as to which tables need to be optimized the most. Be aware that some overhead is normal and an inevitable result of an active website. The goal is not to have that number be zero all the time.
Performing a database optimization with phpMyAdmin is extremely easy. Merely tick the “check all” box on the bottom and choose “Optimize table” from the dropdown menu next to it. Clicking “Go” starts the process and you will receive a confirmation message after the action has been performed.
Note: Repairing tables works much in the same way, only that you will choose “Repair table” instead of “optimize.” Easy peasy!
Reducing website bloat
Next up is taking care of the dead weight hanging on to your your site. As with optimization, some of these are tasks that need to be performed periodically as WordPress continues to be hungry for information.
While post revisions are a nice thing to have in case you have to go back to an old post or page version, they can also significantly contribute to database size.
To take care of this problem, the first order of the day is to limit how many revisions WordPress is allowed to store. By default, the platform will keep an unlimited number of post drafts. Time to reign it in.
To do so, you need to add the following piece of code to your wp-config.php file:
define( 'WP_POST_REVISIONS', x );
X is of course the number of revisions you would like for WordPress to create. If you want to limit yourself to three revisions, it would look like this:
define( 'WP_POST_REVISIONS', 3 );
To disable post revisions completely, you have to adjust the code in this manners:
define( 'WP_POST_REVISIONS', false );
Limiting the number of new revisions that are created, however, does not affect those that are already present in your database. Fortunately, there are plugins that can take care of them. I have good experiences with the Optimize Database after Deleting Revisions plugin. It allows you to selectively delete revisions and keep a chosen number of the most recent. Another plugin of this kind is Better Delete Revisions.
Spam is the plague of the Internet. On average WordPress websites receive 24 times more spam than legit comments. And where do all of these get saved? You guessed it, inside the database.
WordPress automatically deletes comments marked as spam after 30 days. Depending on your website traffic, however, this can easily be in the thousands.
Fortunately, WordPress is making it really easy to get rid of them. Merely go to Comments > Spam and click on the “Empty Spam” button. Voilá! Do this regularly to keep your database small.
As far as I can see, there is currently no means of shortening the interval of spam deletion in WordPress. What you can do, however, is to use an anti-spam plugin such as Akismet which can not only be told to delete obvious spam right away, but also has a shorter interval before it deletes existing spam comments. Another way to schedule regular spam cleanup is the Spam Comments Cleaner plugin.
Items moved to the trash will continue to take up space in your database until they are deleted permanently. This can be done either by hand or performed automatically by WordPress. Its default setting for emptying the trash is 30 days.
If you write a lot and are in the habit of moving a lot of post and page drafts to the trash bin, it might make sense to change this number. All it needs is another piece of code in the wp-config.php file.
define( 'EMPTY_TRASH_DAYS', 3 );
The number determines after how many days trashed items will be deleted permanently, in this case after three. To disable the trash completely, you can also set it to zero. However, I wouldn’t recommend it.
Unused plugin and theme tables
As a space-conscious and expert WordPress user, you are probably already in the habit of deleting plugins you no longer have use for. However, what you might not know is that when you do so, these plugins will often leave some tables behind in your database.
If you know what you are doing, these can be removed manually. However, a plugin is surely the easier alternative. The only one that I could find with this capability is WPDBSpringClean. It will sniff out which tables in your database are no longer in use and give you the option to delete them. Although the plugin hasn’t been updated in more than a year, I recently successfully ran it on a WordPress site with version 4.0.1.
Taking care of your database is a cumbersome but necessary evil of running a WordPress website. Keeping it in good shape can reduce load time and lead to better rankings and an improved user experience. Fortunately optimizing your database is not very complicated and there are a number of powerful plugins to make the process a lot easier.
Apart from that, there are steps you can take to keep your database from ballooning up again in the future. Just don’t forget to create a backup before messing with it in any way.
How do you take care of your WordPress database? Are there additional plugins you can recommend? Any other tricks or code snippets that come to mind? Let us know in the comments.
Nick Schäferhoff is an entrepreneur and writer/blogger from Germany. He learned WordPress when he needed a website for his first business venture and instantly fell in love. He is passionate about health, productivity, and continuous learning, which he writes about on his lifestyle blog. When not building websites, he likes to travel the world, experience other cultures, and learn new languages.