MYSQL Query to Bulk Remove Posts From a Category

Over on my personal blog, www.RVoodoo.com I ran into quite a problem. I use feedwordpress to bring in Colorado Avalanche news as a Custom Post Type for people to keep up on the only hockey news that really matters. Something went wrong and I had over 25000 of the same post show up. Have you ever tried to delete 25000 posts? It’s nearly impossible to fix. Let’s take a look at what I tried and ultimately how I was able to fix that!

I knew in the first place that bringing in the rss feed posts was going to bring a lot of posts over. I was only interested in recent posts. No reason to keep thousands of posts alive on the site that nobody was going to read. So I used auto prune posts to keep things in check. It’s good for deleting posts automatically after a set time period from your choice of post type and category. It normally works great. The main problem is that when 25000 posts show up all at once, it chokes. I believe it uses the built in WordPress delete feature.

My experience is that anything deleting this many posts through the admin interface will not work. WP by default shows 15 posts on a screen. Imaging trying to delete 25000 posts 15 at a time! Well, you can go to screen options and extend the number of posts shown at one time. So I bumped it up to 100. But then every time I tried to move 100 posts to the trash, and then empty the trash, I would bring my whole site down with memory errors. Mind you I have my memory limits configured pretty high on my server, but it still didn’t work.

I couldn’t get the posts auto deleted with auto prune posts, so I tried the bulk delete plugin, same problem. It choked on that many posts. Nothing was working. It took a pretty strong dose of Google-fu to finally come across this post from Lars over on Tripwire Magazine. This was exactly what I needed, and it worked perfectly. I’ll give you the query here for information, but I recommend you head over and read the source article for all the nitty gritty.

I logged on to my server and headed into phpmyadmin. First thing I did is made a backup. I can’t stress that enough…. DO NOT MESS AROUND IN YOUR DATABASE WITHOUT MAKING A BACKUP. I am on godaddy, where I have the option of a one click backup. So I did that. But that’s not all. I also went into the export tab on phpmyadmin and downloaded a local export of my database. I lost my database once before, I have learned my lesson!

Now that I had a backup, I ran this query from the SQL tab.

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 =464

This first query is a safe one to run. We aren’t doing anything yet. This is going to bring up a list of all the posts associated with Category ID 464. That was my Colorado Avalanche category ID. You would need to swap that out for your category ID of course. I ran that query and it broght up all 25000+ posts, so I was able to see what posts were going to be affected by the next query. So here’s the big one. Run this once you are absolutely certain you have a viable database backup, and your previous query brought up the posts you want gone:

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 =464

Again, the 464 on the last line is the ID of my Colorado Avalanche category. You will need to swap that out for yours. But that’s it, I ran that query, it took 20 seconds, and everything was done. All the offending posts were gone, and my site was running smooth. I have run through my site and there are no ill effects. Nothing else is gone, nothing strange has happened.

So hopefully this helps you out. And thanks to Lars on Tripwire Magazine for the huge help!

6 Replies to “MYSQL Query to Bulk Remove Posts From a Category”

  1. Thank you for this code as I have been searching for weeks for a code that worked since I know very little about mysql. I need one added field and I can figure it out and would appreciate if you could assist. I need to delete all posts from a specific category that are older than 45 days. Thanks so much in advance!

    1. I know very little about mysql….. but I might be able to come up with that information. If this is something you do often, there are also plugins which auto delete posts from set categories after a certain time period. I’ve used one on my personal site for a while.

  2. I’ve tried auto delete, auto prune and bulk delete, but the use too many resourses and crash the site. That’s why I’m trying to do it through the backend. Thanks!

    1. Well….. I’m not totally sure if this will work, I don’t have access to my phpmyadmin right now….. but it might work to just tack on to the WHERE statement….

      WHERE e.term_id =464 AND post_date < '2010-01-01';
      

      MAybe you can try running it on the top SELECT example and see what you get?

Leave a Reply