Truncate a WordPress Database To X-Number of Posts

I regularly work with huge MySQL database files (10G-100G) so making copies and importing them locally for use in VVV is extremely cumbersome (at best). Here are some MySQL statements that allow you to truncate all but the most recent 1000 posts in your WordPress database. These are useful because they allow you to truncate the database to a manageable size before copying to your local environment or development server. Unless you have something unusual, these statements should retain all post-to-postmeta associations, comment-to-post associations, and term-to-post associations.

WARNING: DO NOT run these on your production database. You must make a copy of the whole database first and then run these statements on the copy. You have been warned!

Delete all but the most recent 1000 posts:

DELETE FROM wp_posts WHERE ID NOT IN ( SELECT ID FROM ( SELECT ID FROM wp_posts WHERE post_type="post" AND post_status="publish" ORDER BY ID DESC LIMIT 1000 ) foo );

Delete all post meta that is not associated with the remaining posts:

DELETE FROM wp_postmeta WHERE post_id NOT IN ( SELECT ID FROM ( SELECT ID FROM wp_posts ) foo );

Delete all comments that are not associated with a remaining post:

DELETE FROM wp_comments WHERE comment_post_ID NOT IN ( SELECT ID FROM ( SELECT ID FROM wp_posts ) foo );

Delete all comment meta that is not associated with a remaining post:

DELETE FROM wp_commentmeta WHERE comment_ID NOT IN ( SELECT comment_ID FROM ( SELECT comment_ID FROM wp_comments ) foo );

Delete term relationships that don’t associate to a remaining post:

DELETE FROM wp_term_relationships WHERE object_id NOT IN ( SELECT ID FROM ( SELECT ID FROM wp_posts ) foo );

Delete terms that are no longer related to an existing post:

DELETE FROM wp_terms WHERE term_id NOT IN ( SELECT term_taxonomy_id FROM ( SELECT term_taxonomy_id FROM wp_term_relationships ) foo );

As an added bonus, delete transients from the options table:

DELETE FROM wp_options WHERE option_name like '%_transient_%';

And here’s the whole thing in one shot – script it in if you wish:

DELETE FROM wp_posts WHERE ID NOT IN ( SELECT ID FROM ( SELECT ID FROM wp_posts WHERE post_type="post" AND post_status="publish" ORDER BY ID DESC LIMIT 1000 ) foo );
DELETE FROM wp_postmeta WHERE post_id NOT IN ( SELECT ID FROM ( SELECT ID FROM wp_posts ) foo );
DELETE FROM wp_comments WHERE comment_post_ID NOT IN ( SELECT ID FROM ( SELECT ID FROM wp_posts ) foo );
DELETE FROM wp_commentmeta WHERE comment_ID NOT IN ( SELECT comment_ID FROM ( SELECT comment_ID FROM wp_comments ) foo );
DELETE FROM wp_term_relationships WHERE object_id NOT IN ( SELECT ID FROM ( SELECT ID FROM wp_posts ) foo );
DELETE FROM wp_terms WHERE term_id NOT IN ( SELECT term_taxonomy_id FROM ( SELECT term_taxonomy_id FROM wp_term_relationships ) foo );
DELETE FROM wp_options WHERE option_name like '%_transient_%';

Leave a Reply