CODEWITHSUNDEEP
mysqlsqlwordpressphpmyadmin
Debajyoti Das
Debajyoti Das

Reputation: 2148

Delete Comments / Content with Less than 5 words

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.

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 ?

Upvotes: 1

Views: 222

Answers (5)

Nono
Nono

Reputation: 7302

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

Upvotes: 1

echo_Me
echo_Me

Reputation: 37233

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

Upvotes: 1

Dmitrii Bychenko
Dmitrii Bychenko

Reputation: 186748

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,}')

Upvotes: 1

Strawberry
Strawberry

Reputation: 33945

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

Upvotes: 1

domdomcodecode
domdomcodecode

Reputation: 2453

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.

Upvotes: 1

Related Questions