(modifié le 17 avril 2022 à 1:25)

Je me suis rendu compte qu'un de mes plugins WordPress était la cause d'une table plusieurs dizaines de mégaoctets.

Voici comment j'ai résolu ce problème : en créant un déclencheur (trigger

Analyse du problème

Lorsqu'une personne ajoute un commentaire en bas d'un article il est possible de remplir son nom/pseudo, email et site/blog. Il y a quelques années j'ai installé le plugin "Twitterlink Comments" qui ajoute un champ pour le pseudo Twitter.

Ce plugin crée une nouvelle table "wp_wptwitipid" (wp étant le préfixe par défaut) pour faire le lien entre le commentaire stocké dans la table wp_comments et l'email.

 

Problème : je reçois plusieurs dizaines de spams chaque jours, que vous ne voyez pas grâce à l'antispam. Mais ces spams restent stockés dans la base de données MySQL et sont tagués en tant que "spam" donc WordPress ne les affiche pas. Quand je purge les spams des commentaires cela ne purge pas la table wp_wptwitipid qui contient elle aussi une entrée (parce que oui, les spammeurs remplissent tous les champs).

Je me suis donc retrouvé avec pas loin de 100K enregistrements orphelins et inutiles en base, nickel 👍

Je ne l'ai pas précisé mais je me suis rendu compte tout à fait par hasard de la taille de cette table dans PhpMyAdmin, suite à un souci de backup... comme quoi 🙂

Identification des données à purger

Une requête avec jointure sur l'email pour localiser les données qui sont présentes uniquement dans une seule des 2 tables :

SELECT email FROM `wp_wptwitipid` 
LEFT JOIN `wp_comments` ON wp_comments.comment_author_email = wp_wptwitipid.email
WHERE wp_comments.comment_author_email IS NULL

Lors de la comparaison si le champ email est vide dans la table wp_comments alors c'est qu'il existe que dans la table du plugin (wp_wptwitipid).

Purge définitive des données

Avant cette étape j'ai bien réaliser une sauvegarde complète de la base de données mais aussi des 2 tables concernées pour ne restaurer que ces 2 là en cas plus rapidement si besoin.

Pour la suppression la syntaxe devient :

DELETE A 
FROM `wp_wptwitipid` A
LEFT JOIN `wp_comments` B ON B.comment_author_email = A.email
WHERE B.comment_author_email IS NULL

Avec les lettres A et B représentant les tables :

• A=wp_wptwitipid
• B=wp_comments

Et boom : tous les enregistrements inutiles sont supprimés. J'ai lancé une optimisation de la table derrière vu le volume de données supprimé.

Automatiser la suppression des données

Comme je n'ai pas envie de réitérer l'opération régulièrement j'aimerai que la purge soit réalisée à chaque suppression d'un commentaire WordPress.

Pour ça rien de plus simple il faut créer un déclencheur (trigger). Je dis simple mais en réalité c'est la première fois que je m'y intéresse, et c'est en réalité beaucoup plus simple que ce que j'imaginais.

Depuis l'interface de PhpMyAdmin cliquez sur la table puis créer un déclencheur (en haut à droite) :

• table wp_comments
• moment : AFTER
• évènement : DELETE

Et la définition correspond à la requête de suppression précédente, rappel :

DELETE A 
FROM `wp_wptwitipid` A
LEFT JOIN `wp_comments` B ON B.comment_author_email = A.email
WHERE B.comment_author_email IS NULL

Résultat :

note: le champ créateur est facultatif, par défaut ce sera le nom de l'utilisateur MySQL

Autre possibilité si vous n'avez pas PMA pour créer directement le trigger en langage MySQL :

CREATE TRIGGER `trigger_maj_wptwitipid` AFTER DELETE ON `wp_comments`
FOR EACH ROW
DELETE A 
FROM `wp_wptwitipid` A
LEFT JOIN `wp_comments` B ON B.comment_author_email = A.email
WHERE B.comment_author_email IS NULL

Conclusion

Pour vérifier que tout fonctionne il suffit de supprimer un des commentaires depuis l'interface de WordPress. Regardez le nombre d'enregistrements dans la table "wp_wptwitipid" et si après la suppression du commentaire la valeur est décrémentée de 1 alors c'est gagné.

Je rappelle que j'ai laissé pour l'exemple le préfixe "wp_", pensez à utiliser votre prefixe si vous l'avez changé (ce qui est conseillé pour des raisons de sécurité).