If you’re using a self-hosted version of WordPress you may have noticed that WordPress is keeping copies of every post revision you make. While this can be helpful in rolling a post back to an earlier version, the number of revisions can quickly get out of hand. These revisions are stored in your database, and once a post is published you probably don’t need them anymore. There are a few plugins that can help you manage these revisions, but in my opinion, if you can do this without adding another plugin, so much the better.
There’s a lot of information on the web about this topic, and I filtered through it recently and came up with this solution.
Backup your database
First, backup your database. I use a plugin called WP-DBManager to manage my database. If you don’t have a working solution for backing up, restoring, and managing your database, you really should look into using this plugin or something similar.
Modify your wp-config.php file
Next, open your wp-config.php file in your favorite text editor and add the following code:
I used “3” in this example, which will limit the number of revisions to 3, but you can change that to any number you like. Also, I added the code at line 36, after the lines referring to “Database Collate.”
When you’re finished, save the file and FTP it back to your site.
Delete existing post revisions
Modifying your wp-config.php only works for future posts, not existing ones. If you want to remove all the revisions that are still in your database you can do that with the following SQL query. Note: I am not a database or SQL expert, and this information is readily available elsewhere on the web if you would like to verify it. I do know that this query worked for me.
Login to your cpanel and open “phpMyAdmin” by clicking on the “phpMyAdmin” icon in the Databases section. Once “phpMyAdmin” opens, click on the name of the database that WordPress is using. Near the top of the screen click on the tab labeled “SQL” and you should see an empty textbox. In this textbox paste the following code:
If you’ve changed the default database prefixes to something other than “wp_” you will need to change that text in the three places where it occurs in the above example. If you haven’t changed the default database prefix, you really should for security reasons, but that’s a topic for another post.
When you’ve made any necessary changes to the code, click on the “Go” button at the bottom right of the SQL query textbox, and your existing post revisions will cease to exist and your database will be smaller – perhaps significantly smaller.
If you set your post revisions to “3” like I did, eventually those are going to add up as well. My plan is to run this SQL query every so often in the future. It’s not a “set it and forget it” solution, but it’s not all that difficult either.
You can read more about this in the WordPress codex and in the WordPress forums.