MySQL の最適化の初歩の初歩

自分の過去の投稿( https://hacknote.jp/archives/2464/ , https://hacknote.jp/archives/2542/ )を併せてご覧いただけると幸いです。

 users、groups というテーブルがあって、group_idが1のメンバーの名字と名前を取ってきたい場合、以下のようなSQL文を思いつくと思います。

SELECT DISTINCT first_name,last_name FROM users a,groups b WHERE a.user_id=b.user_id AND group_id=1;

 このSELECT文の先頭にEXPLAINを付けて実行した結果が以下のようになったとします。

id select_type  table  type  possible_keys     key       key_len  ref               rows  Extra
1  SIMPLE       a      ALL   PRIMARY                                                8
1  SIMPLE       b      ref   user_id,group_id  group_id  4        org001.a.user_id  1     Using WHERE

どうしてusers (a)テーブルについてはフルテーブルスキャンが必要になってしまったのでしょうか。それはusers.user_idのインデックスがないからです。

indexという単語は“索引”とも訳される通り、インデックスとはその列の内容を特定の順番に並べたものと、それが実際に存在する場所を示すようなものです。

例えば、次のような文でインデックスを作成すれば、SELECT文の実行効率は改善されます。

CREATE INDEX user_id_and_name_index ON users (user_id,first_name,last_name);
CREATE INDEX user_and_group_id_index ON groups (user_id,group_id);