郵便番号データベースにindexを付けても検索が遅い理由

2010年7月26日 14:30

MySQLで実験していて気づいた罠ですが、郵便番号データベースを自前で持っている場合に知っておくと良いTIPSです。

ある時、郵便番号を検索するのにこういうクエリを発行していました。

SELECT zip from postal where zip=1560051;

結果はこれ。

構造はこうなってます。
この画像では見えませんが「zip」フィールドにindexを張っています。

なーんか、遅い。　他のテーブルとの連携で複数データを参照なんかすると激重。

「なんでだ？」と思い、EXPLAIN句を付けてSQLを発行してみると...

EXPLAIN SELECT zip from postal where zip=1560051;

116.197.143.97-8443 - localhost - - - phpMyAdmin 2.8.2.4_1280122707858.png

ありゃ？　全データを参照してる。　他のテーブルと組み合わせるとindexも効かずに「type」列が「ALL」になっちゃってりしてる！

データ型が文字列の場合は...

よく見るとzipのデータ型がVARCHAR(7)になっていたことに気付きました。
文字列でしたね。　この場合は、

SELECT zip from postal where zip='1560051';

このようにシングルクォーテーションで括ってあげるとindexの効果が発揮されます。

おっ！早くなった！　てことで終了です。

MySQLを入門書などで学んで使っていると、ついついデータ型に見合わない一方向な検索クエリを作成してしまうことがある。

「なにか動作が遅いな？」と感じたら放置せず、「EXPLAIN」句を利用してテストクエリーを発行し、ちゃんとindexが利用されているか、一時的なテーブルコピーなどでスロークエリーになっていないかどうかを確認しよう。