0

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?

asked Jan 10, 2017 at 1:39

1 Answer 1

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".)

answered Jan 10, 2017 at 23:18

Your Answer

Draft saved
Draft discarded

Sign up or log in

Sign up using Google
Sign up using Email and Password

Post as a guest

Required, but never shown

Post as a guest

Required, but never shown

By clicking "Post Your Answer", you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.