One of the irritating feature for me in WordPress 2.6 is the post revision. I am the only author of my blog and hence this feature is useless to me.
Just in case you are wondering how post revision works, whenever a post is edited, a new row will be created in wp_posts table. Hence if your posts or pages got edited 10 times, you will have 10 new rows in wp_posts table.
In no time your wp_posts table will be filled up and the post ID will be huge.
To turn off this feature, add this following code to wp-config.php:
define('WP_POST_REVISIONS', false);
You can also delete all post revisions by running this query in phpMyAdmin:
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'
Be sure to backup your database first before performing any queries in phpMyAdmin.
*UPDATE* Auto Saves does not create a revision of the post.
*UPDATE 2* Updated SQL query from Andrei Neculau as the previous query does not delete from wp_postmeta and wp_term_relationships tables.
*UPDATE 3* There is a proper way of cleaning up Post Revisions as mentioned by kitchin in Deleting Post Revisions: do NOT use the a,b,c JOIN code you see everywhere. I like his method more than the SQL query above.
(178 votes, average: 4.18 out of 5)
@Ryan Boren, @noname : Yea I confirm that auto save does not create a revision. My bad.
Just a heads up: I wrote a plugin which allows some control over the post revisions (Disabling in admin, Enabling on per post basis, limiting number of revisions, etc): http://dd32.id.au/wordpress-plugins/revision-control/
PS. @validation function: ‘ ‘ is a valid character in an email address
Oh.. It doesnt like the plus symbol at alll.. Lovely 🙂
@DD32: Nice! WordPress should have that in the core.
This ‘feature’ has been a terrible nuisance to me as well. Thank you very, very much for this helpful post. It’s brought a much needed smile to my hour of trying to figure out what’s going on.
Now on to clean out database tables. I’m a bit, actually quite, disappointed with the devs on this one and definitely agree such a feature should be accessible under Settings in admin.
After disabling Post Revisions, I did manage to get one post ID ‘skipped’ so far after about five test posts. Hopefully it’s not autosave causing this. There is still something that may be wrong and hopefully isn’t hastily overlooked (Ryan).
Don’t put it at the end of wp-config.php or it won’t work.
Can’t believe this bloody thing is a feature! Thanks for the help
This must be a sore spot at WordPress software central.
Comment from support: “First question: Why do you think that it matters how many posts/pages/revisions/whatever are in the database? Databases are fast and efficient, and text compresses easily, you know.”
http://wordpress.org/support/topic/191420?replies=1#post-812141
Thanks for fix!!! I cannot believe WP guys came up with this “feature” and did not announce how to fix it…
It appears there is another version of this fix out there.
Is it this:
define(‘WP_POST_REVISIONS’, false);
or this:
define (‘WP_POST_REVISIONS’, 0);
@oldengine: Both is the same thing as false is equals to 0 as well.
thank you,
this was helpful!
🙂
Where do I put the snippet of code in the wp-config.php file??
After
define('DB_COLLATE', '');should be fine.Thanks for this great post Lester! I installed your Revision Control plugin, and followed your directions for deleting all the revisions in my database. Awesome! I couldn’t believe how many entries were in the database for the revisions. No wonder my post #’s were getting so high. Sheesh. This is sloppy work from WordPress.
Hi Brian, thanks for the compliments. I think the Revision Control plugin is from dd32 and is inspired by this post =D
Is this what I need to clean up the old revisions?
DELETE 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’
@oldengine Yours is more detailed, as mine only cleans up wp_post table and not the term_relationships table and wp_postmeta table.
Did the code work for you?
It came from here:
http://andreineculau.com/blog/2008/07/delete-wordpress-26-revisions/
I was just looking to see if this was a recommended procedure. (My database is a mess of revisions and it needs a complete cleaning.)