Clean Up Your WordPress Database: Post Revisions
Posted By: Behrad 
Tuesday 8 March 2011 
12 Comments
I’ve been using WordPress for a long time and I must say: I LOVE it! Not only I’m using it for my own blog but I’ve used it for clients as well and it has never let me down. It’s easy to work with, you can find millions themes (free and premium ones) and you can extend the standard functionality with plugins so what else do you need? 
If you’re using WordPress for your blog then you must have seen your database size grow over time. I mean all of your posts, pages, categories etc will be stored in the database. Having a large database size can compromise the loading time of your blog, slowing it down considerably. This is why you need to pay some attention to your WordPress Database as well. You can do this with a couple of small tricks. However before I continue: Make sure to make a backup of your database fist!! You can use a plugin for this: WordPress Database Backup.
Post Revisions:
Ever since WordPress 2.6 we can make use of the so called “Post Revisions”. This function can be useful and allows you to keep track of all the revisions. So every time a draft is saved, this is saved in the database. The next time this same draft is saved, another revised draft will be saved in the database. As mentioned it can be useful as you can return to a previous revision.
This means that you can have up to 20 revisions, off course it depends on how often you save a post. Now imagine having 200 posts which have 20 revisions on their own and imagine the size of those. Now if you want to clean up your WordPress Database and get rid off the Post Revisions, you can either use a plugin which deletes them for you. However if you’re like me and you don’t want to have too much plugins you can execute a simple query inside your database using PHPMyAdmin;
DELETE FROM wp_posts WHERE post_type = "revision";
Post Revision Query
This query will look for post type Revision within wp_posts table and will delete them all.
This can make your database much smaller. I’ve seen cases where this saves 20+ MB’s! So perhaps it might save you a couple of MB’s as well.
Disable Post Revisions:
However this feature doesn’t work for me so I’ve disabled this feature and have added the following code into my wp-config.php file.
define ('WP_POST_REVISIONS', 0);
When you use the line above you actually say that no Post Revisions is allowed except one autosave per post. However you change the 0 (zero) into 3 which means that a maximum of 3 Post Revisions can be saved. Off course you can decide who much revisions you want to save as you don’t need to stick with the 3 revisions as suggested here above.
So tell me… With how many MB’s did your WordPress database shrinked after deleting the revisions?
never thought of that since I still have plenty of space. but now since I know, I might want to clean it too.
Btw, last time I read about a plugin that can clean the option database. Option referring to plugin options that we set when we installed a new plugin. When the plugins are disabled or removed, the options still remain in the database. That plugin is supposed to look for orphaned options and removed them. May be you can research that now
Well, the same here. I don’t have to worry about my database size but less is always better.
LOL! You know; I just finished writing a post about that as well. Ok, not about the plugin but you’ll see it tomorrow when it will be published.
Also I’ll come with some more posts how to clean and optimize your WordPress database with some cool & easy tis without using a single plugin! =)
Cool! Looking forward to them
Your welcome anytime!
Thanks for this useful WordPress hacking. I think I will utilize it to limit the revision post number.
If you haven’t limited your post revisions number, then this should save you a lot of space. Give it a try and let me know how much your database size shrinked.
[...] couple of days ago I wrote a post called Clean Up Your WordPress Database: Post Revisions in which I explained how you could clean up your WordPress database. In this post I’ll get to [...]
Hey I know this is off topic but I was wondering if you knew of any widgets I could add to my blog that automatically tweet my newest twitter updates. I’ve been looking for a plug-in like this for quite some time and was hoping maybe you would have some experience with something like this. Please let me know if you run into anything. I truly enjoy reading your blog and I look forward to your new updates.
Hi Dominic,
I’ve some info about this and will post about it in a day or so… =)
Though I do not run one, WP blogs interests me a lot and I’ve always thought about how they manage such huge files and how wisely the owners use space. I can now understand why less is always better for the database size.
I’m glad it you liked the post! =)
i am also a big fan of wordpress and install it on my every website whether it is required or not
The insight to variable wp_post_revisions is nice . i shall set it to 3 or 4.
so like having 20 revisions for single post will slow down the database ? but how , because as i know those wount be selected in any query in database ?