Support » Fixing WordPress » SQL command to delete ‘some’ comments

SQL command to delete ‘some’ comments

  • RobotFX

    (@wattaman)


    2 years, 6 months ago

    Hi again

    Sorry to bother you with my never-ending issues, but I really need this. Basically (see the link please) there are products in my database with hundreds of comments/reviews that nobody cares about. There only consume the server’s resources. So I’m thinking that the fastest way – and might be the only since I couldn’t find a plugin or function to do what I need ‘on the fly’ – is using a mysql command.

    I only know that:
    DELETE from wp_comments WHERE comment_approved = '1'
    deletes all comments. What I want is a mysql command that will:
    1. delete all comments shorter than a specific number of characters (this way I get rid of lot of nonsense)
    And then run another command that will:
    2. delete all comments, from all posts, but keep 30 per post. Preferably the newest, but that’s not so important right now, because the idea right now is to clean the database a little. It’s growing too fast.

    PS if you know of any plugin that can do it, please share. I didn’t have luck googling.

    • This topic was modified 2 years, 6 months ago by RobotFX. Reason: typo

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

Viewing 3 replies - 1 through 3 (of 3 total)
  • Dion

    (@diondesigns)

    2 years, 6 months ago

    The query for (1) is simple, but since the comment_content column is not indexed, the query could potentially take a long time to execute. Please run the following query and see how long it takes:

    SELECT * FROM wp_comments WHERE CHAR_LENGTH(comment_content) < (num)

    Replace (num) with the minimum comment length you want. If the query takes less than 10 seconds, then it is probably safe to run the delete query. Replace SELECT * with DELETE in the above query to delete the comments. Please make a copy/backup of the wp_comments table before running the delete query.

    I believe it would take a PHP script to do what you want in (2). This would be best handled by a plugin.

    RobotFX

    (@wattaman)

    2 years, 6 months ago

    @diondesigns that worked well, thanks a lot.
    I’m still searching for a way to delete the comments.

    RobotFX

    (@wattaman)

    2 years, 6 months ago

    OK, I’ve managed to delete over 100000 comments, optimize the database, remove orphans etc. The DB didn’t reduce too much, but at least I got rid of some nonsense.

    If anyone knows a way to accomplish my second issues, please share.

    2. delete all comments, from all posts, but keep 30 per post. Preferably the newest, but that’s not so important right now, because the idea right now is to clean the database.

    Thank you.

Viewing 3 replies - 1 through 3 (of 3 total)
  • The topic ‘SQL command to delete ‘some’ comments’ is closed to new replies.

Views