How to remove in the wordpress database all posts revisions except the last three?

2 Views

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 ?

Post Views: 2

Related posts

2 comments

  1. 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.

  2. 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 (
    SELECT 
        MAX(p.ID) as ID,
        p.post_parent
    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'
AND ID NOT IN (
    SELECT ID
    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
)

Comments are closed.