« Mail on the fly (usin… | Home | Sick Leave after surg… »

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")

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()

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;
end #

otto commenti

Uno o più commenti sono in attesa di approvazione.

(optional field)
(optional field)

In questo sito si attua la moderazione dei commenti. Questo significa che il tuo commento non è visibile finché non viene approvato da un moderatore.

Mantenere le informazioni personali?
Nota: Tutti i tag HTML eccetto <b> e <i> saranno rimossi dal commento. Puoi inserire collegamenti semplicemente scrivendo un URL o un indirizzo e-mail.