10

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?

6 Answers 6

11

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);
2
  • You're right, this is much efficient too... posted my answer in hurry and didn't notice the WP MU part too. +1.
    – deathlock
    Oct 16, 2012 at 5:39
  • and how to remove revisions except let's say the last 3?
    – Matoeil
    Sep 10, 2013 at 12:41
2

There is also a plugin, WP Optimize that can help you do this

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.

4
  • Please describe what the plugin does and how it solves the problem.
    – fuxia
    Oct 16, 2012 at 7:02
  • 1
    There are many plugins that clean revisions and optimize without phpmyadmin; none of them are clearly stated to work with WPMU or multisite. Oct 16, 2012 at 13:05
  • will this plugin do harm to my database?
    – hugemeow
    Oct 16, 2012 at 13:51
  • I've used the plugin on a couple of sites I run with no issues. But it is always a good idea to backup your db first (but that is just best practise).
    – darronz
    Oct 17, 2012 at 7:42
0

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

DELETE FROM wp_posts WHERE post_type = "revision";

1
  • i have more than 100 blogs on wpmu, so i should run DELETE FROM wp_n_posts WHERE post_type = "revision"; for n times ,right? but this is quite boring:(
    – hugemeow
    Oct 16, 2012 at 13:36
0

You could also add this code to your theme's functions.php file:

if (!defined('WP_POST_REVISIONS')) define('WP_POST_REVISIONS', 3);
if (!defined('WP_POST_REVISIONS')) define('WP_POST_REVISIONS', false);

This code checks if WP_POST_REVISIONS limit has been set in wp-config.php, If it hasn’t then it passes a parameter to the function that limits post revisions. In the example above, posts are limited to 3 revisions.

This is a good solution when building themes for people who dont know how to (or dont want to) add code.

Taken from wp-functions.com

1
  • 2
    This answer does not talk about deleting revisions. But its good advice on how to disable revisions for future content. Mar 23, 2015 at 4:44
0

You can use the WP Sweep plugin to clean post revisions. After activating the plugin, go to Tools » Sweep to clean up your WordPress database.

-1

Thanks for the answer, markratledge. Something in the syntax regarding the {id} did not work for me. I changed {id} to 4009, one of my post ids, but no success. I found a solution on https://dev-notes.eu/2017/11/manage-and-safely-delete-revisions-in-wordpress/

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';

That worked fine.

1
  • The {$id} is a WP table prefix - possibly not the best name for that :)
    – Picard
    Aug 19, 2022 at 9:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.