Massive Spam Delete on WordPress

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 #