WordPress automatically creates revisions of your content. Whenever you save a post or a page, the old version is retained so you can revert back at any time. Older revisions are never deleted so you always have a full history of all page changes.However, sometimes it’s necessary to do a little housekeeping. Every revision requires a separate row in WordPress’s posts table and perhaps multiple entries in the postmeta and term_relationships tables. Removing older revisions will free up disk space and ease MySQL’s processing burden.
Removing old revisions
I’m going to say this only once: BACK UP YOUR DATABASE! We’re about to run SQL statements directly on your MySQL tables and one tiny slip could trash your WordPress installation.First, you need to find your WordPress table prefix which is specified in the following line of wp-config.php:
$table_prefix = 'wp_';
wp_ is the default but it can be changed to give your installation a little additional security. We’ll assume wp_ has been specified in the following code.To delete all revisions for all pages and posts, start a MySQL administration tool such as phpMyAdmin and run the following SQL statement:
DELETE a,b,cFROM wp_posts a LEFT JOIN wp_term_relationships b ON (a.ID = b.object_id) LEFT JOIN wp_postmeta c ON (a.ID = c.post_id) WHERE a.post_type = 'revision';
(Remember to change all table references from “wp_” to your table prefix if necessary.)
tip: SQL Shenanigans
Many thanks to Michael Ambrosio for highlighting a subtle problem with this SQL statement which can cause WordPress links to be deleted. View his article for more information…
If that’s a little too severe, you could remove all revisions prior to a specific date, e.g. the following statement will remove all revisions except for those made after January 1, 2010:
DELETE a,b,cFROM wp_posts a LEFT JOIN wp_term_relationships b ON (a.ID = b.object_id) LEFT JOIN wp_postmeta c ON (a.ID = c.post_id) WHERE a.post_type = 'revision'AND a.post_date < '2010-01-01';
(Note that MySQL dates are specified using YYYY-MM-DD notation.)
Disabling or limiting revisions
Add the following statement to your WordPress wp-config.php file to permanently switch off post revisions:
The value can be set to ‘true’ to re-enable revisions.Alternatively, you can use a positive integer to limit the number of permitted revisions, e.g.
This will create a maximum of 5 revisions per post, plus one for auto-saving purposes. Older revisions will be automatically deleted.
If this all sounds a little too scary, you’ll be pleased to know that there are a number of WordPress plugins offering revision control.Do you have any further tips for controlling WordPress revisions?