I found a lot (A LOT) of spam comments on WordPress recently and decided to get rid of them easily.
Solution 1: from the WP backend
This solution is not reasonable because at the moment any bulk operation on the Db from the application generally cause a timeout. Akismet itself (the plugin which choose what is spam and what is not) has no such feature and installing SOMETHING to REMOVE other stuff is quite crazy imho.
So I decided to create a procedure on Database side, checking also the MySQL performance against such an operation.
First cold attempt: find what is Spam for Akistmet:
Table wp_commentmeta
Filter: meta_key = ‘akismet_result’ and meta_value = ‘true’
As you know something like “delete from `wp_commentmeta`where comment_id in (SELECT comment_id FROM `wp_commentmeta` WHERE meta_key = ‘akismet_result’ and meta_value = ‘true’) limit 1000” cannot be performed because MySql should operate on a table already analyzes.
So first save the comment ids in another table
create table to_delete as SELECT comment_id FROM `wp_commentmeta` WHERE meta_key = ‘akismet_result’ and meta_value = ‘true’
The you should delete this using an inner join or a in like
DELETE FROM `wp_commentmeta` WHERE comment_id IN (SELECT comment_id FROM `to_delete`
) LIMIT 10000
The magic limit value is a good parameter to check performance. And my DB at the moment is really bad on performance and unpredictable (the db is online and in “production”)
- delete 1000 3.5 sec
- delete 10000 16 sec
- delete 10000 29 sec
So to complete, first attempt to create a stored procedure in order to do all the job:
drop procedure if exists remove_spam_comments;
delimiter #
create procedure remove_spam_comments()
begin
declare v_max int unsigned default 1000;
declare v_counter int unsigned default 0;
start transaction;
while v_counter < v_max do
DELETE FROM `wp_commentmeta` WHERE comment_id IN (SELECT comment_id FROM `to_delete`) LIMIT 10;
set v_counter=v_counter+1;
end while;
commit;
end #