ひょんなことからWordPressで運用されてるあるメディアに関わることになりました.
サイトがだいぶ重たいので原因を探ることになったのですが,結論から言うとプラグインによるSQLが原因の一つでした.

かなりヤバめなSQLが二つほどあったので,紹介したいと思います.(半分は愚痴です
アンチパターンとして参考にしてください.

インデックスが全く貼られてない

そのメディアはクイズプラグインが導入されており,ユーザーは記事内でクイズに回答することができます.
ユーザーのクイズの回答結果を保存しているテーブルがあるのですが,そのテーブルに全くインデックスが張られていませんでした.
にも関わらず,クイズごとのユーザーの回答数を度々カウントするので,毎回全走査が起こります.

SELECT COUNT(*) FROM `table` WHERE quizId = 1234

ちなみにこのテーブルは1000万レコードを超えており,このクエリは3~5秒ほどかかっていました.
しかも頻繁にこのようなクエリが走っており,CPUを食いつぶしていました.

対策

単純にquizId カラムにインデックスを貼りましょう.1000万全走査に比べたら爆速です.

offsetをループで回している

SHOW processlist を実行していると,現在走っているクエリが確認できます.

SELECT * FROM `table` LIMIT 3010000, 10000
SELECT * FROM `table` LIMIT 3020000, 10000
SELECT * FROM `table` LIMIT 3030000, 10000
...

このようなクエリが度々走っているのを目にしました.(嫌な予感しかしません)

そして,次のようなソースコードを見つけました.

$rowcount = $wpdb->get_var( "SELECT count(*) FROM `table`" );
$offset = 0;

while( $rowcount > 0 ) {

  $result = $wpdb->get_results( "SELECT * FROM `table` LIMIT {$offset}, 10000" );

  ...処理

  $rowcount -= 10000;
  $offset += 10000;
}

offset 付きの LIMIT 句は offset 分を結局走査してしまうので,上のようなコードでは,ループ1回ごとに,最初から走査をしてしまいます.
ちなみにこのテーブルも1000万件ほどあるので,ループの最後の方は,1000万件ほど全走査をしてしまいます.
人類に のアルゴリズムは無理です.

対策

ケースバイケースですが,例えば次のように対策することができるでしょう.

SELECT * FROM `table` WHERE ID >= {$offset} LIMIT 10000

主キーで限定すれば全走査する必要がないので爆速です.

まとめ

WordPressはカジュアルにプラグインを入れられる一方で,質が低いと言わざるを得ないプラグインもいっぱいあります.
ただ非エンジニアの方でもプラグインを入れられることを前提としているので,見極めやチューニングは難しいかもです.

とはいえ,有料のプラグインでこれか…という気持ち.

プラグインを悪用してビットコインを掘るみたいなことも十分あり得そうなんで,
野良のプラグインを入れるときには充分に注意を.