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!