How to Delete and Limit Post Revisions in WordPress
WordPress Revisions?
WordPress 2.6 and above has a new feature which is the posts and page revision. WordPress automatically creates revisions of your posts or pages. Whenever you save a post or a page, the old version is retained so you can revert back at any time. Older revisions are never deleted so you always have a full history of all changes. However, this feature causes your WP database to rapidly increase in size. Depending how active your blog is, revisions can seriously bloat your database with multiple copies of the same post. Each additional revision will slowly increase the size of your MySQL database.
Sometime it is necessary to do some cleanup. Every revision requires a separate record in wp_posts table and probably entries in the wp_term_taxonomy, wp_postmeta and wp_term_relationships tables. Removing older revisions will free up disk space.
Too many revisions stored in the database might cause longer query time. This means that your blog will load slower and your visitors may wait longer before the content loads. This also leads to high CPU or memory load on your Web hosting server. To my shock, I realized that in this blog I have anywhere from 9 to 47 unnecessary revisions per post/page. Wow!
Does reducing the database size, also speeds up your Website load time?
Below is a partial post revisions image from my WordPress admin area.
As you can see from the above image, removing old revisions is one of the best things you can do to your WordPress blog to keep it running efficiently. For a personal blog, many versions of the same post is a waste of resources. Excessive revisions will increase the burden on the database, slows down your WP admin area, your phpMyAdmin and may slow down the download time of your WP Website.
WP Revision Options
The WP_POST_REVISIONS constant has 3 options.
- true (default), -1: store every revision
- false, 0: do not store any revisions (except one for auto save)
- (int) > 0: store that many revisions (+1 auto save). Old revisions are automatically deleted.
Deleting Existing Revisions Using SQL command
Before we start, I am going to say this once: BACK UP YOUR DATABASE. You are about to run SQL statements to your WP database, and if you make a mistake you can revert back to your backup version.
To permanently delete all existing revisions for all pages and posts, login to your phpMyAdmin, select your WordPress database, click on the “SQL” button and paste the following code in the SQL command window. For a detailed explanation about SQL command1, the tutorial is in the References section at the end of this post.
SQL command1:
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 ) LEFT JOIN wp_term_taxonomy d ON ( b.term_taxonomy_id = d.term_taxonomy_id) WHERE a.post_type = 'revision' AND d.taxonomy != 'link_category';
I am assuming that you kept the WordPress Database Table prefix as
wp_, if not change the above code accordingly.
wp_is the default but it can be changed to give your Website added security.
My WP Database After Executing SQL command1
After executing SQL command1I noticed that NOT all stored revisions are deleted in the my WP database. Checking the post_type field in the wp_posts table, I noticed that there are still many “history” data that have not been removed. It seems that the above code still leaves some traces behind. This brings me to the execution of the next SQL command2.
To better explain, the wp_posts table has a field named post_type. This field can have many values, such as post, page or revision. When we want to get rid of all revisions, we run a SQL command to delete any entry in the wp_posts table in which the post_type field is equal to revision.
Again, in your phpMyAdmin area, click on the “SQL” button, and paste the following code in the SQL command window, and hit Go.
SQL command2:
DELETE FROM wp_posts WHERE post_type = 'revision';
Notice the use of single quotes around the term revision in SQL command2. If SQL command2 does not work for you, use double quotes instead.
My WP Database After Executing SQL command2
Now after executing SQL command2, all previously saved revisions stored in the WP database are totally removed. There are no orphaned data left in the database.
The following table shows my WP database before and after executing SQL command1 and then SQL command2.
| table: wp_posts (Records) |
table: wp_postmeta (Records) |
table: wp_term_relationships (Records) |
Database size (KB) |
Database reduced by: (Compared to Baseline) |
|
|---|---|---|---|---|---|
| Baseline (Before Executing the SQL commands) |
789 | 215 | 494 | 7,558 | n/a |
| After Executing SQL command1 | 416 | 215 | 121 | 4,993 | 33.93% |
| After Executing SQL command2 | 44 | 215 | 121 | 509 | 93.26% |
Wow! After executing SQL command1 and then SQL command2, the size of the database has been reduced by 93.26%, and the wp_posts table has now 44 records down from 789 records originally. That would tell you how much the WP revision feature can accumulate unnecessary records in the database. Now the question that comes to mind is how to prevent or at least limit the number of revisions in WordPress?
Disable or Limit Revision feature in WordPress
To Disable WordPress Post Revisions:
You can turn off post/page revision feature by editing your wp-config.php file located in your WordPress root directory. Simply add the following code at the top of the wp-config.php file:
//Turn OFF WP revisions feature.
define('WP_POST_REVISIONS', false);
You could put the code above anywhere at the beginning of the config file as long as it is inside the opening PHP tag (
<?php)
To Limit WordPress Post Revisions:
Alternatively, you can use a positive integer to limit the number of revisions.
//Limit WP revisions. Value can be any positive integer number.
//Create a maximum of 2 revisions, plus one for auto-save.
define('WP_POST_REVISIONS', 2);
What About Website Loading Speed?
After the size of the database has been significantly reduced (93% in my case), I noticed that:
- WordPress administration area is faster to load and to respond to user requests.
- phpMyAdmin area, where the WP database reside, is faster to respond to user requests.
But the biggest question I have: Did deleting WordPress post and page revisions have any impact on this Blog loading speed?
Does Deleting Post and Page Revisions Speed up Your WordPress Website?
To answer this question, I used two online tools: WebWait and Webpagetest. I tested my Blog´s main page download time before and after deleting the revisions. The results are shown in the following 4 images:
Webwait – Baseline test results. My Blog download speed BEFORE deleting all post and page revisions. There is a 20 sec delay between each run.
Average load time after 30 runs = 15.28 sec
Webwait – Test results. My Blog´s download speed AFTER deleting all post and page revisions.
Average load time after 45 runs = 15.04 sec (1.5% improvement)
WebPagetest Complete Data – Baseline test results. My Blog´s download speed BEFORE removing all post and page revisions.
Average load time after 10 runs = 18.79 sec
WebPagetest Complete Data – My Blog download speed AFTER removing all post and page revisions.
Average load time after 10 runs = 17.46 sec (7% improvement)
From the images above, I would say that the improvement of my Blog´s download time is statistically insignificant. What remains to be seen is that if you have a much larger database with hundreds of posts, would that make a significant difference? I will leave that up to you sharing your experience in the comment section.
Just like post/page revisions, spam comments can take up some serious database space. It is easy to delete them all in the WordPress admin area.
You probably noticed that my Blog is somewhat slow, I will discuss improving Website download time in my next posts.
When you say add the disable code at ‘the top’ of the wp-config.php file, at ‘the top’ of what, specifically? Above the first <?php-the base configurations of wordpress line, or above //** MySQL settings-? (I added it above MySQL settings.)
Thanks for these great tips, by-the-way; they're exactly what I was looking for.
Hi Penina,
I mean you can put it anywhere at the beginning of the config file as long as it is inside the opening PHP tag (
<?php). I will make it clearer in the post.I’m glad this tutorial helped you. Thanks for your comment.
Cheers, good walkthrough. Is there also a setting where you can set the time between autosaves? I know that there are plugins that do that, but I rather limit the number of plugins.
Thanks
Hi Harry,
yes there is. Add it in your “wp-config.php” file:
<?php /** Autosave every 300 seconds (default is 60 seconds) **/ define('AUTOSAVE_INTERVAL', 300 ); ?>Much appreciated for the information and share!
Thanks Boutros, This is a very interesting study you’ve done here. I was curious to the benefit of deleting old post revisions, and you have cleared that up for me. It certainly makes sense, not to bloat the database with unnecessary files.
Thanks, Mark
You are welcome Mark. Glad I was able to help.
You have good points here. Most people agree with you.
Thanks, this was just what I was looking for. I’m curious to know how it affects larger blogs though. Not really worth the trouble for that little bit of an improvement.
Hi Will,
Thanks for your comment. Actually this tutorial helps much more larger blogs than smaller ones. If a blog adds 7 post per week at 10 revisions per post.
If you don’t limit post revisions (i.e. you keep the default WP unlimited revisions), then you will end up storing in the Database 70 unnecessary posts.
You see how your Database gets bloated, becomes large and inefficient? Your Database should be kept at its optimum performance and should be optimized regularly.
In terms of loading speed, there is definitely some improvement but that varies. Even if you get a 5-10% improvement in faster loading time, I think that should be worth the effort. If the blog is small with couple of tutorials per month, then it is probably not worth the effort cleaning up the Database (even though I will personally clean it up), but at least you could add single line of code in your ‘wp-config.php’ to turn off or limit post/page revision feature for future posts or pages.
Boutros.
This web site is really a walk-through for the info I want and didn’t know who to ask. Thanks.
veldig interessant, takk.
(Translate “veldig interessant, takk.” from Norwegian: very interesting, thanks.)