9JFmN66G

1DROP TABLE IF EXISTS test.tree;
2CREATE TABLE test.tree (
3    lft INT(10) UNSIGNED NOT NULL,
4    rght INT(10) UNSIGNED NOT NULL,
5    INDEX ix_left_right (lft, rght),
6    INDEX ix_left (lft),
7    INDEX ix_right (rght)
8);
9
10EXPLAIN
11SELECT X.*
12FROM test.tree X
13LEFT JOIN test.tree Y ON  X.lft BETWEEN Y.lft AND Y.rght;
14	
15{
16    "table": "UnknownTable",
17    "rows":
18    [
19        {
20            "id": 1,
21            "select_type": "SIMPLE",
22            "table": "X",
23            "partitions": null,
24            "type": "index",
25            "possible_keys": null,
26            "key": "ix_left_right",
27            "key_len": "8",
28            "ref": null,
29            "rows": 1,
30            "filtered": 100.00,
31            "Extra": "Using index"
32        },
33        {
34            "id": 1,
35            "select_type": "SIMPLE",
36            "table": "Y",
37            "partitions": null,
38            "type": "ALL",
39            "possible_keys": "ix_left_right,ix_left,ix_right",
40            "key": null,
41            "key_len": null,
42            "ref": null,
43            "rows": 1,
44            "filtered": 100.00,
45            "Extra": "Range checked for each record (index map: 0x7)"
46        }
47    ]
48}