Clean up WordPress post revisions

Remove WordPress post revisions with an SQL statement

Clean up WordPress post revisions, save on MySQL database size and improve WordPress' performance.

To delete the WordPress post revisions you only need one SQL statement. Execute this SQL query to remove all post revisions from your MySQL 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
LEFT JOIN `wp_term_taxonomy` d ON b.term_taxonomy_id = d.term_taxonomy_id
WHERE a.post_type = "revision"
AND d.taxonomy != "link_category";
DELETE from `wp_posts` WHERE post_type="revision";

This'll remove all WordPress post revisions, your MySQL database is smaller and faster! Don't forget to change wp_ with your database table prefix.

Limit or disable WordPress post revisions

The maximum number of saved revisions can be configured in the wp-config.php file. To save a maximum of five revisions, use:

define( 'WP_POST_REVISIONS', 5 );

Or to completely disable the WordPress post revision functionality, use:

define( 'WP_POST_REVISIONS', false );

However, according to Mike Little (the co-founder of WordPress), WordPress' database queries do not retrieve revisions. The number of revisions should have no effect on the queries. The only time the revisions are retrieved is on the edit page of the individual post. There is no impact on the front end speed of your site.

The post revision feature can be a lifesaver.

Bonus: Clean up comment and post meta data

Clean-up WordPress spam comments and meta data
Delete spam comments after three (3) days

If you want to clean up your WordPress MySQL database even further, then you can use the following SQL queries / statements to lookup and remove post meta data and comment meta data. Don't forget to set your table prefix.

-- Clean WordPress Post Meta Data
SELECT * FROM wp_postmeta pm LEFT JOIN wp_posts wp ON wp.ID = pm.post_id WHERE wp.ID IS NULL;
DELETE pm FROM wp_postmeta pm LEFT JOIN wp_posts wp ON wp.ID = pm.post_id WHERE wp.ID IS NULL;

-- Clean WordPress  Comment Meta Data
SELECT * FROM wp_commentmeta WHERE comment_id NOT IN ( SELECT comment_id FROM wp_comments );
DELETE FROM wp_commentmeta WHERE comment_id NOT IN ( SELECT comment_id FROM wp_comments );

SELECT * FROM wp_commentmeta WHERE meta_key LIKE '%akismet%';
DELETE FROM wp_commentmeta WHERE meta_key LIKE '%akismet%';

As always: use with care.