Description:
The documentation for this feature:
http://dev.mysql.com/doc/refman/5.7/en/explain-output.html
----------------------------------------------------
Range checked for each record (index map: N)
MySQL found no good index to use, but found that some of indexes might be used after column values from preceding tables are known. For each row combination in the preceding tables, MySQL checks whether it is possible to use a range or index_merge access method to retrieve rows. This is not very fast, but is faster than performing a join with no index at all. The applicability criteria are as described in Section 8.2.1.3, “Range Optimization”, and Section 8.2.1.4, “Index Merge Optimization”, with the exception that all column values for the preceding table are known and considered to be constants.
Indexes are numbered beginning with 1, in the same order as shown by SHOW INDEX for the table. The index map value N is a bitmask value that indicates which indexes are candidates. For example, a value of 0x19 (binary 11001) means that indexes 1, 4, and 5 will be considered.
----------------------------------------------------
The part from the json explain:
----------------------------------------------------
{
"table": {
"table_name": "l",
"access_type": "ALL",
"possible_keys": [
"left_right_index"
],
"rows_examined_per_scan": 11,
"rows_produced_per_join": 44,
"filtered": 100,
"range_checked_for_each_record": "index map: 0x2",
"cost_info": {
"read_cost": "1.00",
"eval_cost": "8.80",
"prefix_cost": "11.61",
"data_read_per_join": "1K"
},
"used_columns": [
"id",
"lft",
"rght",
"node_depth"
]
}
----------------------------------------------------
And from the traditional explain:
----------------------------------------------------
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: l
partitions: NULL
type: ALL
possible_keys: left_right_index
key: NULL
key_len: NULL
ref: NULL
rows: 11
filtered: 100.00
Extra: Range checked for each record (index map: 0x2)
----------------------------------------------------
The index map bitmap makes sense for the one-line per entry, but not for the json explain.
It should resolve the tables in the index map.
How to repeat:
CREATE TABLE `docnode_tree` (
`id` int(10) unsigned NOT NULL,
`lft` int(10) unsigned DEFAULT NULL,
`rght` int(10) unsigned DEFAULT NULL,
`node_depth` int(10) unsigned DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `left_right_index` (`lft`,`rght`),
KEY `right_index` (`rght`),
KEY `node_depth_index` (`node_depth`),
KEY `dept_l` (`node_depth`,`lft`),
KEY `dept_r` (`node_depth`,`rght`),
KEY `dept_lr` (`node_depth`,`lft`,`rght`),
KEY `dept_rl` (`node_depth`,`rght`,`lft`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `docnode_tree` VALUES (1,10,20,0),(2,8,10,1),(3,11,15,1),(4,16,20,1),(5,21,25,1),(6,8,9,2),(7,9,10,2),(8,10,11,2),(9,11,12,2),(10,12,13,2),(11,13,14,2);
explain format=json SELECT * FROM docnode_tree r JOIN docnode_tree l ON l.lft BETWEEN r.lft AND r.rght WHERE r.node_depth=1\G
explain SELECT * FROM docnode_tree r JOIN docnode_tree l ON l.lft BETWEEN r.lft AND r.rght WHERE r.node_depth=1\G
Suggested fix:
Solution 1: Add the index map
"range_checked_for_each_record": "index map: 0x2",
"index_map_info": {
"0x1": "foo_index",
"0x2": "bar_index"
"0x3": {"foo_index", "bar_index"}
}
Listing all combinations makes it easier for the tools, but might create a large list.
Solution 2: Resolve the map and display only the used indices
"range_checked_for_each_record": {
"foo_index",
"bar_index"
}
© 2024, Oracle Corporation and/or its affiliates