Clean up WordPress Revisions and Meta Values

Apr 30, 2024

WordPress has a "revision" feature. Which means you can see the past changes of post, can restore a specific revision if you wish. This is good. Yet, it would be cumbersome when your posts has many meta fields, say using Advanced Custom Fields. WordPress copies all meta fields for each revision to wp_postmeta table—if you didn’t change the default prefix.

Why Clean up

Imagine one of your post type has 10 custom meta fields. WordPress and Advanced Custom Fields save two entries for each meta: one for meta value and other for field ID. Which gives us twenty entries for revision. Assuming the average revision count for each post is five, and you have fifty posts in total. So your total number of meta entries for one custom post type would be:

10 * 2 * 5 * 50 = 5,000

This is a modest scenario. I guess in many cases you would have repeater fields, which contains an additional sub fields.

One day you were oblivious to this facts. You made over ten revisions on every posts. Then you found yourself facing millions of meta entries. Crap.

That is why you should clean up the unnecessary data of meta values and revisions regularly.

Clean up

Thanks to hochwald.net. He has a great blog post Delete all WordPress post revisions with a single SQL query to do the job.

DELETE a, b, c
FROM wp_posts a
LEFT JOIN wp_term_relationships AS b ON (a.ID = b.object_id)
LEFT JOIN wp_postmeta AS c ON (a.ID = c.post_id)
WHERE a.post_type = 'revision';

I recommend to take a backup of database before running the query above just in case.

Select before delete

Here is a SELECT statement to check how many revision’s meta fields are there.

SELECT COUNT(b.meta_id)
FROM wp_posts a
LEFT JOIN wp_postmeta AS b ON (a.ID = b.post_id)
WHERE a.post_type = 'revision';

-- or

SELECT COUNT(`meta_id`)
FROM `wp_postmeta`
WHERE `post_id` IN (
	SELECT DISTINCT(ID) FROM `wp_posts` WHERE `post_type` = 'revision'
);

Plugin Solution

If you are not a developer unfamiliar to MySQL query, you can use WP-Optimize plugin to clean up the revisions and meta values. All processes can be done through dashboard.

Disable revision

If you wish to disabled revision feature, you can add the following code to wp-config.php:

define( 'WP_POST_REVISIONS', false );

// If you want to limit the number of revisions
define( 'WP_POST_REVISIONS', 3 );