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は候補の一つとしていいと思います。
参照
Discussion