Need help deleting tens of thousands of posts!

You’re going to have to drop down to the SQL query level to do this. How is your site hosted? do you have access to PHPMYADMIN in your hosting control panel?

From phpmyadmin, you’re going to have to look at the wp_posts table and determine the ID for the last good post. Once you know that, we’ll write query that will delete all newer posts.

I am hosted by Bluehost. I have a control panel, but not sure if it has PHPMYADMIN or not.

Beau

https://my.bluehost.com/hosting/help/556

I have almost the same problem with one of my sites that feeds another site. Thankfully it’s not a public site and there are no duplicate posts but it does time out.

The importer was supposed to delete posts automatically after 60 days but something went wrong or I forgot to set something properly and I lost track of things for a bit. I’ve cut the number of posts it shows to 10 from the admin panels ‘screen options’ and just when I have time I remove a set of ten posts here and there. And it will sometimes ‘quit’ anyway.

Bulk delete dies shortly after it starts.

The duplicate posts plugin helped some but that wasn’t what I really needed in this case. I thought about doing something from the database side itself but it just wasn’t that important and once the duplicates were gone there wasn’t much else to ‘trigger’ on from the database side.

I had almost the exact thing happen earlier this year with another site to where I started having issues with maintenance, automatic backups and database optimization not working.

I followed this same course.

Run the duplicate checker plugin and clean up the duplicates. That was easy enough.

I then cut the number of posts to be manually shown and selected to be deleted at one time down to a number that let the maintenance side keep running (I think 5 was the magic number for a bit… then seven then ten then fifteen and twenty…).

As you go along you’ll find you can raise the number and somewhere along the line the Bulk delete will start working for you.

A good sign you’re getting ahead of the game is when the ‘Empty Trash’ button starts actually working.

That seemed safe to me.

The duplicate content is a big problem. Having ‘singular’ content you don’t really need won’t hurt all that much unless your server chokes on the sheer numbers of it.

OK, thanks all! I will go check it out and see what I can do.

Beau

@steven –

Thanks, I can see the posts in PHPMYADMIN. I know when they started and when they stopped.

Started on Jan. 23rd, 2017 (58979 posts that month)

Ended on Feb. 1st, 2017 (142485 posts that month)

PS – ;ast good post: https://epicdolls.com/beauturkey/2016/10/31/16245/

Thanks again for any help.

Beau

• This reply was modified 6 years, 3 months ago by BeauHindman.

so something like this should work. Please verify carefully and BACKUP YOUR DATABASE FIRST! Using the SQL tab on PHPMYADMIN:

Assume low_id is the first ID you want to delete and high_id is the last.

1. Delete the postmeta for those posts:

DELETE FROM wp_postmeta WHERE post_id in (SELECT ID FROM wp_posts where ID GE low_id AND ID LE high_id);

2. Then delete the posts

DELETE FROM wp_posts where ID GE low_id AND ID LE high_id;

OK thanks a ton, Steven. Let me try this and get back to you!

Beau

Yeah sorry Steven, I am a total noob when it comes to this. I was writing in the SQL tab (after doing a backup, so don’t worry lol) :

Here’s what I am typing

Not sure what to press to “run” this. Thanks again for helping!

Beau

There’s a “go” button below the text entry area.

Steven,

Thanks, I saw that. But it’s getting an error when I run it.

I type this and then hit GO and then get the error below:

DELETE FROM wp_postmeta WHERE post_id in (SELECT ID FROM wp_posts where ID GE 24819 AND ID LE 232485)

MySQL said: Documentation

#1064 – You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘GE 24819 AND ID LE 232485)’ at line 1

(thanks for being so patient with me! lol)

Beau

That line needs a semicolor at the end. Also, try this instead:

DELETE FROM wp_postmeta WHERE post_id in (SELECT ID FROM wp_posts WHERE ID BETWEEN 24819 AND 232485);

OK I ran that and it deleted the post data, but when I run

DELETE FROM wp_posts where ID GE low_id AND ID LE high_id;

(with the IDs filled in of course)

I get

#1064 – You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘GE 24555 AND ID LE 232485)’ at line 1

Doh! Think I got it.

Thanks a ton, again. You deserve a raise ??

Beau