How to bulk delete posts in WordPress with MySQL

Usually deleting several posts at once is not a problem thanks to the bulk delete options in WordPress. Those queries however rely on a single delete each, initiated by PHP loop. That’s fine if you’re deleting up to about 100 posts at a time.

But it’s not when you have thousands of posts to delete. I’ve come across installations with hundreds of thousands of posts which have often been created automatically – and there comes the time when you need to clean things up and prune that massive database.

Deleting 100.000 posts is impossible from within WordPress: it puts a huge load on your server, it takes forever, and besides WordPress will time out after about 200 posts.

The solution: a dedicated SQL query.

Sounds scary I know – let’s go through it step by step in this article.

What do we need

Since we won’t be using the WordPress back end to do this, the most convenient option is to use phpMyAdmin to query your database. Your host will provide you with details on how to get access to it.

Alternatively, you can use other MySQL clients such as the amazing Sequel Pro, or just access MySQL from the command line. Whatever floats your boat really, the syntax is the same – it’s just where and how you get to executing it is slightly different in each client.

I’m using phpMyAdmin in this example. I’m also assuming that you have one category that contains the posts you want to delete.

Let’s get started

Before you do anything, make sure you BACKUP YOUR DATABASE. There’s no undo command in MySQL, so whatever you’re doing is final! Remember: rehearsals are what make a good production.

Find out your Category ID

Before we begin working with phpMyAdmin we need to first find the numeric category ID from which we’d like to delete our posts. The easiest way to do this via your WordPress back end:

• heading over to Posts – Categories
• hover over the category in question
• observe your bottom browser bar which will display a link which contains your category ID

In my example I’m using a category called “test”, and my link looks like this:

http://mydomain.com/wp-admin/edit-tags.php?action=edit&taxonomy=category&tag_ID=2&post_type=post

See where it says &tag_ID=2?

2 is my category ID. Yours will be different, remember that number.

Making the selection

Now login to phpMyAdmin and select your database from the list on the left. You may only have one to choose from, and it may already be pre-selected. Mine is called “podcast”.

You should see something like this:

Head over to the option at the top that reads “Query” which lets you execute raw SQL code. We’re going to tell MySQL that we want to bring up all posts that match our search critera, ie everything from our category ID.

Paste the following code into the box:

SELECT *
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 ( d.term_taxonomy_id = b.term_taxonomy_id )
LEFT JOIN wp_terms e ON ( e.term_id = d.term_id )
WHERE e.term_id = 2

The last line has my category ID (2) on it. Obviously amend it with your own.

Hit “Submit Query” and be presented with a list of tables. This can take a moment or two depending on how many posts you have. I only have a few here, but you’ll see something like this:

This was a test run. Now we’ll execute another query, this time deleting our posts instead of just selecting them. It’s more or less the same as above, the only difference being the first line. Remember there’s no UNDO option here – you have been warned!

Click on the SQL option at the top and paste the following code in the box (don’t forget to change your category ID – it’s the last line, replace the X with your numeric ID):

delete a,b,c,d
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 ( d.term_taxonomy_id = b.term_taxonomy_id )
LEFT JOIN wp_terms e ON ( e.term_id = d.term_id )
WHERE e.term_id = X

As soon as you hit “Go”, MySQL starts deleting posts. phpMyAdmin may or may not come back with a “success” message here depending on your version. The SQL box looks a bit different the second time round (don’t ask me why – but here’s a screenshot):

To check if it has worked, either look at your list of posts in WordPress or execute the first query again – if all went well, no posts should be returned.

As I said, the code works just as well with Sequel Pro. I tested this on a database with just over 100.000 posts, 98.000 of which needed to be deleted. It took less than a minute to do this.

Thanks go to Tripwire Magazine for the only solution on the web I could find on this.