comments 9

Painlessly Remove Post Revisions From Your WordPress Database

Database Revisions?

Database Revisions?

Take-away message: Removing those pesky post and page revisions from your WordPress website and optimising the database is easy to do and it can make for a leaner and speedier website.

Introduction

As you may know, there are two flavours of WordPress.

There is the WordPress.com version that runs on WordPress’s own servers. And then there is the WordPress.org version that you run on your own server or with a commercial web hosting company. This article is about the WordPress.org version.

The Quillcards Blog is of the WordPress.org self-hosted variety and it has been going for a while. In fact, I see that we posted the first article in October 2008.

WordPress is an elegant system that is made up of two main parts.

One part – the text of the articles, etc. – is contained in a database. The other part – the appearance of the site including for example the colours and the layout – is contained in a separate series of files.

When a visitor looks at the site, what happens very cleverly behind the scenes is that the WordPress software marries up the appearance with the data to present what the visitor sees.

And it all happens in a split second.

The thing is that the WordPress database stores all the drafts and revisions of the articles.

And as time goes on, this mass of information can get very big. And most of it is just not needed.

For example, do I really want to know what was in draft #7 of an article I wrote three years ago?

No, I just want to keep the finished article.

How All This Extra Data Gets In There In The First Place

If you write a blog, are you one of those people who writes as a stream of consciousness and then hits the publish button?

I don’t think many people do.

Tamara and I almost never do that. When I write a post, I save a draft and then review it. Then I make some changes and save the draft again.

Then Tamara will look through the article and point out anything that she thinks could be made clearer to the readers.

I take that on board and make the changes and save the draft. I make one final check and then I hit ‘publish’.

It’s the same with Tamara when she writes an article and I act as her editor (except she is a better writer and editor than I am).

If it’s a long article with a lot of research and information in it, there might be 20 or 30 revisions before the final published version.

Some of those revisions will be tiny. It might be just an added comma or a single word changed.

But each and every time a revision is saved, it is a draft of the complete article that is saved and added to the database as a post revision.

In other words the difference between versions #7, #8, and #9, for example, might be no more than a couple of commas.

Changes To Published Articles Are Also Stored As Revisions

Besides revisions to draft articles, there are also the changes that are made to articles that have been published.

And every past version is saved and added to the database.

Want to know how many revisions we had in the database before we cleared them out yesterday? Over 8,000.

How To Get Rid Of All Those Pesky Revisions

I searched for how to delete the revisions in the database itself. And I came across a number of plugins, but none of them did what I wanted.

That is, until I found Optimize Database after Deleting Revisions in the WordPress repository.

It’s free and it’s great because it removes post revisions and optimises the database all in one action.

I set it to keep the last three revisions of every post (don’t ask me why – I just thought it was a good idea to keep some drafts) and then ran it.

It deleted 7,975 post and page revisions and lopped a mighty 100.807MB from the database – and took about 45 seconds to run the script.

You have to admire the power and quality of the plugin.

If you try Optimize Database after Deleting Revisions yourself, let me know how you get on.


The Photograph At The Top Of This Article

If you take another look at the photograph at the top of this article, you might wonder whether I really found an abandoned building with that graffiti scrawled on it.

The building is at the now defunct Lady Victoria Colliery near Edinburgh that I photographed a couple of days ago, but there is no graffiti on it.

I added the graffiti in Photoshop just now.

The font is called Floydian and I wrote the text and then rotated the layer to give the appearance that it might have been written on the wall.

Post Script For The Technically Minded

We don’t have phpMyAdmin installed on our server but I have the sequelpro app with which I can tunnel into the database via ssh. Either way, I couldn’t figure out a way to kill the post revisions directly in the database, and the Optimize Database after Deleting Revisions plugin does a fine job with very little overhead.

9 Comments

  1. I often revise my posts and pages – I’m glad to see I’m not the only one 🙂

    I already use a plugin to deal with the revisions, but the one you’ve recommended here gives more options so I might give that one a go.

    I just checked the plugin I use and it’s removed 9881 revisions for me so far – that’s a scary number of useless revised posts and pages that would have been in the database!

    • David Bennett

      Angela – for some reason your comment went into spam. Sorry for the delay in seeing it.

      Nearly 10,000 revisions is a lot 🙂

      There’s an option to schedule the ‘ Optimize Database after Deleting Revisions’ plugin to run all the time but I have not set it to do that. I’d rather let the numbers build up for a week or three and then run the script again.

  2. Thanks for this recommendation. I had tons of drafts that I was dying to get rid of but didn’t know how. My site was running slow, and I am hoping that this, in conjunction with some other things, will help the loading speed.

  3. Post revisions are useful, but when too many post revisions take a lot of space in your hosting. I wrote a tutorial about this where the post revisions can be limited to a certain number (including the post revisions for all old posts) using a plugin called Better Post Revision.

Leave a Reply