How to clean your WordPress database
Photo by Roi Dimor on Unsplash
Do you know how to clean your WordPress database? Did you know that you needed to clean it up at all? Well, here’s all you need to get started.
I once drove around Australia in a 20 year old VW Combi named ALA with my brother.
We were very smart before setting off from Melbourne; we cleaned her out and fixed whatever needed fixing.
We had the upholstery repaired and the seat belts checked.
She got new tyres and working lights. We fixed the bed, the fridge and the roof popup.
We even gave her a nice new coat of cyan before we got underway.
All cleaned up and ready to go. Or is she … ?
There was just one thing we didn’t do. We didn’t get anyone to check under the hood. We’d no idea about the state of the engine and I think it didn’t even cross our minds.
Being superficial just isn’t good enough
She lasted us well, getting us (and the frequent taggers-along) from Melbourne all the way up the East coast, across the Queensland outback and into the middle of Australia. We almost made it up to Katherine in the Northern Territory – about 8,000 km!
But then she started leaking oil. In the middle of the outback, we’d no idea what to do. We bought used oil and kept her filled up but it wasn’t enough. She leaked faster and faster until we couldn’t keep her topped up fast enough. With just a few days to go to Darwin she decided enough was enough and suddenly froze. The engine seized.
We were left sitting on the side of the highway. In the blazing sun. In the middle of nowhere.
So we brewed a cup of tea and put our feet up. That was life back then; not too many cares.
Of course, we should have looked a bit deeper under the surface when we were polishing ALA before our trip! I’ll fill you in on what happened with ALA at the end of the post …
A lot of people make the same mistake with their WordPress website’s database. In fact, I’m betting you don’t know the size or the state of your database right now.
This post will show you the basics of how to clean your WordPress database; that all-important engine of your WordPress website.
Why you need to clean your WordPress database
Your WordPress website has two main software components: the files and the database.
The files are the PHP, HTML, CSS and JavaScript files that live on your web server.
The database is usually MySQL, an open-source Relational DataBase Management System. WordPress, themes and plugins all use this database to store your posts, pages, comments and all sorts of settings for your site.
As you can imagine, this database gets used quite heavily.
WordPress uses it any time you change a page, a post, a comment or a setting anywhere on your site.
In fact, WordPress uses your MySQL database every time it loads a page on your website.
If HTML is the chassis, CSS is the fairing and PHP is the engine, then you can look on the database as the fuel of your WordPress website.
Database problems from Plugins
When you deactivate a plugin, it will leave its settings intact in your database. This is so that you don’t need to go through all the settings when you re-activate that plugin. If you want these settings removed, you will need to delete the plugin; that’s the difference between deactivating and deleting a plugin. Deleting will remove the plugin files and save you space that way. But will deleting a plugin also clean your WordPress database?
Unfortunately, the answer is no. Some plugin authors have decided to leave their plugin settings intact in the database, even after you delete the plugin. This is in case you ever reinstall the plugin. These authors think they are helping you by making this decision for you but, in fact, it’s poor programming and poor cleaning up.
As a result of this enthusiasm of theirs, you can get lots of “orphaned” results left lying around various places in your database. The options table in your database is one such place. Naturally your database will grow in size if these orphaned bits of data are never cleaned up.
Database problems from Transients
In WordPress terms, transients are just like options expect they have an expiry date. Transients are a form of caching that takes place on the server side. Because of this, they are a good choice for storing the results of remote calls. e.g. to a third party site.
Transients aren’t all problems. They might still exist in the database because they haven’t yet reached their expiry date. Lots of them, though, can be left lying around longer than needed. Another cause of your database size increasing and another reason to clean your WordPress database.
Does database size matter?
This is another of those “it depends!” situations.
It depends on what your web page needs to look up each time it’s loaded. Here’s a simple way of looking at it:
If your site uses a lot of plugins that do a lot of things in your WordPress database, then yes, your database size will matter.
For example, the WordPress options table gets loaded on every page. So if this table is big, then of course it will influence the loading time of every page.
On the other hand, your web pages might be simple and might not use many database look-ups. In this case the size probably doesn’t matter.
Most people will never know nor care how their database is being used and that’s fine. In general, though, it does no harm to keep your database clean, shiny and fast. Luckily there are easy ways to do that.
How to clean your WordPress database
You have a couple of options to clean your WordPress database: manually or automatically.
Clean your database by hand
Thinking of cleaning up your WordPress database by hand?
First, of course, TAKE THAT BACKUP!
Second, forget about cleaning up your database by hand.
Seriously.
It takes time. It’s highly prone to error. Any mistake in here has the potential to destroy your site.
So, my advice is … “don’t bother.”
Unless you LOVE tinkering with the innermost workings of your website. And of course it’s also the only way to really make sure you are deleting exactly what you want.
But even I do this on very rare occasions and for very specific problems.
Most times I just use a plugin.
Clean your database with a plugin
None of these plugins will do a perfect job i.e. deleting everything it could delete and leaving everything it should not.
But they will do a good job.
As with ANY thing to do with the database – do this with care. Don’t blame the plugin if it does something you don’t understand. (Remember … take that backup!)
WP-Rocket
This is our caching plugin of choice at WPStrands for a host of reasons. And as a bonus it can also clean your WordPress database! The options for database cleanup aren’t very advanced and include the most common areas of database bloat so it’s relatively safe for beginners. (But always, always take that backup.)
Did you know that you get the premium version of WP-Rocket free with our Entrepreneur and Business plans?
WP-Optimize
It’s hugely popular, it’s well maintained and does a pretty good job. Some people critiscise it since it doesn’t follow the WordPress way of deleting things. It uses direct database queries to remove stuff, instead of WordPress functions. Because of this it can and does leave some things behind that it shouldn’t.
WP-Sweep
Not as popular as WP-Optimize, but it has it’s fans because it uses WordPress delete functions and not direct MySQL queries. In that sense it is a better coded plugin in WordPress terms. So it has a better chance of ensuring WordPress stuff is properly cleaned up. Note the author says it is not compatible with Meta Slider
Optimize Database after Deleting Revisions
Another popular one. This one has the added bonus of being able to keep revisions for specific posts/pages.
WP Clean Up Optimizer: Optimize Database & WordPress
Also pretty popular, has good ratings and appears to work well.
Advanced Database Cleaner
Another one that seems to do exactly what it says on the tin.
Prevention is better than cure
I favour the “stitch in time saves nine” approach.
Having to clean your WordPress database shouldn’t be a massive job every time you get around to it. Here are some easy things anyone can do to minimise database bloat.
How to manage your WordPress database size
Delete unused plugins
When you’re trying out a few different plugins to find one you like, it’s tempting to install half a dozen plugins, try them out then uninstall them again.
This is too easy to do. Plugin data and settings are stored in the database. I also mentioned earlier that some plugins can leave orphaned data behind. This is more common than you think. If you aren’t choosy about the plugins you pick at this stage it’s a certainty that you’ll have litter left in your database.
Moderate comments & Delete Spam regularly
These are also stored in the database. Make sure you go through your comments regularly.
Post revisions
This is a huge one! A lot of people aren’t aware that every time they edit a post or page, WordPress saves a copy.
This means you always have every version of your posts. It also means this is a major cause of database bloat! I regularly see hundreds of post revisions in the websites I clean. Most of the plugins mentioned above will delete all those unwanted revisions.
Fortunately, you can set the number of revisions you want to keep stored in your database. Do this through your site’s wpconfig.php file. FTP to your site as per these instructions and edit the file.
Insert this code above the line /* That’s all, stop editing! Happy blogging. */
define(‘WP_POST_REVISIONS’, 4); /* save up to 4 post revisions */
Of course, there’s also a plugin for that: https://wordpress.org/plugins/revision-control/ though I’ve never used it.
We do all of these tasks regularly as part of our regular maintenance plans at WPStrands. Here’s some more advanced stuff that we also do:
- Optimize your database
- Change your database prefix for advanced security
Conclusion
For such a heavily used part of your website, the WordPress database is often overlooked by site owners. That’s understandable since it’s highly technical and to be honest, scary to touch.
But, it’s essential nonetheless- don’t ignore the fuel source of your WordPress website! If you do, you’ll be stumped by the strange happenings when it gets big and bloated.
And, in case you were wondering about ALA? Within thirty minutes at the side of the road a guy stopped. He and his girlfriend towed us for two days to Darwin! We sold ALA for a few hundred dollars to a guy in a campsite. He probably knew exactly how to free up that engine and get her running again.
Us? We bussed it all the way back to Melbourne… it was nice to put our feet up and be driven for a change after all that driving!
Sources
https://www.pixemweb.com/blog/wp-optimize-vs-wp-sweep-vs-optimize-database-vs-advanced-database-cleaner/https://wptavern.com/orphaned-tables-created-by-wordpress-plugins-are-not-necessarily-a-bad-thinghttps://stackoverflow.com/questions/1276/how-big-can-a-mysql-database-get-before-performance-starts-to-degradehttps://wordpress.stackexchange.com/questions/81912/relationship-between-performance-and-database-sizehttps://scotch.io/tutorials/a-guide-to-transients-in-wordpresshttps://css-tricks.com/the-deal-with-wordpress-transients/https://pressjitsu.com/blog/transient-cache-alternatives/
https://codex.wordpress.org/Options_APIhttps://codex.wordpress.org/Transients_APIhttps://www.wpbeginner.com/plugins/how-to-clean-up-your-wordpress-database-for-improved-performance/