Delete Comments / Content with Less than 5 words

1 Views

I have lots of useless approved comments on my site. One way I use of removing them is 

DELETE FROM wp_comments WHERE comment_content LIKE '%agree%with%you,%thanks%, '

However, this is removing good comments as well and leaving out a lot of bad comments.

Read More

How Do I modify the query to delete comments with less that 5 words.

Due to the large number of comments in the actual database, I am worried whether to run a command with like since it will scan more than 20K rows. Is there any way to reduce the load ?

Post Views: 1

Related posts

Leave a Reply

5 comments

  1. try using LENGTH . bellow you will delete entries which have less then 35 characters. Ithink its better then using words.

    length(comment_content) < 35 --//change length number as you want //35 characters

    like that:

      DELETE FROM wp_comments WHERE comment_content LIKE '%agree%with%you,%thanks%, '
                           AND   length(comment_content) < 35

    DEMO HERE

  2. You can try counting the characters, replace all the spaces with empty '' and subtracting them. You’ll need CHAR_LENGTH() and REPLACE.

    DELETE FROM wp_comments
WHERE (CHAR_LENGTH(comment_content) - CHAR_LENGTH(REPLACE(comment_content,' ',''))+1) < 5;

    The plus 1 is an adjustment since you’ll have 1 less space than words.

    Note: CHAR_LENGTH() for mysql, you could use LENGTH() for others.

  3. E.g.:

    SELECT LENGTH('this is a test')x,LENGTH(REPLACE('this is a test',' ',''))y;
+----+----+
| x  | y  |
+----+----+
| 14 | 11 |
+----+----+

14-11 = 3
3+1 = 4

This sentence has 4 words

  4. You can try using regular expressions, something like that:

    delete 
  from wp_comments
 where not (MyComment REGEXP '[A-Za-z]+([[:space:]]+[A-Za-z]+){4,}')

  5. The most effective way is:

    MySQL Query Start:

    -- Select All Rows

    mysql> SELECT * FROM comments;
+----+---------------------------------------------------------------------------------------------------------------+---------------------+
| id | comments                                                                                                      | log_time            |
+----+---------------------------------------------------------------------------------------------------------------+---------------------+
|  1 | This Comment has 4 space                                                                                      | 2014-03-20 16:05:33 |
|  2 | Lorem ipsum dolor sit amet.                                                                                   | 2014-03-20 16:08:12 |
|  3 | Lorem ipsum dolor sit amet, consectetur adipisicing elit. Laborum molest                                      | 2014-03-20 16:08:12 |
|  4 | Lorem ipsum dolor sit amet, consectetur adipisicing elit. Ipsa, eum, fuga dolorum cupiditate blanditiis enim  | 2014-03-20 16:08:29 |
|  5 | Lorem ipsum dolor sit amet, consectetur adipisicing elit. Magnam                                              | 2014-03-20 16:08:29 |
|  6 | Lorem ipsum dolor sit amet.                                                                                   | 2014-03-20 16:09:09 |
|  7 | Lorem ipsum dolor sit amet.                                                                                   | 2014-03-20 16:09:16 |
|  8 | Lorem ipsum dolor sit amet.                                                                                   | 2014-03-20 16:09:18 |
+----+---------------------------------------------------------------------------------------------------------------+---------------------+
8 rows in set (0.00 sec)

    -- Check Space Count 

    mysql> SELECT comments, (
    -> length( trim( comments ) ) - length( replace( trim( comments ) , ' ', '' ) )
    -> ) AS total_space
    -> FROM comments
    -> LIMIT 0 , 30;
+---------------------------------------------------------------------------------------------------------------+-------------+
| comments                                                                                                      | total_space |
+---------------------------------------------------------------------------------------------------------------+-------------+
| This Comment has 4 space                                                                                      |           4 |
| Lorem ipsum dolor sit amet.                                                                                   |           4 |
| Lorem ipsum dolor sit amet, consectetur adipisicing elit. Laborum molest                                      |           9 |
| Lorem ipsum dolor sit amet, consectetur adipisicing elit. Ipsa, eum, fuga dolorum cupiditate blanditiis enim  |          14 |
| Lorem ipsum dolor sit amet, consectetur adipisicing elit. Magnam                                              |           8 |
| Lorem ipsum dolor sit amet.                                                                                   |           4 |
| Lorem ipsum dolor sit amet.                                                                                   |           4 |
| Lorem ipsum dolor sit amet.                                                                                   |           4 |
+---------------------------------------------------------------------------------------------------------------+-------------+
8 rows in set (0.00 sec)

    -- Delete Those Records who Has less than 5 words

    mysql> DELETE FROM comments WHERE (
    -> length( trim( comments ) ) - length( replace( trim( comments ) , ' ', '' ) )
    -> ) < 5;
Query OK, 5 rows affected (0.16 sec)

    -- Select All Rows Again to Verify Rows

    mysql> SELECT * FROM comments;
+----+---------------------------------------------------------------------------------------------------------------+---------------------+
| id | comments                                                                                                      | log_time            |
+----+---------------------------------------------------------------------------------------------------------------+---------------------+
|  3 | Lorem ipsum dolor sit amet, consectetur adipisicing elit. Laborum molest                                      | 2014-03-20 16:08:12 |
|  4 | Lorem ipsum dolor sit amet, consectetur adipisicing elit. Ipsa, eum, fuga dolorum cupiditate blanditiis enim  | 2014-03-20 16:08:29 |
|  5 | Lorem ipsum dolor sit amet, consectetur adipisicing elit. Magnam                                              | 2014-03-20 16:08:29 |
+----+---------------------------------------------------------------------------------------------------------------+---------------------+
3 rows in set (0.00 sec)

    In your case you can use like this:

    -- Check Space Count
SELECT comment_content, (
length( trim( comment_content ) ) - length( replace( trim( comment_content ) , ' ', '' ) )
) AS total_space
FROM wp_comments
LIMIT 0 , 30;

-- Delete those comments who has less than 5 words
DELETE FROM wp_comments WHERE (
length( trim( comment_content ) ) - length( replace( trim( comment_content ) , ' ', '' ) )
) < 5;

    -- Live DEMO

    Click Here to see Live DEMO