WordPress Cleanup: Database Optimisation

So the database is the most dangerous place non-experienced/non-technical users can mess around in. And I’m certainly not comfortable rolling around in there. So you have to be cautious and if you’re going to hand this responsibility over to someone else then you need to be sure they can be relied upon. So I proceed with caution, as should you.

I spent a lot of time googling, reading guides on database optimisation, etc and I finally I came across the oddly named Optimise Database after Deleting Revisions plugin now this only has 60,000+ installs acording to wordpress, whereas the WP-Optimise plugin had 400,000+ installs, however it was updated less than a month ago as opposed to over 7 months ago for the more popular plugin and that worries me.

I generally won’t install a plugin without an official release via wordpress but I also like to have a read of the developers page and the more detailed that is, the more comfortable I feel. Finding video reviews by other bloggers, such as below, is also reassuring:

So suitably reassured I went ahead and installed the plugin.

Selecting Settings

The plugin is reassuringly neat and well behaved installing two menu options:

• Plugin settings in: Settings > Optimise Database
• and Tools (run the plugin) in: Tools > Optimise Database

This is even more re-assuring. I really can’t stand wordpress plugins that clutter up your dashboard menu, after all they’re mostly plugins rather than entire feature sets.

I opted for a relatively simple setup:

• Maximum number of – most recent – revisions to keep per post / page: 3
• Delete trashed items: YES
• Delete spammed items: YES
• Delete unused tags: YES
• Delete expired transients: YES
• Delete pingbacks and trackbacks: NO
• Keep a log: YES
• Number of excluded tables: 0

I decided not to schedule optimisation. With something that can screw your entire installation I think you should opt for manual control, plus it means you can always – and you always should – backup your solution before you run any optimisation (in case you need to restore it). I also wouldn’t run it just after I’ve updated wordpress, particularly a full digit update, without checking for an update to the plugin.

So here are my Results

• 587 post revisions from 75 posts; I selected to keep a minimum of 3 revisions per post.
• No TRASHED ITEMS found to delete.
• No SPAMMED ITEMS found to delete
• No UNUSED TAGS found to delete
• 7 expired transients deleted
• 1 Postmeta Orphans deleted

Size of the Database

• 7.229 MB BEFORE optimization
• 5.372 MB AFTER optimization

So I saved 1.857 MB or 25 percent of the size of my database for a process that took 1.4988 seconds. And this was just on my site, on my work site I saved 7.007 MB more than 50 percent of the size of that database!

Thanks

Thanks of course to Rolf van Gelder of CAGE Web Design for writing the plugin in the 1st place. Without people like this the wordpress community wouldn’t thrive.