How to: Batch deleting post revisions

Post revisions, a WordPress 2.6 new feature can be very usefull, but it also increase the size of your MySQL database. Sure, you can manually delete posts revisions, but that’s a very long and boring work. Another solution is what I’m going to teach you: Batch deleting post revisions.

First of it all, login to your phpmyadmin and select your WordPress database. Once done, click on the sql button to open the sql command window.

Paste the following sql command in the sql window:

DELETE FROM wp_posts WHERE post_type = "revision";

Work's done. You saved a precious space on your database by deleting unnecessary post revisions.

37 Responses

Oct 29 2008 11:23

I don’t suppose there is a way to stop WP creating these revisions in the first place?

thanks

wilco

Oct 29 2008 13:11

Do these post revisions take up a lot of space on a database?

Oct 29 2008 16:41

is there any way to automate it? Or any hack to stop wp creating the revision?

Oct 29 2008 16:56

@fromtheold: It depends of how many posts you have in your WP database, but if you have a lot of posts, revisions can eat up a lot of database space.

@all: Yes there is a hack to disallow WP to create theses revisions. Recipe to come very soon ;)

Oct 30 2008 21:56

Hi Jbj,
this is an excellent recipe :
my database has been reduced by two !
Thanks a lot !

Oct 30 2008 22:00

@Rainbow: Glad to see this recipe helped you =)

Nov 02 2008 01:27

Thanks for the “trick”; now i understand why my db is so fat :D
Sorry for my bad english.
thanks :)

Nov 03 2008 23:33

Awesome! I halved my DB and then used the same concept to clean out my mediawiki one. BTW, combine that maneuver with this plugin and you’ll be able to control your revisions: http://wordpress.org/extend/plugins/revision-control/

Nov 04 2008 00:24

@Ipstenu: Glad it helped! To control your post revisions, you can also use this recipe: http://www.wprecipes.com/how-to-control-how-much-post-revisions-are-saved

Nov 06 2008 09:33

The revisions in 2.7 also interfered with plugins like more fields (to add custom fields in your “post edit page”) and I think in some SEO packages.

In most cases this resulted in double custom fields. Every time I saved a post an extra custom field was added. If I deleted a field manually nothing happened. I now have many posts with multiple double custom fields.

By disabling and deleting the revisions as described above my problem is solved since no custom fields are ddoubled anymore.

However, I still have all the double custsom fields in my database. how could i solve this withou destroying my database

Nov 06 2008 09:45

@Erik: I’ll look on my DB and see if I can do something :)

Nov 06 2008 09:57

Would be great. When searching the internet for this problem I get the feeling that lots of people were having the double custom field problem.

In this respect I think that the post revisions were not a big success. Although they help multi author blogs a lot of unwanted side-effects happened.

Nov 06 2008 10:14

@Erik: I think post revisions are useful, but the WP team should have provided a tool to batch delete it. As the code above shows, it isn’t really hard to code :)

Nov 09 2008 22:43

lol i didn’t actually think you can delete that… time to save some MB :)

Nov 28 2008 23:23

@Erik:

I have the same issue. When I look at my wp_postmeta table in phpmyadmin, I saw that many of the entries (meta_id’s) were associated with “post_id=0″. In order to fix this, I deleted all of the rows with a post id of zero by running this SQL query: DELETE FROM wp_postmeta WHERE post_id = 0

I think this occurred when I imported a large number of pages…

Also, this discussion may be helpful: http://wordpress.org/support/topic/168011?replies=20

Good luck!

Apr 15 2009 23:06

That helps a lot when importing and exporting DBs. Thanks!

Trackbacks:

Leave a Comment

* Name, Email, Comment are Required