[MySQL]INFORMATION_SCHEMAから情報を取得する場合は列をしっかり指定しよう

はい。タイトルの通りです。



phpMyAdminもどきの(再)開発みたいなことをしてるんですけど、
最近全テーブル一覧を取得するのがどんどん遅くなってきたんです。
「テーブルの量も増えてきたし、InnoDB主体になってきたのも影響しているかなー。」
なんて適当に考えてたんですけど、ちょっと業務に支障が出始めてきたので少し調べてみたら漢(奥野)氏のこんな記事が。

MySQLでINFORMATION_SCHEMAへのアクセス時に利用される最適化アルゴリズム
(http://nippondanji.blogspot.jp/2010/01/mysqlinformationschema.html)


・・・ええ、確かに今まで

SELECT *
  FROM INFORMATION_SCHEMA.TABLES;

ってやってました。※そもそもまずこんな手抜きをしてはダメです。
これをEXPLAINしてみると、

Extra:Open_full_table;Scanned all databases

先のブログ記事によれば

Scanned all databases... 全てのデータベースをスキャンする必要がある場合に表示されます。(以上引用)

これは全テーブルの一覧を取得するのでしかたないとして、

Open_full_table... .frmファイルだけでなく実際にテーブルをOpenする（MyISAMの場合は.MYDや.MYIファイルを開く）必要がある場合に表示されます。(以上引用)

とあります。

そんじゃぁってことでこれを私のアプリで必要な列だけ取得するよう、

SELECT TABLE_SCHEMA,
       TABLE_NAME,
       TABLE_TYPE,
       TABLE_COMMENT
  FROM INFORMATION_SCHEMA.TABLES;

ってしてみました。
これをEXPLAINしてみると、

Extra:Open_frm_only;Scanned all databases

同様にみてみると

Open_frm_only... .frmファイル（テーブル定義ファイル）をOpenするだけでクエリを解決できる場合に表示されます。(以上引用)

とあります。なんかこっちの方がよさげですね。

で、実際これ変えたら3～4秒程度かかっていたレスポンスが0.2秒程度まで短縮されました!
当社比15倍以上!


上記ブログのネタは恐らく奥野氏の鍵本(エキスパートのためのMySQL[運用+管理]トラブルシューティングガイド)に掲載するつもりで書かれて惜しまれながらも削られたものだろうと思われます。
ブログに残していただいて感謝申し上げたい。


INFORMATION_SCHEMAが遅いとお悩みの方がいたら是非ここを見直してみてください。