You know i’m all for “If you can do it yourself then don’t use a plugin”. That goes, without saying, to my plugins too. So, i saw a plugin recently doing a very useful thing, cleaning up post revisions. But what is a post revision? Well, it’s a safety measure the WordPress team took since version 2.6.X to protect your writing from unfortunate circumstances that may occur. Moreover, it’s a way to have different versions of your posts. When you write you tend to delete and start over. At some point you might want to go back and see what your post looked like back then. Pretty much this is what post revision is.
But, when you are done with a post, you most probably will not use that ever again. So, think about having ten versions of a certain post, or even worse, ten versions of many posts. That’s a lot of unnecessary info and clutter for your database. To give you the whole picture, post revisions rest in the table that your actual published posts are. This is bad in two ways. One, it’s making it harder to index and make a query on the table. Even worse, when a query joins this table with another one then the result of the join will have a lot of junk lines, therefore, alot of no needed info. Two, imagine a post replicated ten times on your database. It could be like 60Kb or even, much more. Now that is alot of fragmented space. For this reason it’s a very good idea to remove post revisions.
The plugin can do the job for you but, as i already said, this is a trivial job and you can easily do it by hand. All you need to do is run a simple SQL query on your database and you will be done. Before going any further please make sure you keep a backup of your database for the scary moment that something goes wrong. Open your sql manager (either console or phpmyadmin or whatever you use to access your database). Run this query:
DELETE FROM wp_posts WHERE post_type="revision";
You are done! Please make sure you type exactly this and nothing less or you might be in serious trouble. Now, just to improve things for you, you might want to delete all revisions that are older than a month. This is what you are looking for:
DELETE FROM wp_posts WHERE post_type="revision" AND post_date="2008-11-18 00:00:00";
This deletes all revisions that are older than a week today. So, go ahead and get rid of all the clutter. When i did so, i reduced my database’s size by 1.2Mb! I know, impressive huh? Keep one thing in mind though, backups, backups and more backups. The more the better 😉
Great tip Stratos!
I moved a blog to it’s own domain today and of course messed up something so I went to the database to edit the url in the settings so I could access it again. While I was in there I noticed what seemed like multiple copies of the same article and then quickly realized it was the revisions. I am going to do this tomorrow. (and don’t worry I have experience mucking around with the database). This is so much better than using a plugin.
Though I always knew about post revisions in wordpress, I never realized it would have crossed 750 rows in wp_posts. Whoa!! Thanks for helping me in cleaning up my database 🙂
Btw, both the queries failed initially. I am using phpMyAdmin. After playing around with the query, this is the one which worked
DELETE FROM `wp_posts` WHERE post_type=”revision”
@Kim Thanks for dropping by. Yes actually you don’t need a plugin to do such a job especially when you are comfortable playing with the database…
@Raju i thought this would happen… so all phpmyadminers out there surround your table names with “`”! thanks Raju for bringing this up!
So, I’m assuming something similar could be done with categories. In one of my blogs about 1500 posts don’t have a category – well, they have the uncategorized one. Could I do a search on uncategorized and change it to some other more useful term? How would I do this? (and I use phpadmin).
i’ll get back to you with my next article since this is a slightly complicated issue… stay tuned 😉
Writing such posts need a clear insight about wordpress and database… You have done a very good post…
Enjoy my stumble 🙂
@Pavan Thanks man. I am trying to mess around with the code and see what is going on. Thanks for the stumble and for reading!
I stumbled on top of it 🙂
@Raju thanks! hope you guys find it useful!
Hi Stratos,
I tried this again today and this time the query without “‘” worked, what is the reason? i am still using phpMyAdmin like before
@Raju: If i am not mistaken you moved to a VPS. Maybe a newer version of phpMyAdmin is used or something because the “`” thingy is just a configuration. In general i would suggest adding it just to be safe. MySQL understands both…
I cleared up my database thanks to you Stratos. I found this post via kim’s how to disable post revisions. The next obvious step was to clean up the database. Thanks mate.
@Sire: I am glad this helped! It really saves a lot of clutter indeed….
Not that I never noticed it before, but once I found out about it, I just had to clean it up. Too bad I don’t feel the same way about the clutter on my desk. 😆
@Sire: Same goes for my desk 🙂