When you write articles in WordPress, your revisions will periodically get saved in the database. This is a useful feature, however, each revision that is saved by WordPress results in a new record in your MySQL database. I don’t have a problem with having revisions stored in the database for my recent articles, but I don’t linke keeping the revisions that are more than, say, 3 months old. For example, 1 article can have as many as 100 revisions associated with it. That means your wp_posts table could have 100 rows to hold information of a single post. Imagine if your blog has thousands or tens of thousands of articles! You do the math on how much unnecessary data your poor MySQL database will end up having. What you really want to do is clean up your database from time to time and delete revisions in WordPress.
First of all, you can disable revisions alltogether by adding this line of code in your wp-config.php file:
define('WP_POST_REVISIONS', false);
I do like revisions, so I have written an SQL script that will delete them for me whenever I want. There are plugins out there that will delete revisions in WordPress for you. The most popular one is Delete-Revision. WHat I like about this plugin is that it will remove all revisions from wp_posts table, as well as all meta data associated with revisions which are stored in wp_postmeta and wp_term_relationships. What I don’t like about it is that it will try to remove all revisions at once. If you have tens of thousands of revisions to delete, the query could take a long time to execute and your site will suffer a performance hit.
This SQL script I wrote will delete revisions in WordPress prior to the date you specify. That way you have a better control on how many revisions you want your query to delete. Because the query does a LEFT JOIN on wp_postmeta and wp_term_relationships, to delete about 5000 revisions will take approximately 5 seconds. I don’t like my SQL queries to run more than 5 seconds on mission critical systems, so I will keep my delete batches to about 5000. You can adjust these yourselves.
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) WHERE a.post_type = 'revision' AND a.post_date < '2012-11-23'
As a general rule of thump, NEVER run a delete statement before running a SELECT statement first to verify what your query is about to delete is correct.
Marko