In a previous post on optimizing WordPress, I suggested configuring WordPress so that revisions are not saved, and to delete old revisions to make the wp_posts table smaller and faster to query, but I didn’t mention how to do it. That’s because there is a little more to deleting old revisions than many posts on the web, and some plugins designed to delete revisions, would have you believe.
Several months ago, in his WordPress.org post “Deleting Post Revisions: do NOT use the a,b,c JOIN code you see everywhere,” kitchin, and the discussion that follows, provides an excellent description of the problems one may encounter when attempting to clean out old post revisions. As you will find if you read that post, there is an easy way to safely remove old post revisions. You can even optimize your database tables while you’re at it.
To avoid the pitfalls, and the hassle, involved in executing numerous MySQL queries in phpMyAdmin or a similar GUI to remove old post revisions, I recommend giving Rolf van Gelder’s Optimize Database after Deleting Revisions plugin a try. Rolf has done all the hard work involved in writing the database queries for deleting post revisions and their associated data. His plugin even adds the ability to empty your post Trash and Spam folders and optimize your data tables.
As of the date of this posting, the Optimize Database after Deleting Revisions plugin is also actively maintained and compatible with the current version of WordPress.
Don’t let the long list of features for the plugin fool you; after installing and activating the plugin, it’s very easy to configure and use:
- As with any operation that works with your data, you should back up your database before proceeding. (I admit I have used the Optimize Database after Deleting Revisions plugin often enough, and never had a hiccup, that I often skip this step, but I do have nightly backups of my databases.)
- Go Settings -> Optimize DB Options.
- Select the number of revisions–if any–that you want to save, then check the boxes for “Delete all trashed items,” “Delete all spammed items,” and “Delete expired transients.”
- Click the “Save Options” button, then either use the link at the top of the screen to run optimization, or go Tools -> Optimize Database and click the “Start Optimization” button.
You’ll be presented with a nice summary of the operations you selected and their results, including the before-and-after sizes of your data tables.