If you’re like most WordPress users, you probably have a lot of content in your blog’s trashcan. Maybe you deleted a post that was embarrassing, or you realized after the fact that you shouldn’t have published it. Whatever the reason, emptying the trash can is an important part of keeping your blog clean and organized. To empty the trash can on a WordPress site using a MySQL query, follow these steps:

  1. Open your WordPress site in a web browser.
  2. In the address bar, type wp-admin and press Enter.
  3. Click the “Plugins” menu item in the left column of the admin screen and then click “Activate.”
  4. Click “MySQL” in the left column of the activation screen and then click “Activate.” (If you don’t see this screen, your MySQL installation may not be activated.)
  5. In the MySQL activation window, type these two lines into it: SELECT wp_posts.* FROM wp_posts WHERE 1; DELETE FROM wp_trash WHERE post_id IN (SELECT post_id FROM wp_posts); 6. Click OK to activate MySQL and return to the WordPress admin screen. 7. In the left column of this screen, click “Posts.” 8. In the Posts table row that appears, type this query into your browser’s address bar: SELECT post_title FROM wp_postmeta WHERE meta_key=‘title’ 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 ..

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.

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?

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?