I had to make “Site5” happy, so recently, I’ve had to become a “master” of WordPress database cleanup & optimization. I could write a book on all the things I had to do to make them happy – but I’ll save that for another post so I can be more technical for advanced users – this post on the other hand, will identify the best WordPress plugins that I installed that actually made a huge difference to my out-of-control database, that can be used by almost anyone who has a logical mindset.
Before & After (Culling 300,000+ Tables in the database)
Just to give you an idea of the magnitude of how helpful these plugins will be to your wordpress “spring cleaning” – my database (of my largest website) started at: Size: 51.1MiB / Tables:322,533
And by the time I was finished, ended at: Size: 8.4 MiB / Tables: 32,935
Can you see the difference? Yes, you should be saying “WOW!” – I was able to cull – without any side-effects to my website whatsoever – over 40MB out of the database, and just under 300,000 tables – yes three hundred THOUSAND tables deleted that were no longer in use but were causing resources to be used on the server.
The problems & main culprits:
Here are the main problems/culprits that I’ll deal with today but there are heaps more that I’ll discuss in future (bad spiders, scrapers, spammers, hackers, people using your images, badly-written plugins, caching, and more) that can also cause high resource usage, and I had to deal with all of them for this particular site. But, to keep things simple in this post, and to make the most amount of difference on every site – the most common problems that you can start to fix in a few minutes from now, are the following:
- Many plugins do not clean up their database tables after you uninstall them. Plugin developers have the option to add it, but most of them choose not to – even the popular plugins that you feel “safe” with, rarely delete any of their left-over database tables after you have uninstalled & deleted a plugin. What you are left with is a bulked-up database full of tables that you no longer use.
- WordPress automatically saves revisions & drafts for you as you are writing new posts & pages, and keeps copies of these for you to go back to, if something happens (you have a power-outage, etc.) These “revisions” are no longer needed after you have published the “final” post, and all is well in the world. What you end up with is hundreds or thousands of extra posts/page copies that bulk-up your database.
- When you install & uninstall plugins, and even through daily use, your database needs to be Optimized on a regular basis. It helps you reclaim un-used space in the database which essentially reduces the size of your database, improves database performance, and helps your website run faster. I regularly go into my database (PhpMyAdmin) manually to repair & optimize my databases, but in this post, I’ll suggest a plugin.
If your host is threatening you because your site is using too many resources, then this post will help with the above main culprits… and at the very least, your site will definitely run faster after using these plugins:
Enjoy (and be careful) with the following plugins that have made the greatest impact for me:
(Note: I don’t use multisites, and I’m pretty sure that nothing I recommend will work with multisites)
WP Clean Up
Make sure you get the right one, there are 2 WP Cleanup’s, one that hasn’t been updated in over 2 years, and the other one, above, that is kept up-to-date with the latest versions of WordPress.
This one is fairly safe to use for beginners, it doesn’t generally pick up any real orphan options, at least – I haven’t noticed it picking up ANYTHING in the wp-options table (so it is unlikely that you will accidently delete something that will break your site). This one you would use on a fairly regular basis – i.e. everytime you do a new blog post, you can go into this and delete the revisions/drafts, etc. (that wordpress automatically creates as you are writing) as well as optimize some of the tables in the database.
This plugin hasn’t been updated since 2010, which is one of the reasons why it was one of the very last plugins I tried – but … it ended up being the absolute BEST plugin I’ve found for really culling down your database, it ROCKS my world. But – it is extremely dangerous for newbies to use – if you are not confident, you shouldn’t risk using this one – I would still be really nervous using this on client sites, but I was desperate for one of my sites to stop using so many resources so was willing to take a risk on my own site.
This plugin has the amazing ability to pickup what every other plugin seemed to miss/skip: left-over remnants of plugins in the “wp-options” table. Maybe the other plugins skip it because they don’t want to be responsible for people deleting the wrong thing and blaming them, but in any case, for me, this one was a life-saver.
When to use this:
Maybe as a once-every-3-month type of cleanup, unless you have been installing & uninstalling a lot of plugins lately. If you have been trialling different plugins lately – I would definitely be using this straight away.
It searches through your wp-options table (and the rest of your database) and finds remnants of plugins that “it thinks” they left behind.
errr… It “thinks”?
Unfortunately, it also picks-up some tables that your plugins might still use, so the caution that you have to take with this one, is to:
1.) Make sure you have a full backup of your website and database before you go crazy
2.) Look through every line and try and determine with your logic, whether you are likely to need it or not.
How? Well I look at every single line and if I see a plugin that I “know for a fact” that I have definitely deleted in the past, I can happily delete it. If I look at a line, and “know for a fact” that I still have that plugin installed, then I can make sure that I don’t delete it.
If your database is out of control, this might be the only plugin that saves the day, so even though it’s dangerous in the wrong hands – it is necessary that I include this plugin here because this plugin could be the difference between keeping your host happy on your current plan or moving to a more expensive plan/host.
This plugin alone, was responsible for most of the difference in database & tables because I was already deleting my revisions and optimizing my database on a regular basis. If you haven’t been deleting your revisions or optimizing your database, then you will see a huge improvement just by adding that to your regular things to do.