ほぼ自分用のメモ。
ただいま、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の確認をしてからにしましょう、という話でした。