読者です 読者をやめる 読者になる 読者になる

ひっきぃのメモ帳

趣味で作るプラモデル製作の過程と作品、日々の資格取得へ向けての活動、Apple中心のIT関連ネタを書いています。

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

ほぼ自分用のメモ。


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


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

UserID 試験No 問番号 日付 回答 正解 ○×

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


[正解テーブル]

試験No 問番号 正解

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


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


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

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

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


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の確認をしてからにしましょう、という話でした。