The post revision system is one of my favourite features of WordPress. It allows WordPress to keep every draft of your posts and pages. This allows you to refer back to older versions of your article. It is also useful if you experience a power outage or if your internet connection drops.
By default, WordPress does not limit the number of revisions it saves. This can make your WordPress database significantly larger than it needs to be as each draft takes up another row in you your post database table; which is a problem as a larger database increases the time it takes for your pages to load.
In this article, I would like to show you how you can delete old WordPress post revisions and make your WordPress database more efficient.
A Work in Progress
I have been meaning to optimise this blog for some time. The reason I had not did it until now was because I was writing a book about optimising a WordPress database. My idea for the book was to optimise my website, document every step of the way, and explain best practices for optimising WordPress. Great idea, huh?
By last Summer 2013, I had finished more than half of the book (well, the first draft). The book started off great, however I noticed later on that it was not flowing in the way I wanted it to. I was detailing every aspect of my website so was telling readers about every plugin I had installed. This was not ideal.
Firstly, it made it difficult for me to change the plugins I was using as it would mean changing the book. Secondly, advising readers what plugins I was using was not very helpful. It was just making the book longer than it needed to be; which is not good for that type of book. I realised that the book would be better as a reference than a walkthrough. Later this year, I hope to rewrite the book so that it reads more like a step by step tutorial rather than a behind the scenes look at how I optimised my website.
In the end, it took me until 2014 to finally make the decision to not write the book as a walkthrough; which is why this blog had not been optimised until now.
Limiting Post Revisions
The first thing you should do is decide how many post revisions you want to save on your website. I am personally against disabling all post revisions as it removes the failsafe that post revisions provide. Two or three post revisions should be sufficient for most website owners.
Limiting the number of revisions that WordPress saves is easy. All you have to do is add the following code to your wp-config.php file (this file is located in the root of your WordPress installation):
define( 'WP_POST_REVISIONS', 3 );
Revisions can be disabled by adding the following code to your wp-config.php file:
define( 'WP_POST_REVISIONS', false );
Reducing the number of post revisions that are saved does not delete the existing revisions that are stored in your database. Therefore, you need to delete this unnecessary data.
Deleting Old Post Revisions
Five or six years ago I optimised one of my websites and deleted my older post revisions. To do this, I ran an SQL query on my database.
It has since come to my attention that this may not have been the correct thing to do. Apparently, the a,b,c JOIN code query that thousands of WordPress users were using to delete post revisions was not suitable. Specifically, there was a chance that the code would delete the relationship needed to make the dashboard and links work.
Rather than execute an SQL query to delete my older post revisions, I decided to use an optimisation plugin. The plugin that I used was Optimize Database after Deleting Revisions. Using a plugin such as this reduced the odds of me entering the wrong query. It also helped me see details about the revisions that needed to be deleted.
The plugin allows you to define how many revisions are saved and whether items such as tags, spam, trash and expired transients are deleted during the optimisation process.
The number of revisions I had saved was ridiculous. There were a few of my longer posts that had over one hundred revisions. Due to that, my database was much larger than it needed to be.
Think abut it this way: One article with one hundred revisions takes up the same space in your database as one hundred published articles with no saved revisions.
A simple click of the button reduced my database size from around 223 megabytes to around 91. This total reduction was a whopping 132 megabytes.
Or to put it another way, my database was reduced by 59 per cent.
Another good plugin that can be used to optimise a WordPress database is WP-Optimize (I spoke about this plugin briefly in January). It works a little differently but essentially does the same job.
The Ideal Set Up
Post revisions are very useful before you publish an article, however in most website set ups, there is little need for those revisions to be kept after an article has been published.
Ideally, you want to keep two or three revisions available whilst you are writing an article and delete revisions a few days after an article has been published (this would give you a little time to view old revisions before they were purged). Sadly, this cannot be done directly through wp-config.php.
You will be glad to know that the solution to this is straight forward. All you have to do is define the number of revisions you want to keep via wp-config.php and then purge revisions using WP-Optimize or Optimize Database after Deleting Revisions.
Alternatively, you can use these plugins to define the number of post revisions and utilise their scheduling functionality to delete older revisions automatically. Easy!
Final Thoughts
Allowing WordPress to save an unlimited number of revisions will increase your website’s significantly over time. This can slow down your website, which is a major concern since all search engines use the speed of your website has a ranking factor. Slow websites can also make visitors click the back button before your website has even loaded.
The database for this blog was reduced by 59%. It could be reduced even more if I purge older revisions (there are currently around eight hundred revisions still in my database). It just goes to show how important it is to keep your website efficient.
If you have not implemented this change on your website, I recommend you do so as soon as you can. It will only take you five minutes to update your wp-config.php and optimise your database using an optimisation plugins. What are you waiting for? You have nothing to lose :)
As always, if you are unsure about any aspect of this procedure, please leave a comment and I will do my best to help.
Kevin