mysql merge index的奇怪问题

jason0127

mysql> explain select SENDERID from test1  force index(RECEIVERID,DELETED) where test1.RECEIVERID=16136  or test1.DELETED=0 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test1
         type: index_merge
possible_keys: RECEIVERID,DELETED
          key: RECEIVERID,DELETED
      key_len: 4,1
          ref: NULL
         rows: 13296
        Extra: Using union(RECEIVERID,DELETED); Using where
1 row in set (0.00 sec)

mysql> explain select SENDERID from test1  where test1.RECEIVERID=16136  or test1.DELETED=0 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test1
         type: ALL
possible_keys: RECEIVERID,DELETED
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 26590
        Extra: Using where
1 row in set (0.00 sec)

mysql 的版本如下，存储引擎为innodb, 采用共享存储。为什么默认用不上union index呢？

mysql> select version();
+------------+
| version()  |
+------------+
| 5.1.42-log |
+------------+
1 row in set (0.00 sec)

justlooks

type: index_merge 已经指出是扫描多个索引，并且进行结果合并了，注意extra栏的using union

cenalulu

和statistics有关吧，优化器觉得merge没有意义，所以不用了。
force index 强制修改的执行计划，所以index merge了