WordPress 2.6 introduced a post revisions feature, which automatically saves a draft as you begin to write a post or page and saves a revision for each change made afterwards. That’s right, every time you make even the slightest alteration to a post or page, a new revision is saved. Now don’t get me wrong, this is an incredibly useful feature, especially if you accidentally cut half of your post during a late night edit or lose power while composing your latest masterpiece, but these revisions can slowly build up in your database.
If it’s been a few days since you’ve last edited your blog, chances are that you don’t need to keep any revisions around, and they’re probably just siting in your database taking up space and growing cobwebs. To remove all of your revisions safely without harming your published posts and pages, backup your database, then use either Delete-Revision or Better Delete Revision. If you’re handy with SQL queries, use either phpMyAdmin or the MySQL command line to run the following query (change the table prefix as necessary):
DELETE FROM wp_posts WHERE post_type = "revision"
Update: Thanks to Ozh for pointing out that the above query “just deletes post marked as revisions. If for some reason you associated a revision with a tag or a category that was then removed when the final post was published, you will have extra entries in other tables such as terms.” The proper query to safely remove all of your revisions is as follows (change the table prefix as necessary):
DELETE a,b,c
FROM 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'
If you want to disable the revision system, add the following line to your wp-config.php file:
define('WP_POST_REVISIONS', false );
If you want to specify the number of revisions that WordPress can save, add the following line to your wp-config.php file (change the number to your desired maximum revision count):
define('WP_POST_REVISIONS', 3);
As the revision system is a form of backup, I recommend periodically deleting revisions as opposed to disabling or limiting it.
I ran the above query on my blog for the first time last night and reduced the database size by almost half! How much did you save?
I use WP CleanFix; it has lots of good features: http://wordpress.org/extend/plugins/wp-cleanfix/
I saved more than 55% of my DB when I deleted revisions a couple of weeks ago
Note that the above SQL query just deletes post marked as revisions. If for some reason you associated a revision with a tag or a category that was then removed when the final post was published, you will have extra entries in other tables such as terms.
The full query to properly clean your DB from revisions is something like this:
DELETE a,b,c
FROM 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’
Thanks, Ozh! I’ve updated the post with your explanation and query.
Does limiting the number of revisions using
define(‘WP_POST_REVISIONS’, 3);
also limit the revisions on pages? That is where they seem to build up for me.
Yes, that setting limits all revisions, including posts and pages.
This is a rather timely post. I was just looking for a good plugin to do the dirty work for me of removing all the zillions of post revisions cluttering up my db and along comes your suggestions.
Nearly 4000 revisions can really add up to be some severe DB clutter.
Thanks!
I’m using a post revision plugin and setting the number of revisions to 3. Every month or so, I use WP-Optimize and delete all the revisions (after saving a db backup).
I’d love to have “real” revision history, implemented by storing diffs instead of whole posts… not gonna hold my breath. =)
I just use the Revision Control plugin to get fine grained control over revisions. Adjust the number of revisions you want on a per-post or per page basis, with default settings. Much simpler.
Didn’t count the size of db before, but it cleared out 4244 rows
I think this should be an option in the wp-admin area, to either delete and or disable the revisioning system. I’m all for backuping, but thats being too paranoid about it. Anyone can run a simple SQL dump in phpmyadmin to backup their entire database, there is no reason that I can think of that a person could ever need the revisioning system, but if they do for some reason, there should be an option to disable it in wp-admin, not just the wp-config.php file
“Anyone can run a simple SQL dump in phpmyadmin to backup their entire database” — I hate to break it to you, but not everyone who wants to write text on the internet can (or wants to) write SQL code…or wade through an entire SQL database dump to find the one paragraph or link they wanted to restore from an old draft of one post.
Tom Coburn wrote:
“there is no reason that I can think of that a person could ever need the revisioning system, but if they do for some reason, there should be an option to disable it in wp-admin, not just the wp-config.php file”
Weird..I can’t imagine anyone *not* wanting the revisioning system. See http://www.movingtofreedom.org.....ff-viewer/
Great tip!! Thanks!! Database download size went from 21.4mb to 10.7mb. Amazing… I do typically make a ton of revisions when I post.
Should the 3 in define( ‘WP_POST_REVISIONS’, 3 ); be enclosed in single quotes or it is not necessary?
It isn’t necessary in this case.
Really, Brian?
Weird..I can’t imagine anyone *not* wanting the revisioning system.
I can understand why some people would like it and find it useful, but I personally detest the revision system and remove it from every WP installation I use.
Unfortunately, I’ve found that just adding “define(‘WP_POST_REVISIONS’, false );” to my wp-config.php does not do the trick, although it should. I’ve actually had to modify the wp-settings.php file and change the POST_REVISIONS from true to false to make it STOP creating revisions.
I’m fully in favor of making this an admin option – even if it’s set to “On” as a default – those who like it can keep it, and those of us who don’t get to turn it off without having to monkey around in the core code and repeat our mods every time we upgrade.
i used better-delete-revision and had 8383 entries removed. 🙂
database size went from 130mb to 100mb.
i currently have 983 posts.
thank you for the tip.
14796 row(s) deleted. ( Query took 318.4725 sec )
🙂
Better Delete Revision uses the sql query suggested by Ozh, so if you are not familiar with sql, just use that plugin
As wordpress is used by so many people, its flexibility and functionality are its strengths, thus its good to have this feature.
As Tom says, not everyone wants or uses this feature, so it would be nice to have this as an option in the admin settings section. As we have seen from the comments, this feature is adding substantially to the database size, especially for heavily modified sites. This can impact on performance.
Part of the problem is people are not even aware of the impact thus having an option in the admin section will at least make people aware and look into whether they need it!
Thanks for sharing this dude. Just removed 878 revisions from my DB. There is also this plugin called revision control, but then I dont use many plugins as it brings down the speed of wordpress