In some of WordPress based blogs, I get more than 10000 (10k) comments, which all of them spams. Using WordPress UI to delete comment is not an easy task, because we’re limited to delete 20 comment per actions. Instead of installing random plugin, let’s do it via SQL (MariaDB/MySQL) query.
Inside the WordPress database it using wp-comments table to store all comments, to make the exact table check wp-config.php
$table_prefix = 'atetux_';
if we define the $table_prefix the table name will become atetux_comments.
Login to MySQL server
mysql -h SERVER-IP -u USER -p DATABASE-NAME
for example
mysql -h sql.atetux.com -u atetux -p atetux
Check the table first, to make sure it exists, using desc wp_comments
MariaDB [atetux]> desc wp_comments; # or MariaDB [atetux]> desc atetux_comments;
sample output from command above
MariaDB [atetux]> desc wp_comments; +----------------------+---------------------+------+-----+---------------------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------------------+---------------------+------+-----+---------------------+----------------+ | comment_ID | bigint(20) unsigned | NO | PRI | NULL | auto_increment | | comment_post_ID | bigint(20) unsigned | NO | MUL | 0 | | | comment_author | tinytext | NO | | NULL | | | comment_author_email | varchar(100) | NO | MUL | | | | comment_author_url | varchar(200) | NO | | | | | comment_author_IP | varchar(100) | NO | | | | | comment_date | datetime | NO | | 0000-00-00 00:00:00 | | | comment_date_gmt | datetime | NO | MUL | 0000-00-00 00:00:00 | | | comment_content | text | NO | | NULL | | | comment_karma | int(11) | NO | | 0 | | | comment_approved | varchar(20) | NO | MUL | 1 | | | comment_agent | varchar(255) | NO | | | | | comment_type | varchar(20) | NO | | comment | | | comment_parent | bigint(20) unsigned | NO | MUL | 0 | | | user_id | bigint(20) unsigned | NO | | 0 | | +----------------------+---------------------+------+-----+---------------------+----------------+ 15 rows in set (0.004 sec)
Get the total pending comments using SQL query
SELECT COUNT(*) FROM wp_comments WHERE comment_approved = '0';
it should be equal to the Pending comments from https://DOMAIN/wp-admin/edit-comments.php
Delete all pending comments
DELETE FROM wp_comments WHERE comment_approved = '0'; # OUTPUT Query OK, 10000 rows affected (0.090 sec)
after cleanup the comment exit from MySQL CLI
exit;