|
Почему не работает index_merge?
#39381177
Ссылка:
Ссылка на сообщение:
Ссылка с названием темы:
|
|
|
|
Имеется MariaDB 10.1.20 (также проверял в MySQL CE 5.7 и Percona Server 5.7)
Создаю таблицу:
1.
2.
3.
4.
5.
CREATE TABLE test1 (
id INTEGER NOT NULL DEFAULT 0,
flag enum('y','n') NOT NULL DEFAULT 'n',
KEY(id), KEY(flag)
) ENGINE=InnoDB;
Проверяю, что все index_merge_xx включены:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
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_rowid_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)
Делаю запрос:
1.
2.
3.
4.
5.
6.
7.
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)
Он же в развёрнутом виде:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
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)
|
|
|
