How to Effectively Bulk Delete many Posts in WordPress

I recently had put myself in a situation where I had to delete just about 10.000 posts in WordPress in a specific category.

The reason for this was the design of the community link on tripwire magazine. At the time when I created it I thought it was an OK solution to add posts into a category only used for community submissions but as more and more people started using the feature I found that WordPress where literally becoming flooded with these posts and I spent too much time locating my own articles.

I have been motivated to change this for month but lately I decided to migrate to the Headway theme (see review here) I thought the timing was good as I didn’t want to do too much tweaking in the theme to hide the community posts in all the post listings (front page, category lists etc.). My problem was how to actually get rid of all these posts and after trying different options I ended up coding my own SQL that I share with you in this post.

Why is this even worth a post? Well I ran into a lot of requests from people trying to get this done in an efficient way but there where no solutions.

[exec]$filestr = file_get_contents(‘http://www.tripwiremagazine.com/googleadsensebelowmoretag.inc’);
echo $filestr;[/exec]
I considered and experimented with different options. The most obvious way to delete posts in WordPress is of cause to use the administration area but if you have many posts to delete if may take many hour. In my case 10.000 posts seamed to be overwhelming (close to 670 deletes of the 15 posts showed on one page in WordPress admin).

Bulk Delete WordPress Plugin

I decided to look for other and much easier solutions and I ran into Bulk Delete. Bulk Delete is a WordPress Plugin which can be used to delete posts in bulk from selected categories or tags. This Plugin can also delete all drafts, post revisions or pages.

So when there’s a plugin out there that seams to do the trick why didn’t I use it? Basically because I experienced some problems with it and never got it to work on my production site. The plugin rely (as far as I know) on the same functions that WordPress use when you delete a post through the admin. This sounds great right but it will put a lot of load on your server to process 10.000 posts and I ran into problems with memory and timeouts when I tested it.

SQL specifically designed to delete all posts in a WordPress Category.

OK so I had to look for other options. I normally find what I need for WordPress after a few minutes of searching Google but this time no solutions came up. I then decided to write a SQL script specifically targeted at deleting all posts in a Category in one efficient execution. I worked as a developer years ago but still it wasn’t that easy for me to crack the problem and I guess other can benefit from this.

WARNING USE THIS SCRIPT WITH CAUSION AND ONLY AFTER BACKING UP YOUR DATABASE! USE AT YOUR OWN RISK.

How to use it:

Step 1:

Log into your phpMyAdmin and locate your WordPress database

Step 2:

Press the SQL tab and fire off this SQL to make sure the right posts are selected. You need to add the target Category Id in stead of <category id> in the script. The script should select all the meta data WordPress collects for a post. You can check out the database ER diagram here that I used.

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 =<category id>

OK so if you’re OK with the posts being selected you fire off this script. It deleted my 10.000 posts in less than a second. But be careful if you choose the wrong category you will not be able to get your posts back unless you restore from backup! The script will clean up all the metadata in the tables: wp_term_relationships, wp_postmeta, wp_term_taxonomy, wp_terms where I had hundreds of thousands of records. It is quite important to get this cleaned out

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 =<category id>

Hope you will find this guide and the script useful and please leave a comment if you have any suggestions or comments on how to improve it.

Lars

Lars started tripwire magazine back in January 2009. He is really passionate about web design, web development and loves to look into new technologies, techniques, tools etc. and to write articles for his readers.