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_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
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.
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
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
Again, in your phpMyAdmin area, click on the “SQL” button, and paste the following code in the SQL command window, and hit Go.
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.
|Database reduced by:
(Compared to Baseline)
(Before Executing the SQL commands)
|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
You could put the code above anywhere at the beginning of the config file as long as it is inside the opening PHP tag (
To Limit WordPress Post Revisions:
Alternatively, you can use a positive integer to limit the number of revisions.
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.