WordPress have a feature which saves various copies of your posts in order to allow you to compare revisions or restore an older version of your posts. This is very useful, but it also take a lot of space in your database. Here is a very handy SQL query that will instantly delete all posts revisions as well as meta associated with it.
Just run the following query on your WordPress database, and all revisions (As well as meta associated with it) will be deleted from your database.
Of course, do not forget to make a backup of your database before running the code.
DELETE a,b,c FROM wp_posts a WHERE a.post_type = 'revision' LEFT JOIN wp_term_relationships b ON (a.ID = b.object_id) LEFT JOIN wp_postmeta c ON (a.ID = c.post_id);
If you'd like to see more SQL queries for WordPress, make sure to read this post.
Thanks to One Extra Pixel for this cool query!
8 Responses
Better still just insert the following in your WP-conf file and wp will automatically delete older revisions. Between 5 or 10 are reasonable numbers for most people.
define (‘WP_POST_REVISIONS’, 5);
Hey presto!
Thank you or the very helpful SQL query. I had many old posts on my blog which I didn´t need anymore, put now I´ve deleted them comptletly. As I´m not very used to WordPress, I´m always glad to find helpful posts like this, so I can learn to handle my blog much better.
Hi simon, i just put the code on my wp-config.php, but it nothing happens. So what should i do then?
Thank you very much for this, very helpful
Thanks for this. I need to do a little spring cleaning on a few of my blogs, so this is much appreciated.
The query above doesn’t seem to work, this one does:
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’
The query above doesn’t seem to work, this one does:
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’
Trackbacks: