How to Delete and Limit Post Revisions in WordPress
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?
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';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';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?
I would say that the improvement of this 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?
