MySQLでの、WHERE句のORにindexは効かないのか..?

概要

ネットでWhere句のORにindexが効くかどうか調べると、以下のような意見があって何が正解かわかりませんでした。なので、検証して真偽を確かめていこうと思います。

• 「OR演算子にインデックスは効かない」
• 「OR演算子にインデックスは効く」
• 「OR演算子の右辺はインデックスが効かない」

検証するDBはMySQL5.7.36です。

検証1 where句の片方の検索条件にindexがあり、もう片方の検索条件にはindexがないパターン

country_id、laungage_idの２つのカラムを使います。
country_idにはindexが貼られていて、laungage_idには貼られていません。

EXPLAIN SELECT *
FROM member
WHERE country_id = 10 OR laungage_id = 20;

結果:効かない
type が ALLなのでフルテーブルスキャンになっていますね。

id  select_type  table  partitions type  possible_keys    key   key_len  ref   rows    filtered     Extra     
---------------------------------------------------------------------------------------------------------------
1	SIMPLE	member	NULL	ALL	idx_country_id	NULL	NULL	NULL	10000	15.29	Using where

検証2 where句の両方の検索条件にindexがあるパターン

country_idにはindexが貼られています。

EXPLAIN SELECT *
FROM member
WHERE country_id = 10 OR country_id = 20;

結果:効く
type が rangeなのでindexが効いています。

id  select_type  table  partitions type  possible_keys    key   key_len  ref   rows    filtered     Extra     
---------------------------------------------------------------------------------------------------------------
1	SIMPLE	member	NULL	range   idx_country_id	NULL	NULL	NULL	5	100.00	Using index condition

検証3　indexが貼ってあるカラムでinを使うパターン

orではなくinを使った方がいいと書いているサイトが幾つかあったのでこちらも検証。

EXPLAIN SELECT *
FROM member
WHERE country_id in (20, 26);

結果:効く
type が rangeなのでindexが効いています。

id  select_type  table  partitions type  possible_keys    key   key_len  ref   rows    filtered     Extra     
---------------------------------------------------------------------------------------------------------------
1	SIMPLE	member	NULL	range   idx_country_id	NULL	NULL	NULL	5	100.00	Using index condition

検証4 indexが貼ってあるカラムでunionを使ったパターン

orではなくunionを使った方がいいと書いているサイトが幾つかあったのでこちらも検証。

EXPLAIN SELECT *
FROM member
WHERE country_id = 20
UNION ALL
SELECT *
FROM member
WHERE country_id = 26;

結果:効く
type が refなのでindexが効いています。

id  select_type  table  partitions type  possible_keys    key   key_len  ref   rows    filtered     Extra       

1	 SIMPLE	 　member	 NULL	 ref   idx_country_id	NULL	NULL	NULL	4	100.00	
1	 SIMPLE	 　member	 NULL	 ref   idx_country_id	NULL	NULL	NULL	1	100.00	

注意

where句内のorに限った話ではないと思いますが、MySQLがフルテーブルスキャンの方が効率がいいと判断したら、indexを貼っていてもフルテーブルスキャンになるはずです。

例1) country_idにはindexが貼られていて、country_id=1が全体のレコードの90％を占めている場合。

EXPLAIN SELECT *
FROM member
WHERE country_id = 1 ;

type が ALLなのでフルテーブルスキャン。

id  select_type  table  partitions type  possible_keys    key   key_len  ref   rows    filtered     Extra     
---------------------------------------------------------------------------------------------------------------
1	SIMPLE	member	NULL	ALL	idx_country_id	NULL	NULL	NULL	10000	90.00	Using where

例2) country_idにはindexが貼られていて、country_id=2が全体のレコードの5％を占めている場合。

EXPLAIN SELECT *
FROM member
WHERE country_id = 2;

type が refなのでindexが効いています。

id  select_type  table  partitions type  possible_keys    key   key_len  ref   rows    filtered     Extra     
---------------------------------------------------------------------------------------------------------------
1	SIMPLE	member	NULL	ref	idx_country_id	NULL	NULL	NULL	500	100.00	

何故こちらの注意を書いたかというと、読んでいる方が手元でorにindexが効くかEXPLAINで確認したけど、ここで書いている結果と違うんだけど...!!ってならないようにするためです。本来indexが効くような場面でも検索条件によっては効かないということもあります。

まとめ

「OR演算子にインデックスは効かない」なんてことはない。
where句の片方の検索条件にindexがあり、もう片方の検索条件にはindexがない時はindexが効かないので要注意。
orの代用として、inやunionは候補の一つとしていいと思います。

参照

https://zenn.dev/team_soda/articles/ce989fa4cbe4c2?redirected=1

https://zenn.dev/hk_206/articles/ec5f4e347caff4#インデックスが適用されない場合もある