Handling WordPress Post Revisions the right way

I have came across so many blogs which talk about disabling WordPress Post revisions to have some performance gain but almost each of them gives the solution which is not correct, at least not completely.

Deleting WordPress Post Revisions

They just tell the users to execute the following MySQL command but its not the correct way of doing so.

DELETE FROM wp_posts WHERE post_type = "revision";

It will leave some data related to revisions in the database. The correct method of removing the post revisions is to run this command

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';

Even the above method leave some traces behind, Mike Schinkel explains in comments how post revisions can be removed by creating a custom PHP file in which we loads WordPress and run a loop deleting every revision. Here is the code :

<?php
require_once('wp-load.php');

$posts = get_posts('post_type=revision&post_status=any&numberposts=-1');
foreach($posts as $post)
{
echo "{$post->post_title}\n";
wp_delete_post($post->ID,true);
}
?>

Create a php file with the above code and load it to WordPress root and then open in browser. It will delete all the post revisions and will output their titles too. Thanks Mike Schinkel!

Disable WordPress Post Revisions

You can disable them by adding the following line in your wp-config.php file (located in WordPress root)

define('WP_POST_REVISIONS', false);

Limit WordPress Post Revisions Count

You can also limit the number of post revisions count to 3 or 5 by adding the following line in your wp-config.php file (located in WordPress root)

define('WP_POST_REVISIONS', 5);

Just change the value as per your needs.

Advantages of disabling & removing Post Revisions

Post Revisions are created for changes you make to the post. You may not (in fact many of us) don’t need to keep them and it also increases our database size. Disabling them takes care that post revisions are not created and removing them is a a good option to lighten your database size. Remember, smaller database means there is less stuff to look for which means faster speed.

Thanks to Sitepoint for the correct code. If you like any kind of help with WordPress then let me know, I would love to help you πŸ™‚

Published by

Ashfame

Developer + Entrepreneur + Open Source fanatic. Love WordPress + Startups + Adventure + Music.

  • Agreed that the simple DELETE command will leave orphans, but then the other DELETE command you suggest could also potentially leave orphaned data if there are plugins used that add data related to posts. So really the best way to delete post revisions is to do it with PHP code that uses functions from WordPress core. Doing so will ensure deletion of any new data related to posts introduced in newer versions of WordPress and/or to leverage hooks to delete post-related plugin data.

    The following can be saved to a standalone file in the root of your WordPress installation and when loaded in a browser will delete revisions and any related data. The same basic code sans the require_once of wp-load.php could also be used in a plugin to do the same:

  • One more time???

    require_once(‘./wp-load.php’);

    $posts = get_posts(‘post_type=revision&post_status=any&numberposts=-1’);
    foreach($posts as $post){
    echo “{$post->post_title}\n”;
    wp_delete_post($post->ID,true);
    }

    • Superb! I will make the changes in post πŸ™‚

    • Fantastic code – thank you so much. I just spent the day browsing around for a plugin or method to delete the mass of revisions on my sites but this worked perfectly first time.
      Thanks again

  • πŸ˜€ never knew this helps to speed up the site… Will clear them out….

    • They will make a difference on old WordPress installations which have several records (rows) of them.

  • Babaji M P

    Hello Ash,

    I uploaded the php file named rev.php as stated by Mike Schinkel.
    Went to url mysite.com/rev.php and mysite.com/rev but nothing happened.

    Ran SQL with the above code,
    The result was 2068 row(s) deleted. ( Query took 8.6624 sec )

    But i still see the revisions under each and every posts.
    Any suggestions ?

    • Hi,

      There was a slight issue with the code snippet. The single quotes were different. I have fixed them now.

      Give it a try.

      And I am clueless about your observation. Even after deleting from database, they still show up. Anyways try the edited code now and let me know. πŸ™‚

      • Babaji M P

        Wonderful.
        Got a blank page. Checked the posts and no revisions found.

        Thanks Ashfame !

  • hello i want to ask what exactly do i neeed to do ? u mean i shud create a php fole and copy
    post_title}\n”;
    wp_delete_post($post->ID,true);
    }
    ?>

    what shud i name this file ? and do i need to upload this file in the theme directory which i m using ? and after that simply do i need to open http://www.mydomain.com/filename-which-i-have-given.php/ in the browser ?
    sry for noob question bu t i m newbie so plz help πŸ™

    • Create a PHP file and drop it in WordPress root, then load in the browser.

      • u mean in public_html or my theme folder ?

        • where you can see folders wp-content, wp-admin etc

  • i mean this code
    post_title}\n”;
    wp_delete_post($post->ID,true);
    }
    ?>

  • Nice post! Very helpful.

  • Would be great to see some code to bring the revision count for all existing posts in sync with the newly set revision limit setting. Saving a post correctly trims them for that post, but it’d be great to trigger it for all of them.

    • You mean to clear up additional revisions that are stored beyond what you have specified your revision limit, because they were already created before you limited their count?