MySQLに関して、インデックスの機能を確認したいです

Question

###知りたいこと
インデックスについて確認させていただきたいことがございます。

仮にインデックスを次のように３つセットした場合ですが、
```
CREATE TABLE search_cache (
	ID INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	service_id TINYINT(1) UNSIGNED NOT NULL,
	q VARCHAR(100),
	color VARCHAR(10),
	updated_at DATETIME(6) NOT NULL,
	PRIMARY KEY  (ID),

	# インデックスをセット
	INDEX idx_search_cache_01(service_id),
	INDEX idx_search_cache_02(q),
	INDEX idx_search_cache_03(color)
);
```
この場合、次の`SELECT`でインデックスは効かないのではないでしょうか？
```sql
SELECT * 
FROM cache_datas 
WHERE service_id=1 AND q='りんご いちご' AND color='red'
```
###なぜこう考えたのか
インデックスは
`idx_search_cache_01` (`service_id`が対象カラム)
`idx_search_cache_02` (`q`が対象カラム )
`idx_search_cache_03` (`color`が対象カラム)
という３つでセットしています。

`WHERE`の最初に`service_id`を指定した場合、その時点でまず、検索対象データは`idx_search_cache_01`となりますよね。

この`idx_search_cache_01`に対しさらに`AND q='りんご いちご'`と指定していますが、`idx_search_cache_01`のインデックスの中には`q`に対するインデックスはありませんから、`idx_search_cache_01`の中では`q`の並び順は不明瞭ですよね。

そうであれば、`q`に対してセットした`idx_search_cache_02`は、上記`SELECT`のように

一番目に`idx_search_cache_01`を指定した`SELECT`
（つまり一番目に`WHERE service_id=1`と指定した`SELECT`)

では効かないのではないかと考えたのですが…


###対策
上記の考え方があっていれば、ひょっとして、複合インデックスというのはこのための機能なのでしょうか？

つまりインデックスは3つでなく次のように1つに複合して、
`INDEX idx_search_cache_01(service_id,q,color)`
こうすることではじめて、上記`SELECT`でもインデックスが効くという仕組みですか？

###さらなる疑問
上記の考え方と対策が正しい場合ですが、そうすると検索のパターンが複数ある場合、そのパターンだけ複合インデックスを貼るということになるのでしょうか？

たとえば`SELECT`したいパターンとして次の【A】と【B】２つがあった場合
```sql
# パターン【A】
SELECT * 
FROM search_cache
WHERE service_id=1 AND q='りんご いちご' AND color='red'

# パターン【B】
SELECT * 
FROM search_cache
WHERE service_id=1 AND color='red'
```
この場合は複合インデックスも次のように貼るということになるのでしょうか？
【A】のためのインデックス → `INDEX idx_search_cache_01(service_id,q,color)`
【B】のためのインデックス → `INDEX idx_search_cache_02(service_id,color)`

Accepted Answer

FROM句の一つのテーブルに対して使用されるインデックスは一つです。
また、インデックスを使用するかどうかは、統計情報を元にオプチマイザーが判断します。

マルチカラムインデックスで項目が全て揃っているからと言って使用されるわけでもありません。
項目の並びが逆の場合に非効率と判断されて使用されない事もあります。

質問の例で使用されないのは、総じて件数が少ないから、全表検索が効率的とオプチマイザーが判断したのでしょう。

> 検索のパターンが複数ある場合、そのパターンだけ複合インデックスを貼るということになるのでしょうか？

SQLが違うなら、当然適切なインデックスも変わります。
但し、「大は小を兼ねる」事が出来ますので、兼用できるようにインデックスを構成します。
項目の並びは、カーディナリティの低いものから並べるのがコツです。

Answer

３カラムにまたいだ検索が前提なら、３カラムを複合インデックスで貼ってください

関連した質問