here is the commonly recommended sql command for removing posts revisions and cleaning up the wp database:

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

how can i modify it to keep let's say the last 3 revisions ?

  • 1
    Please rework your question to bring into WP context. So far it's just plain SQL and would belong to SO.
    – kaiser
    Sep 10, 2013 at 13:27
  • well, these tables and the revision system i am talking about is wordpress specific
    – Matoeil
    Sep 10, 2013 at 13:52
  • It doesn't matter if those tables exist in WordPress. The could as well live in a fork named WorkDress and work similar. What exactly is WordPress specific about your question? Do you use $wpdb? No. Do you want to know where this is best hooked? No. Do you want to have WPDB Error output? No.
    – kaiser
    Sep 10, 2013 at 14:05
  • Btw, please also rework your question to use proper Capital letters. All the questions on the SE network shall be easily readable for later readers as well who face the same problem.
    – kaiser
    Sep 10, 2013 at 14:06
  • to put in another words: how wordpress store in dbs post revisions? and how can i select using sql query only a subset of revision ?
    – Matoeil
    Sep 10, 2013 at 14:20

2 Answers 2


You can prevent more than three revisions from being saved to the database in the future by adding this line to your wp-config.php file:

define( 'WP_POST_REVISIONS', 3 );

That line should limit new posts to three revisions, but it won't go through your database and clean it up.

The SQL snippet that you found for deleting old revisions from the WordPress database has problems, which you can read about in this thread on WordPress.org. The thread outlines the problems, and offers alternatives. The thread also reviews some plugins that will do this for you.

  • thanks very much for that, i will look at this thoroughly
    – Matoeil
    Sep 10, 2013 at 15:29

Here's an approach to delete all revisions except the last one (I know its not what was asked originally but I hope this helps somebody to use a similar solution).

Step 1. Create temporary table with the revisions that you want to keep:

DROP TABLE IF EXISTS t_keep_revisions;
CREATE TEMPORARY TABLE t_keep_revisions AS (
        MAX(p.ID) as ID,
    FROM wp_posts p
    WHERE p.post_type = 'revision'
    GROUP BY p.post_parent
    ORDER BY p.post_parent

Step 2. Delete all Revision Posts except those which ID is in the table created above

DELETE FROM wp_posts
WHERE post_type = 'revision'
    FROM t_keep_revisions

Step 3. Delete orphan metadata from wp_postmeta

DELETE FROM wp_postmeta
WHERE post_id NOT IN (
    SELECT ID FROM wp_posts

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.