index_merge not working in MariaDB

Question

There is MariaDB 10.1.20 (also checked in MySQL CE 5.7 and Percona Server 5.7)

1) Create table:

CREATE TABLE test1 (
     id INTEGER NOT NULL DEFAULT 0,
     flag enum('y','n') NOT NULL DEFAULT 'n',
     KEY(id), KEY(flag)
) ENGINE=InnoDB;

2) All index_merge_xx seems enabled:

MariaDB [db1]> show variables like 'optimizer_switch'\G
*************************** 1. row ***************************
Variable_name: optimizer_switch
        Value: index_merge=on,index_merge_union=on,index_merge_sort_union=on,
index_merge_intersection=on,index_merge_sort_intersection=on,
engine_condition_pushdown=off,index_condition_pushdown=on,
derived_merge=on,derived_with_keys=on,firstmatch=on,
loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowi    d_merge=on,partial_match_table_scan=on,
subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,
outer_join_with_cache=on,semijoin_with_cache=on,
join_cache_incremental=on,join_cache_hashed=on,
join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,
extended_keys=on,exists_to_in=on,orderby_uses_equalities=off
1 row in set (0.01 sec)

3) Do query:

MariaDB [db1]> EXPLAIN SELECT * FROM test1 WHERE id = 1 AND flag = 'n'; 
+------+-------------+-------+------+---------------+------+---------+-------+------+-------------+
| id   | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra       |
+------+-------------+-------+------+---------------+------+---------+-------+------+-------------+
|    1 | SIMPLE      | test1 | ref  | id,flag       | id   | 4       | const |    1 | Using where |
+------+-------------+-------+------+---------------+------+---------+-------+------+-------------+
1 row in set (0.00 sec)

In more detailed form:

MariaDB [db1]> EXPLAIN FORMAT=JSON SELECT * FROM test1 WHERE id = 1 AND flag = 'n'\G
*************************** 1. row ***************************
EXPLAIN: {
  "query_block": {
    "select_id": 1,
    "table": {
      "table_name": "test1",
      "access_type": "ref",
      "possible_keys": ["id", "flag"],
      "key": "id",
      "key_length": "4",
      "used_key_parts": ["id"],
      "ref": ["const"],
      "rows": 1,
      "filtered": 100,
      "attached_condition": "(test1.flag = 'n')"
    }
  }
}
1 row in set (0.00 sec)

Why SELECT ignores the second index and uses the first only?

Answer 1

"Index merge intersect" is rarely picked because it involves getting lots of row pointers from two indexes, then intersecting the sets, and only then looking back in the table.

It is always better (as far as I know) to build a composite index:

INDEX(id, flag)  -- in either order

If this is a 1-row table, then the effort of looking in two indexes to ultimately get a single row -- this is a waste.

A side note: An InnoDB table should always have a PRIMARY KEY.

Put a thousand realistic rows in the table and add a PRIMARY KEY. Then we can discuss this further. (But the answer will be the same -- use a composite index instead of hoping for "index merge intersect".)