Has anyone ever successfully make index merge work for MySQL?
- by user198729
Setup:
mysql> create table t(a integer unsigned,b integer unsigned);
mysql> insert into t(a,b) values (1,2),(1,3),(2,4);
mysql> create index i_t_a on t(a);
mysql> create index i_t_b on t(b);
mysql> explain select * from t where a=1 or b=4;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | t | ALL | i_t_a,i_t_b | NULL | NULL | NULL | 3 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
Is there something I'm missing?
Update
mysql> explain select * from t where a=1 or b=4;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | t | ALL | i_t_a,i_t_b | NULL | NULL | NULL | 1863 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
Version:
mysql> select version();
+----------------------+
| version() |
+----------------------+
| 5.1.36-community-log |
+----------------------+
Has anyone ever successfully make index merge work for MySQL?
I'll be glad to see successful stories here:)
