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:
1 | define('WP_POST_REVISIONS', false); |
You can also delete all post revisions by running this query in phpMyAdmin:
1 2 3 4 5 | 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.
(156 votes, average: 4.13 out of 5)
@oldengine Thanks, I have updated the code.
One small correction I’ve made just now –
DELETE a,b,c
Thanks updated =D
[...] finally, Lester Chan offers a way to delete the revisions that have already been [...]
My WP-CMS Post Control plugin allows you to control post revisions and auto saving in WordPress over at It also allows you to take control of all aspects of your WordPress write post/page panels, so you can control the visibility of the more confusing items such as hiding post revisions, but still keeping them active!
I just did it, and removed about 80 additional posts I won’t need.
Thanks for the very clean fix!
[...] points to good advice from Lester Chan to ensure that you don’t fill up your wordpress revision [...]
Thanks a lot, this solved a blank page error when trying to edit a post I made after the upgrade. All the other articles
Same for me, thanks a lot, also it solved the blank page error when trying to edit a post and I don’t think it is very useful to have a revision of each post.
Also my provider only gives me 100Mb of mysql space and I think with a open public blog it will rapidly got out of space.
Thanks and bye
[...] que vi em dois sites (primeiro e segundo) pode-se facilmente desabilitar a revisão de posts adicionando a linha abaixo no arquivo [...]
[...] información la he sacado fundamentalmente de esta web y juraría que también se habló de ello en Ayuda Wordpress, pero me ha sido imposible encontrar [...]
Thanks was looking for the option. Just wanting to mention the post is highly appreciated.
[...] WordPress guru Lester Chan found a way. An edit to the wp-config file turns off post [...]
[...] a database full of revisions, you can get rid of them. Lester Chan outlines a nice and easy way to remove the old revisions from your [...]
Phew! You just saved my day with this hack.
Awesome! I have been deleting the “iframe – malware” stuff and each time I deleted a visible iframe, I was auto-saving/revisions the hidden iframe. Your just helped me “clean” my site, Thanks! Now Google will release me.
[...] How To Turn Off Post Revision In WordPress 2.6 is the answer to how to disable. To turn it off, add the following code to [...]
Hi Lester,
Those of us that ran the original “Delete” as recommended by Andrei Neculau would now have orphaned records in the term_relationships and postmeta tables.
Any suggestions?
R
Thank you for this info. Post revisions consume a lot of my database space, so I have to turn it off otherwise I’ll run out of my hosting space.
Hi Robert,
No idea whether this code works, you want to try to see if it returns any results first.
SELECT a,b,cFROM 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.id IS NULL
Hi Lester,
Thanks for responding so quickly.
I ran the code you noted above and got the following response:
#1054 – Unknown column ‘a’ in ‘field list’
Just as an aside and an added benefit to turning of post revisioning and also the automatically saving of posts is the CPU savings. Since turning off both – I am no longer getting penalised by my Hosting Provider for chewing up CPU cycles. A big plus!!
Cheers,
R
NB: I have been using your WP-DBManager and WP-PluginsUsed and they are tops!!
Ops my bad, try this code:
DELETE b, cFROM wp_term_relationships b LEFT JOIN wp_postmeta c ON (c.post_id = b.object_id) WHERE post_id IS NULL
I tested it, it should work
Hi Lester,
You are definitely the man!!
Touch wood but it seemed to work OK. It deleted over 6000 rows – which means there must have been a lot of “dead wood” in the DB (or I’m totally screwed). Will run a repair followed by an optimise.
Thanks a million bro.
R
Hi Robert,
I hope you did a backup before you ran the code just in case
Lester,
I spoke up too soon.
My tags and categories seem to have screwed up!
Yep – I run a backup with your WP-DBManager plugin every night.
Important question: Is it possible to restore both those tables: term_relationships and postmeta individually or do I have to restore the entire DB?
R
Hi Lester,
I just ran a restore using WP-DBManager (with fingers crossed) and all seems like its back to normal.
Want to have another go at the SQL delete or do we leave it for now?
R
I just tested on my localhost, it seems to work fine. What version of MYSQL u are using?
I’m with Bluehost and they run V5.0.51a
R
Lester,
I want to run an SQL SELECT just to see what it comes back with before running the actual SQL DELETE.
SELECT b, c
FROM term_relationships b
LEFT JOIN postmeta c ON ( c.post_id = b.object_id )
WHERE post_id IS NULL
I get the following error:
#1054 – Unknown column ‘b’ in ‘field list’
Any ideas?
R