WordPress Post Revisions – Delete and Limit

re: WordPress 3.2.1, phpMyAdmin 3.3.10, MySQL 5.1.54

WordPress by default will store an unlimited number of revisions made to a post and allow you to restore any of these versions. The revision history and the ability to restore these is a great feature of WordPress – but – it can create database-bloat. The extra space taken up by a lot of revisions may be a concern to some as well as there may be minor performance improvements when reducing the size of your WordPress database.  I’ll follow with some steps to do some maintenance on your WP database but first there is a nice WP constant you can define to control revisions.

I’ll not suggest turning off revisions but you can limit the number of revisions created where older ones will automatically be deleted when you reach the limit.  To limit revisions to 2 edit your wp-config.php file and add:

define(‘WP_POST_REVISIONS’,2);

Turn off revisions by setting the constant to false or 0 (not recommended). 

Just in case you aren’t 100% familiar with WP’s revisions you can see these when editing a post in admin, scroll down to the bottom, and you’ll see a “Revisions” section. Clicking on the link provided will allow you to view and restore that revision. Note that revisions are not created from WP’s auto-save feature.

wp_revisions_screen_shot02

If you aren’t seeing this then the post doesn’t have a revision or this Revisions section isn’t visible – to turn on scroll up to the top and over on the right you’ll see Screen Options which you can drop-down and make sure Revisions is checked.

wp_revisions_screen_shot01

Now be warned to go much further you’ll need some experience with phpMyAdmin and/or the shell prompt. And I’m a bit leery of the WP plugins that state to take care of this level of database maintenance but maybe there are some good ones out there. Oh and I’d always suggest backing up your database before doing this level of maintenance.

The example WP site I’m using has about 12,900 posts and from phpMyAdmin select the  WP database and before removing the revisions and optimizing we see the size: 77.3MB,

wp_revisions_screen_shot03a

Now lets get a count of the total number of revisions – run this SQL query,

SELECT COUNT( DISTINCT a.ID )
FROM wp_posts a
LEFT JOIN wp_postmeta b ON ( a.ID = b.post_id )
LEFT JOIN wp_term_relationships c ON ( a.ID = c.object_id )
WHERE a.post_type = ‘revision’

wp_revisions_screen_shot03b

Not a small number of revisions and since we’ve decided we no longer need any of the revisions we’ll delete them all.  Note that while you can just query wp_posts for your total revisions, we’re joining on the meta data also since when we delete the revisions this is the query we needed. Otherwise you’ll end up with orphaned records in wp_postmeta & wp_term_relationships,

DELETE a,b,c
FROM wp_posts a
LEFT JOIN wp_postmeta b ON ( a.ID = b.post_id )
LEFT JOIN wp_term_relationships c ON ( a.ID = c.object_id )
WHERE a.post_type = ‘revision’

wp_revisions_screen_shot03c

Since we’re removing the needed rows from all three tables the number deleted is higher than our post revision count.  Now after doing this the database size won’t change so we need to optimize it,

To optimize your database you can do this from a command/shell prompt using the myslqcheck command like:

mysqlcheck -o –u user_name –u database_name

Replace user_name and database_name with your values.

You can also do this from within phpMyAdmin – select your WP database, click the Structure tab and click Check All to check all the tables, then from the With selected: dropdown select Optimize table,

wp_revisions_screen_shot03

The screen shot is post optimize where we’re down to 50.9MB – and nice 1/3 smaller. Of course we’ve lost all revision history in the process.

One thought on “WordPress Post Revisions – Delete and Limit”

Comments are closed.