Support » Fixing WordPress » Need help deleting tens of thousands of posts!

  • Hey all —

    So I have been blogging for many years, but then over time used Twitter as a sort of small, public journal. I installed a plugin that made my tweets post to my self-hosted WordPress blog, and it worked great!

    Suddenly, over two months, (really, 2 days that crossed into two months it seems like) the twitter plugin copied and re-posted as many as 10 tweets per my ONE tweet. It also went back in time a bit and posted multiple copies of OLD tweets! Agh!

    So, I have 2 months in my blog that are filled with tens of thousands of posts. I have tried a million ways to delete the posts, from the wordpress control panel to a plugin that bulk-deletes, but all of them time out. I tried reducing the number of posts to delete down to 100, and it still times out.

    Any ideas how I can delete these posts? Could I delete something in the WordPress install folder that will get rid of them?

    Thanks a ton for any help!

    Beau

    The page I need help with: [log in to see the link]

Viewing 14 replies - 1 through 14 (of 14 total)
  • Moderator Steven Stern (sterndata)

    (@sterndata)

    Forum Moderator & Support Team Rep

    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.

    Thread Starter BeauHindman

    (@beauhindman)

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

    Beau

    Moderator Steven Stern (sterndata)

    (@sterndata)

    Forum Moderator & Support Team Rep

    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.

    Thread Starter BeauHindman

    (@beauhindman)

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

    Beau

    Thread Starter BeauHindman

    (@beauhindman)

    @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: http://epicdolls.com/beauturkey/2016/10/31/16245/

    Thanks again for any help.

    Beau

    • This reply was modified 3 years, 7 months ago by BeauHindman.
    Moderator Steven Stern (sterndata)

    (@sterndata)

    Forum Moderator & Support Team Rep

    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;

    Thread Starter BeauHindman

    (@beauhindman)

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

    Beau

    Thread Starter BeauHindman

    (@beauhindman)

    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

    Moderator Steven Stern (sterndata)

    (@sterndata)

    Forum Moderator & Support Team Rep

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

    Thread Starter BeauHindman

    (@beauhindman)

    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

    Moderator Steven Stern (sterndata)

    (@sterndata)

    Forum Moderator & Support Team Rep

    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);

    Thread Starter BeauHindman

    (@beauhindman)

    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

    Thread Starter BeauHindman

    (@beauhindman)

    Doh! Think I got it.

    Thanks a ton, again. You deserve a raise 🙂

    Beau

Viewing 14 replies - 1 through 14 (of 14 total)
  • The topic ‘Need help deleting tens of thousands of posts!’ is closed to new replies.