MySQLでUpdateが遅いときはIndexを見直そう

ほぼ自分用のメモ。

ただいま、php+MySQLで試験の答案をブラウザで入力して採点するWebシステムを作成中です。

テーブル構造は次の通り（実際には英語のテーブル名とフィールド名です）
[答案テーブル]

UserID

試験No

問番号

日付

回答

正解

○×

現在６０００件ぐらいのデータ。今後データは受験人数×試験回数×80で増え続ける。

[正解テーブル]

試験No

問番号

正解

現在７０００件ぐらいのデータ。今後徐々に増えるが劇的には増えない。

答案テーブルの回答に、各自の答え（ア〜エ）を記録。
正解テーブルの正解と突き合わせて、そのまま答案テーブルの正解に書き込み、○×を1か0で書き込みます。

こんなSQLを発行してみました。
２つのテーブルをjoinで繋いで、答案テーブルの正解と○×にデータを付与しています。

UPDATE 答案テーブル  as X join 正解テーブル as  Y on X.試験No=Y.試験No and X.問番号=Y.問番号 
      SET X.正解=Y.正解, ○× = IF(X.正解=Y.正解,1,0);

ところが、これを実行すると１分を超える処理時間が必要でした。これから、答案テーブルはどんどんデータが増えるというのに。

UPDATEよりINSERTした方が速いらしいということで、どこかで見つけてこんなSQLも試してみました。
テーブルを複製して、INSERTでデータを突っ込み、複製したテーブルを削除します。

CREATE TABLE IF NOT EXISTS 答案テーブル_bk as SELECT * FROM 答案テーブル;
TRUNCATE TABLE 答案テーブル;
INSERT INTO 答案テーブル
     SELECT X.User,X.試験No,X.問番号,X.日付,X.回答,Y.正解, IF(X.回答=Y.正解,1,0) as ○× 
          from 答案テーブル_bk as X JOIN 正解テーブル as Y on X.試験No=Y.試験No and X.問番号=Y.問番号;
DROP TABLE 答案テーブル_bk;

確かに効果はあって、15秒ぐらいで終了します。
しかし、ロック機能を実装してみたけどうまく動かず、タイミングによってはデータ消失という致命的な問題が残りました。

さらに調査を進めると、INDEXを付与するのも効果ありとのこと。
あれ？でも、主キー設定してるはずだなぁ…。

…設定されてない orz

設定し忘れてたようだ。
早速主キーの設定を行い（試験No,問番）、最初に作ったSQLで実行してみると。
0.1秒で処理終了。

ここまでの苦闘はなんだったんだ。これならなんの問題も無いな。主キーに出来ないフィールドでもIndexを設定すると効果あるはず。

これからデータ件数が増えた時に、どの程度速度が落ちるかは様子を見ながら考えよう。

以上、パフォーマンスの検討はindexの確認をしてからにしましょう、という話でした。