私のサイトではフォーラムとして「BBPress」を使っているのだが、最近スパムの投稿が多くて、データベースのパフォーマンスがかなり落ちていた。定期的にスパムを削除していなかったこともあって、スパム認定されたデータがpostsテーブルに5万件以上、postmetaテーブルには34万件も溜まっていた…orz

これはイカン…と、管理画面からスパムをすべて削除を実行したところ、クエリが重すぎて500エラーになってしまった。

仕方ないので、コマンドラインからデータベースに直接ログインしてpostsテーブルのスパムは駆除した。だが、postmetaテーブルなどのリレーションテーブル系からのスパムデータ駆除が何気に面倒なことに気づいた。
先に親テーブル（postsテーブル）のスパム投稿を削除しているので、postmetaテーブルにおける駆除対象は親となる投稿IDが存在していないデータになる。通常のDELETE文の条件式で考えると、外部キーのpost_idからpostsテーブルに該当するプライマリキーがないことを確認して、なければ対象のmeta_idのデータを削除する…みたいなループ処理を作らないと…とか面倒なことを考えていたのだが、そういえばSQLにはNOT EXISTSサブクエリーという機能があったじゃないか！─とひらめいた。

ついでに今後のことも考えて、リレーションテーブル系の連結が外れているゴミデータを掃除するスクリプトを作ってみた次第。

<?php
require_once './wp-load.php';

global $wpdb;

$cleanups = [
  $wpdb->postmeta           => [ 'pk' => 'ID', 'from' => $wpdb->posts, 'fk' => 'post_id' ], 
  $wpdb->comments           => [ 'pk' => 'ID', 'from' => $wpdb->posts, 'fk' => 'comment_post_id' ], 
  $wpdb->commentmeta        => [ 'pk' => 'comment_id', 'from' => $wpdb->comments, 'fk' => 'comment_id' ], 
  $wpdb->usermeta           => [ 'pk' => 'ID', 'from' => $wpdb->users, 'fk' => 'user_id' ], 
  $wpdb->term_taxonomy      => [ 'pk' => 'term_id', 'from' => $wpdb->terms, 'fk' => 'term_id' ], 
  $wpdb->term_relationships     => [ 'pk' => 'term_taxonomy_id', 'from' => $wpdb->term_taxonomy, 'fk' => 'term_taxonomy_id' ], 
];

$messages = [];
foreach ($cleanups as $_target => $_params) {
  $_query = sprintf( ' FROM %s WHERE NOT EXISTS (SELECT %s FROM %s WHERE %s.%s = %s.%s)', 
    $_target, $_params['pk'], $_params['from'], $_params['from'], $_params['pk'], $_target, $_params['fk'] 
  );
  $_query_type = 'SELECT ' . $_params['fk'];
  $_checks = $wpdb->get_results( $_query_type . $_query );
  if (!empty($_checks)) {
    $_query_type = 'DELETE';
    $_results = $wpdb->get_results( $_query_type . $_query );
    if (empty($_results)) 
      $messages[] = sprintf( 'Removed the unnecessary %d data from "%s" table.', count($_checks), $_target );
  }
}
echo empty($messages) ? 'Removing data was not exist.' : implode("<br>\n", $messages);

このPHPスクリプトをWordPressのインストールディレクトリ直下にcleanup.phpとか名前をつけて置いて実行すると、各種リレーションテーブルにて親テーブルに連携元となるデータが存在しないデータのみをごっそり削除してくれる。

私も無事に35万件もあったpostmetaテーブルのクリーンアップができて、2700件にまで減りました（いやぁ、良かった良かった…）。

この機能、いつかCDBT V2に取り込んでおこう。

しっかし、BBPressのスパム量にはげんなりする…近く、フォーラムはWordPress.orgに集約してしまった方が無難かもしれない…。