MySQLの複合インデックス注意点

インデックスには、複数の列に対してまとめて設定する複合インデックスという種類がある。
検索条件に複数列を指定する場合などでは、単一カラムのインデックスよりも複合インデックスの方が高速な検索を行うことが出来る。

複合インデックスを作成するには、CREATE INDEXを使用。下記では、テーブル名tbl_nameのcol1、col2カラムに、col1and2という複合インデックスを作成する。

CREATE index_name ON tbl_name (col1, col2)

作成した複合インデックスを利用するのに特別な構文はなく、下記のようなSELECT文で複合インデックスが自動的に使われる。

SELECT * FROM tbl_name
     WHERE col1=1 AND col2=1;

複合インデックスにはいくつかの利用制限があり、最初に指定したカラムを検索対象にする場合は有効となりますが、最初に指定したカラムを検索条件に含まなかったり、順番が違っていたりする場合などは無効になる。また、カラムとカラムをORで検索する場合は複合インデックスが無効になる。

追記：

最初に指定したカラムを検索対象にする場合は有効となりますが、最初に指定したカラムを検索条件に含まなかったり、順番が違っていたりする場合などは無効になる。

どうも5.0以降のバージョンでは動作が違う気がする。

最初に指定したカラムでなくてもインデックスが使われるような。。。

下記のSELECT文では、最初と2番目のクエリがインデックスを使用できる。

# 複合インデックスが有効
... WHERE col1=1;
... WHERE col1 AND col2=1;
... WHERE col1 AND (col2=1 OR col2=2);

# 複合インデックスが無効
... WHERE col1=1 OR col2=1;
... WHERE col2=1 AND col1=1;
... WHERE col2=1;
... WHERE col2=1 AND col3=1;

col2だけの検索もインデックスを利用したい場合は、col2のインデックスを別に作成する事。