1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
|
create or replace table t1 (col1 char(10) character set ucs2, filler char(100), key(col1)) ;
insert into t1 values ('a', 'a');
insert into t1 values ('a', 'a');
set optimizer_trace=1;
explain format=json select * from t1 force index(col1) where col1 >='a';
EXPLAIN
{
"query_block": {
"select_id": 1,
"nested_loop": [
{
"table": {
"table_name": "t1",
"access_type": "range",
"possible_keys": ["col1"],
"key": "col1",
"key_length": "21",
"used_key_parts": ["col1"],
"rows": 2,
"filtered": 100,
"index_condition": "t1.col1 >= 'a'"
}
}
]
}
}
select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives'))
[
{
"range_scan_alternatives":
[
{
"index": "col1",
"ranges":
[
"(a) <= (col1)"
],
"rowid_ordered": false,
"using_mrr": false,
"index_only": false,
"rows": 2,
"cost": 2.547733708,
"chosen": true
}
],
"analyzing_roworder_intersect":
{
"cause": "too few roworder scans"
},
"analyzing_index_merge_union":
[
]
}
]
drop table t1;
|
