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)
Lester,
Interestingly when I do a browse in PHPmyadmin of the postmeta table – I cannot find any rows with “post_id” set to NULL.
R
NB: Everything back to normal since I did the restore earlier this afternoon. So it’s not an urgent situation and it’s not even one of your plugins that’s playing up. So, unless you are really keen Lester – just leave it for now.
NB2: Have you ever come across a good diagram that outlines the relationships between the different tables in WordPress? If I had one of these I could study the entity relationships and in the process gain a better understanding of the database aspects of WP.
Hmm I have not come accross any diagrams of that sort yet. If no post_id is NULL, i am guessing that there is no left over term and postmeta.
Thanks Lester,
Keep up the good work bro!!
R
Hi Lester,
This worked perfectly for 2.6. But when I upgraded my blog manually to 2.6.1, post revisions still accumulate, even when they’re turned off in the config file.
Know of any reasons as to why this could happen?’
Thanks.
I have updated to 2.6.1 and it still works for me. I don’t see why it do not work for you. You overwrite the wp-config.php?
Nope, took extra care not to overwrite that one.
I have WP-Cache turned on. Could it be that?
Huh, that’s weird.
I just moved the placement of the define line in the config, and changed ‘false’ to ‘0’. Worked again.
I don’t have an idea whether WP-Cache caused it though.
What about after that change back to false again, does it work? I am using false lol
Yeah false works too (again).
Seems like I only needed to change the placement of the line, which is still weird. But hey, I’m not one to complain when things work. 😀
Hmm glad it works. But it is quite weird
Hi Lester,
Fantastic work! Just to add one line that results in a whole lot of unnecessary stuff no more there! And I was already wondering my my post-id’s were shooting up so rapidly!
Thanks a lot!
Hey Lester,
At least have the respect to correct IN PLACE your inflammatory comments regarding Autosave. It does not add to the revisions table. Fix your content rather than leaving it to the reader to realise that you ‘face-palm’ got it wrong.
I’ll be back later to check.
Ops my bad, updated.
I imagine the loops and search queries would gradually take longer with each month if not for your genius one-liner solution. Many thanks.
I can’t thank you enough for your solution! TTTTTTTTTTThank you
Thanks a lot! I was having problems with Gengo connecting my translations with the revisions! That should fix it. Thank you very much!
just see the number of replies here the ppl who added this to WP as a default setting should feel ashamed. luckily enough there is this configuration to use. I hope the state of art simple config file won’t be filled up with these mandatory disable statements in the future.
Thanks… ALOT. I was wondering why this sh…t was popping up and everytime I save a page I got pushed to the manage pages without saying anything.
Later on when I notice I got so many “Post Revisions” at the VERY bottom of the screen I know something terrible is going on. Shall add that thousands of thanks to your name.
Thank you, Lester Chan!
Thank you, Lester Chan!
Thank you, Lester Chan!
Thank you, Lester Chan!
…
Thank you, Lester Chan!
How about a plugin to remove those revisions now? I got maybe hundred…
Hi Binh, I think there is a plugin by DD32 that controls the revision. You can run the sql query to remove the revisions =)
Thank you so much that drove me crazy too. 🙂
You saved me a ton of wasted database entries. Thanks!
Beyond being annoying, this creates all kinds of legal problems, as the post’s whole history can now be subpoenaed. Previously you could change a post the minute someone complained, and by the time the guy got a lawyer and decided to sue for libel, the offending text would be long gone (most people in the heat of anger call you up before thinking to make a copy of the post).