This tutorial is currently being redacted. Database Cleaner is a very new plugin, and I am pushing changes (related to features and UI) every few days. I am trying to keep this tutorial up to date as much as I can. Don’t hesitate to come back to me with some feedback as well 🙂
If you are here, is that you might feel your WordPress database is running slow, and needs to be cleaned and optimized. Let’s get started 🙂
The User Interface
I think it’s important to start by going through a few concepts and features of the UI. It will make everything easier for you.
You can quickly check the database size, compared with the previous recorded size. It looks like this:
Info, Mode, Actions,
The count is the number of entries for that item. You can refresh that result, and take a quick look into the related data by using the magnifying glass. The Mode is very important: it can be Auto, Manual, or Never. When set on Auto, the Count will be automatically retrieved, and it will be also part of the Auto Clean. When set on Manual, you will need to manually click on Refresh and Clean.
When set on Never, that item will be basically disabled and ignored.
They are at the top right of each section, and they don’t always work the same way depending on the tab. The Refresh is always available, and will calculate the count for each item in that section. The Trash and others action buttons will delete all the selected items (if selecting is possible in that section) or will delete all the items set on Auto.
This rocket button is for optimizing tables 🙂
There aren’t so many options yet (and I hope it will not grow too much), but those two are relatively important. Age Threshold will delete only the entries older than a specific number of days (of course, that option doesn’t matter when you delete tables and options).
Batch Size is an unique feature of Database Cleaner that allows huge database to be also cleaned without facing timeouts or technical issues. It will basically perform deletions little by little.
Each tab of Database Cleaner represents a different performance bottleneck, and proposes different ways to clean your database.
I will use the term item to design a very specific type of data to be cleaned. The items are basically presented as rows, and here are a few: Auto Drafts, Orphaned Term Relationship, Transient Options.
You might have seen those items in other cleaners and mentioned in articles about optimizing WordPress. There are common ways of cleaning WordPress. I wouldn’t say they are the most important ones, but they will help you keeping everything tidy while being relatively safe to run.
This tab might be extremely helpful for some, while not at all for others. In the WordPress database, one of the most heavily and intensily used table is the posts table. There is basically all your content in it, and WordPress needs to constantly queries for your posts, pages, products and others. Each type of content is represented by a post type (actually post, page, and product, for example). Having a lot of data here is the main bottleneck for many WordPress sites, so you absolutely need to define is some of your post types became useless (used by a plugin you uninstalled) or, at least, can be emptied from time to time.
In my case, I am using EDD (Easy Digital Downloads), which allows me to sell my products online. This plugin keeps a lot of logs under those post types: edd_log, edd_license_log and edd_subscription_log. At some point, my database even started to time out! Since I also have those logs in Paypal and Stripe, I don’t really need those data. I use the Clean button from time to time, but I could also set it to Auto to have it cleaned automatically.
Let’s be honest here: this section is more about reducing the size of your database than actually enhancing it’s performance (even though it is correlated, a little bit).
Database Cleaner will try to tell you by which plugin each table is used. Click on the Inactive section, and delete the tables which you think are useless.
Optimize is also possible; while this MySQL feature doesn’t really hurt, it doesn’t necessarily improve anything. You might expect that your database size decreases, but it doesn’t always, and might even increase.
You will need some patience to tackle this section, but it worth it! Most plugins create options, which are full of settings and various kind of data. Your WordPress is probably full of useless registered options, and that’s unfortunate but expected.
One kind of option is worse than the others: the autoloaded ones. At every request to your WordPress all those options are retrieved from your database, then stored in memory for the time of that request. Needless to say, the impact of useless autoloaded option is major. Review each autoloaded option, delete it if it’s useless, or set the autoload to off if you think this option is not used for all requests to your WordPress.
The Pro Version of Database Cleaner recognizes the options of 5,000 different plugins. Try the Pro Version if you can, that will likely help a lot.
The Auto Clean Button
By default, you should be all good! But you might have changed the Mode for a few items in the tabs. In my case, I could set a specific post type on Auto, in order to have it emptied automatically. Then, just click on the button Auto Clean, and it will go through everything that is set to Auto.
This feature is being build, and not available yet. Basically, it will constantly keep your database clean based on what was set to Auto. The SQL requests will be very small, so that there will be no impact on the performance of your WordPress.