Over on HTG main we’ve been working on cleaning things up after getting hit with an unexplained 35% drop in Google traffic, and as part of that process we’ve been deleting old posts that we don’t really want around anymore.
The problem is that once you delete hundreds or thousands of posts in WordPress, it’s basically impossible to empty the trash using the interface — it’ll take a few minutes and then time out without successfully emptying the trash.
Luckily there is always a better way… and in this case, it’s as simple as running a database script. You can launch a MySQL console by running this command from the terminal, using the values from your wp-config.php file to connect to the database.
mysql -uUser -pPassword database
Once there, paste in the following, and promise not to get mad if your whole site breaks. Maybe you should have a full backup of your website, right?
DELETE wp_posts,wp_term_relationships,wp_postmeta,wp_term_taxonomy FROM wp_posts LEFT JOIN wp_term_relationships ON ( wp_posts.ID = wp_term_relationships.object_id ) LEFT JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id ) LEFT JOIN wp_term_taxonomy ON ( wp_term_taxonomy.term_taxonomy_id = wp_term_relationships.term_taxonomy_id ) WHERE wp_posts.post_status='trash';
What this will do is not only delete the post record itself from wp_posts, but also the extra metadata from wp_postmeta, wp_term_relationships, and wp_term_taxonomy — the latter two are for category and tag assignments, and the former is obviously for metadata about the post itself.
The DELETE part of the statement lists out the tables that we want to delete data from, and MySQL will delete all records in those tables that are matched by this query. So, obviously, if you’re going to tweak this technique you should be realllllllly careful.
You could, of course, only delete the posts by using
delete from wp_posts where post_status='trash' but that would leave all the meta around, which can be a ton of extra data.
I would highly recommend running this script on your local development or QA site first to make sure it does what you’re expecting. You wouldn’t run random SQL on production in the middle of the day would you?