How to delete post revisions?

Question

I haven't set a limit to the number of revisions, which makes some of my post have more than 20 revisions, so how to delete these revisions?

By the way, I am using WPMU and have many blogs, so how to delete WordPress revisions for all my blogs?

Answer 1

This is a much safer query to use and will remove related entries from the postmeta and term_relationship, unlike deathlocks query in his answer.

Change the {id} to the id of each blog posts table. You can combine this query to run all the post tables at once, but try this on one table first. I've used it many times on single WP installs.

DELETE a,b,c
FROM wp_{id}_posts a
LEFT JOIN wp_{id}_term_relationships b ON (a.ID = b.object_id)
LEFT JOIN wp_{id}_postmeta c ON (a.ID = c.post_id)
WHERE a.post_type = 'revision'

After you run it, optimize the database in phpmyadmin.

And add this line near the top of wp-config.php to prevent future revisions:

define('WP_POST_REVISIONS', 0);

or save one revision:

define('WP_POST_REVISIONS', 1);

Answer 2

There is also a plugin that can help you do this http://wordpress.org/extend/plugins/wp-optimize/

From the website:

WP-Optimize is a WordPress 2.9++ database cleanup and optimization tool. It doesn't require PhpMyAdmin to optimize your database tables.

It allows you to remove post revisions, comments in the spam queue, un-approved comments within few clicks.

Answer 3

To delete all of your Wordpress revisions, you could use this query:

DELETE FROM wp_posts WHERE post_type = "revision";