Delete Revisions in WordPress – HOWTO

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

  • http://gravatar.com/alexga939560811 alexga939560811

    Hi,
    This is really interesting. What if I want to delete all the duplicate drafts (+meta + relationships). I am trying this but it is not working:

    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_status = ‘draft’ AND wp_posts.ID IN (SELECT max(wp_posts.ID) FROM wp_posts GROUP BY wp_posts.post_title HAVING count(wp_posts.ID)>1)

    Do you have any idea on how to do so please?

    Thanks a lot

  • http://twitter.com/mtomic Marko Tomic (@mtomic)

    Alex, what do you mean by ‘duplicate drafts’? I presume you’re referring to ‘revisions’ in which case the query in this blog post will do that for you. WordPress will create revisions for every post whether it’s in published or draft status.

  • http://gravatar.com/alexga939560811 alexga939560811

    Hi Marko,
    In WP you can choose to post as a draft. It happened that I have uploaded via CSV import many posts as draft (status) but some have been imported twice. I can delete all the drafts + metas + relationships associated by replacing “WHERE a.post_type = ‘revision’” by “WHERE a.post_status = ‘draft’” in your script.
    The thing is I do not want to delete all the drafts + metas + relationships but only duplicate entries. And this part is the tricky one as I have tried a lot without success.
    Thanks a lot Marko!

    • http://www.markomedia.com.au Marko Tomic

      Sorry @Alex I just saw your reply! If you know that your duplicate posts are identical (i.e. share the same ‘post_name’ and content), why not add another subquery to exclude original post ids from being deleted. You can get a list of those duplicate post ids by running something like

      select ID
      from wp_posts
      where post_type = 'draft'
      group by post_name

      Back up your DB before deleting things from it ;)

  • A. N.

    This is most useful. Thank you. What is the likelihood of you showing a pupil how to use a third party api with a wordpress site?

  • http://twitter.com/mtomic Marko Tomic (@mtomic)

    Very likely!