mirror of
https://github.com/MariaDB/server.git
synced 2025-10-01 21:39:18 +02:00
16f2b7e3df
Introduces NO_INDEX_MERGE and INDEX_MERGE, which control whether or
not index merge strategies are used during query optimization. Here
is an example query from the tests:
SET optimizer_switch='index_merge_intersection=off';
EXPLAIN SELECT /*+ INDEX_MERGE(t1 f4, f2) */ COUNT(*) FROM t1
WHERE f4 = 'h' AND f2 = 2;
with the hint in place, the query plan will employ the index_merge
intersect strategy (abbreviated EXPLAIN output):
type Extra
index_merge Using intersect(f2,f4); Using where; Using index
The presence of the [NO_]INDEX_MERGE hint overrides the optimizer's
choice of keys during the index merge optimization. As we see in
the above example, keys f2 and f4 and given and the optimizer will
consider only those keys for this query.
When the hint is given without any particular keys, as in
INDEX_MERGE(table), then all keys are considered. In this case, the
cheapest index merge among the keys should be used. When
NO_INDEX_MERGE(table) is given, then index merge is disabled for
that table.
When the hint is given with one or more keys, then only those keys
are considered. In the case of NO_INDEX_MERGE, those keys are
excluded. This can lead to no merged indexes at all, because
there may not be sufficient row-ordered read columns available for
consideration.
The index merge strategies of intersection, union, and sort union
cannot themselves be directly controlled via the hints. In combination
with the optimizer switches for the same, the strategy may be
indirectly controlled but this is not guaranteed.
When the hint directs the optimizer such that insufficient ROR scans
are available, thus leading to a situation where the INDEX_MERGE hint
cannot be honored, the server will emit a warning to that effect.
In the hints module (opt_hints*{cc,h}), this commit adds some
index merge-specific functionality to make interpreting hint state
at callsites in the optimizer cleaner and more intuitive.
Particularly, we add a bit field to the table hints class which
indicates the keys that are marked by an [NO_]INDEX_MERGE hint, if
present. A new function, index_merge_hint (and associated new
helper functions) relies on this field when interpreting index merge
hint state for the optimizer.
If there are no index merges available prior to attemping to find
a suitable union/sort union, then the optimizer will not attempt
it. This change results in optimizer trace output which does not
include the 'analyzing_index_merge_union' block when there are no
merges.
Parts of this implementation based on MySQL commit
ebcb981807e3d91a64782e89d48e1a25622eafea
703 lines
26 KiB
Text
703 lines
26 KiB
Text
set @tmp_opt_switch= @@optimizer_switch;
|
|
set optimizer_switch='index_merge_sort_intersection=on';
|
|
set optimizer_trace='enabled=on';
|
|
create table t0 (a int);
|
|
insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
|
|
create table t1 (a int, b int, c int, filler char(100),
|
|
key(a), key(b), key(c)) charset=latin1;
|
|
insert into t1 select
|
|
A.a * B.a*10 + C.a*100,
|
|
A.a * B.a*10 + C.a*100,
|
|
A.a,
|
|
'filler'
|
|
from t0 A, t0 B, t0 C;
|
|
This should use union:
|
|
explain select * from t1 where a=1 or b=1;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 index_merge a,b a,b 5,5 NULL 2 Using union(a,b); Using where
|
|
select * from information_schema.OPTIMIZER_TRACE;
|
|
QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
|
|
explain select * from t1 where a=1 or b=1 {
|
|
"steps": [
|
|
{
|
|
"join_preparation": {
|
|
"select_id": 1,
|
|
"steps": [
|
|
{
|
|
"expanded_query": "select t1.a AS a,t1.b AS b,t1.c AS c,t1.filler AS filler from t1 where t1.a = 1 or t1.b = 1"
|
|
}
|
|
]
|
|
}
|
|
},
|
|
{
|
|
"join_optimization": {
|
|
"select_id": 1,
|
|
"steps": [
|
|
{
|
|
"condition_processing": {
|
|
"condition": "WHERE",
|
|
"original_condition": "t1.a = 1 or t1.b = 1",
|
|
"steps": [
|
|
{
|
|
"transformation": "equality_propagation",
|
|
"resulting_condition": "multiple equal(1, t1.a) or multiple equal(1, t1.b)"
|
|
},
|
|
{
|
|
"transformation": "constant_propagation",
|
|
"resulting_condition": "multiple equal(1, t1.a) or multiple equal(1, t1.b)"
|
|
},
|
|
{
|
|
"transformation": "trivial_condition_removal",
|
|
"resulting_condition": "multiple equal(1, t1.a) or multiple equal(1, t1.b)"
|
|
}
|
|
]
|
|
}
|
|
},
|
|
{
|
|
"ref_optimizer_key_uses": []
|
|
},
|
|
{
|
|
"rows_estimation": [
|
|
{
|
|
"table": "t1",
|
|
"range_analysis": {
|
|
"table_scan": {
|
|
"rows": 1000,
|
|
"cost": 0.1729314
|
|
},
|
|
"potential_range_indexes": [
|
|
{
|
|
"index": "a",
|
|
"usable": true,
|
|
"key_parts": ["a"]
|
|
},
|
|
{
|
|
"index": "b",
|
|
"usable": true,
|
|
"key_parts": ["b"]
|
|
},
|
|
{
|
|
"index": "c",
|
|
"usable": false,
|
|
"cause": "not applicable"
|
|
}
|
|
],
|
|
"setup_range_conditions": [],
|
|
"analyzing_range_alternatives": {
|
|
"range_scan_alternatives": [],
|
|
"analyzing_roworder_intersect": {
|
|
"cause": "too few roworder scans"
|
|
},
|
|
"analyzing_sort_intersect": {
|
|
"cutoff_cost": 0.1729314
|
|
},
|
|
"analyzing_index_merge_union": [
|
|
{
|
|
"indexes_to_merge": [
|
|
{
|
|
"range_scan_alternatives": [
|
|
{
|
|
"index": "a",
|
|
"ranges": ["(1) <= (a) <= (1)"],
|
|
"rowid_ordered": true,
|
|
"using_mrr": false,
|
|
"index_only": true,
|
|
"rows": 1,
|
|
"cost": 0.001478954,
|
|
"chosen": true
|
|
}
|
|
],
|
|
"index_to_merge": "a",
|
|
"cumulated_cost": 0.001478954
|
|
},
|
|
{
|
|
"range_scan_alternatives": [
|
|
{
|
|
"index": "b",
|
|
"ranges": ["(1) <= (b) <= (1)"],
|
|
"rowid_ordered": true,
|
|
"using_mrr": false,
|
|
"index_only": true,
|
|
"rows": 1,
|
|
"cost": 0.001478954,
|
|
"chosen": true
|
|
}
|
|
],
|
|
"index_to_merge": "b",
|
|
"cumulated_cost": 0.002957908
|
|
}
|
|
],
|
|
"cost_of_reading_ranges": 0.002957908,
|
|
"use_roworder_union": true,
|
|
"cause": "always cheaper than non roworder retrieval",
|
|
"analyzing_roworder_scans": [
|
|
{
|
|
"type": "range_scan",
|
|
"index": "a",
|
|
"rows": 1,
|
|
"ranges": ["(1) <= (a) <= (1)"],
|
|
"analyzing_roworder_intersect": {
|
|
"cause": "too few roworder scans"
|
|
}
|
|
},
|
|
{
|
|
"type": "range_scan",
|
|
"index": "b",
|
|
"rows": 1,
|
|
"ranges": ["(1) <= (b) <= (1)"],
|
|
"analyzing_roworder_intersect": {
|
|
"cause": "too few roworder scans"
|
|
}
|
|
}
|
|
],
|
|
"index_roworder_union_cost": 0.005185782,
|
|
"members": 2,
|
|
"chosen": true
|
|
}
|
|
]
|
|
},
|
|
"group_index_range": {
|
|
"chosen": false,
|
|
"cause": "no group by or distinct"
|
|
},
|
|
"chosen_range_access_summary": {
|
|
"range_access_plan": {
|
|
"type": "index_roworder_union",
|
|
"union_of": [
|
|
{
|
|
"type": "range_scan",
|
|
"index": "a",
|
|
"rows": 1,
|
|
"ranges": ["(1) <= (a) <= (1)"]
|
|
},
|
|
{
|
|
"type": "range_scan",
|
|
"index": "b",
|
|
"rows": 1,
|
|
"ranges": ["(1) <= (b) <= (1)"]
|
|
}
|
|
]
|
|
},
|
|
"rows_for_plan": 2,
|
|
"cost_for_plan": 0.005185782,
|
|
"chosen": true
|
|
}
|
|
}
|
|
},
|
|
{
|
|
"selectivity_for_indexes": [
|
|
{
|
|
"use_opt_range_condition_rows_selectivity": 0.002
|
|
}
|
|
],
|
|
"selectivity_for_columns": [],
|
|
"cond_selectivity": 0.002
|
|
}
|
|
]
|
|
},
|
|
{
|
|
"table_dependencies": [
|
|
{
|
|
"table": "t1",
|
|
"row_may_be_null": false,
|
|
"map_bit": 0,
|
|
"depends_on_map_bits": []
|
|
}
|
|
]
|
|
},
|
|
{
|
|
"considered_execution_plans": [
|
|
{
|
|
"plan_prefix": "",
|
|
"get_costs_for_tables": [
|
|
{
|
|
"best_access_path": {
|
|
"table": "t1",
|
|
"plan_details": {
|
|
"record_count": 1
|
|
},
|
|
"considered_access_paths": [
|
|
{
|
|
"access_type": "index_merge",
|
|
"rows": 2,
|
|
"rows_after_filter": 2,
|
|
"rows_out": 2,
|
|
"cost": 0.005185782,
|
|
"chosen": true
|
|
}
|
|
],
|
|
"chosen_access_method": {
|
|
"type": "index_merge",
|
|
"rows_read": 2,
|
|
"rows_out": 2,
|
|
"cost": 0.005185782,
|
|
"uses_join_buffering": false
|
|
}
|
|
}
|
|
}
|
|
]
|
|
},
|
|
{
|
|
"plan_prefix": "",
|
|
"table": "t1",
|
|
"rows_for_plan": 2,
|
|
"cost_for_plan": 0.005185782
|
|
}
|
|
]
|
|
},
|
|
{
|
|
"best_join_order": ["t1"],
|
|
"rows": 2,
|
|
"cost": 0.005185782
|
|
},
|
|
{
|
|
"substitute_best_equal": {
|
|
"condition": "WHERE",
|
|
"resulting_condition": "t1.a = 1 or t1.b = 1"
|
|
}
|
|
},
|
|
{
|
|
"attaching_conditions_to_tables": {
|
|
"attached_conditions_computation": [],
|
|
"attached_conditions_summary": [
|
|
{
|
|
"table": "t1",
|
|
"attached_condition": "t1.a = 1 or t1.b = 1"
|
|
}
|
|
]
|
|
}
|
|
},
|
|
{
|
|
"make_join_readinfo": []
|
|
}
|
|
]
|
|
}
|
|
}
|
|
]
|
|
} 0 0
|
|
drop table t0,t1;
|
|
set optimizer_trace="enabled=off";
|
|
set @@optimizer_switch= @tmp_opt_switch;
|
|
# More tests added index_merge access
|
|
create table t1
|
|
(
|
|
/* Field names reflect value(rowid) distribution, st=STairs, swt= SaWTooth */
|
|
st_a int not null default 0,
|
|
swt1a int not null default 0,
|
|
swt2a int not null default 0,
|
|
st_b int not null default 0,
|
|
swt1b int not null default 0,
|
|
swt2b int not null default 0,
|
|
/* fields/keys for row retrieval tests */
|
|
key1 int,
|
|
key2 int,
|
|
key3 int,
|
|
key4 int,
|
|
/* make rows much bigger then keys */
|
|
filler1 char (200),
|
|
filler2 char (200),
|
|
filler3 char (200),
|
|
filler4 char (200),
|
|
filler5 char (200),
|
|
filler6 char (200),
|
|
/* order of keys is important */
|
|
key sta_swt12a(st_a,swt1a,swt2a),
|
|
key sta_swt1a(st_a,swt1a),
|
|
key sta_swt2a(st_a,swt2a),
|
|
key sta_swt21a(st_a,swt2a,swt1a),
|
|
key st_a(st_a),
|
|
key stb_swt1a_2b(st_b,swt1b,swt2a),
|
|
key stb_swt1b(st_b,swt1b),
|
|
key st_b(st_b),
|
|
key(key1),
|
|
key(key2),
|
|
key(key3),
|
|
key(key4)
|
|
) ;
|
|
create table t0 as select * from t1;
|
|
# Printing of many insert into t0 values (....) disabled.
|
|
alter table t1 disable keys;
|
|
# Printing of many insert into t1 select .... from t0 disabled.
|
|
# Printing of many insert into t1 (...) values (....) disabled.
|
|
alter table t1 enable keys;
|
|
insert into t1 (key1, key2, key3, key4, filler1) values (100, 100, -1, -1, 'key1-key2');
|
|
insert into t1 (key1, key2, key3, key4, filler1) values (-1, -1, 100, 100, 'key4-key3');
|
|
set optimizer_trace='enabled=on';
|
|
# 3-way ROR-intersection
|
|
explain select key1,key2,key3 from t1 where key1=100 and key2=100 and key3=100;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 index_merge key1,key2,key3 key1,key2 5,5 NULL 77 Using intersect(key1,key2); Using where
|
|
select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) AS JS from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
|
|
JS
|
|
[
|
|
{
|
|
"range_scan_alternatives":
|
|
[
|
|
{
|
|
"index": "key1",
|
|
"ranges":
|
|
["(100) <= (key1) <= (100)"],
|
|
"rowid_ordered": true,
|
|
"using_mrr": false,
|
|
"index_only": false,
|
|
"rows": 2243,
|
|
"cost": 2.770351251,
|
|
"chosen": true
|
|
},
|
|
{
|
|
"index": "key2",
|
|
"ranges":
|
|
["(100) <= (key2) <= (100)"],
|
|
"rowid_ordered": true,
|
|
"using_mrr": false,
|
|
"index_only": false,
|
|
"rows": 2243,
|
|
"cost": 2.770351251,
|
|
"chosen": false,
|
|
"cause": "cost"
|
|
},
|
|
{
|
|
"index": "key3",
|
|
"ranges":
|
|
["(100) <= (key3) <= (100)"],
|
|
"rowid_ordered": true,
|
|
"using_mrr": false,
|
|
"index_only": false,
|
|
"rows": 2243,
|
|
"cost": 2.770351251,
|
|
"chosen": false,
|
|
"cause": "cost"
|
|
}
|
|
],
|
|
"analyzing_roworder_intersect":
|
|
{
|
|
"intersecting_indexes":
|
|
[
|
|
{
|
|
"index": "key1",
|
|
"index_scan_cost": 0.240986767,
|
|
"cumulated_index_scan_cost": 0.240986767,
|
|
"disk_sweep_cost": 2.564386012,
|
|
"cumulative_total_cost": 2.805372779,
|
|
"usable": true,
|
|
"matching_rows_now": 2243,
|
|
"intersect_covering_with_this_index": false,
|
|
"chosen": true
|
|
},
|
|
{
|
|
"index": "key2",
|
|
"index_scan_cost": 0.240986767,
|
|
"cumulated_index_scan_cost": 0.481973534,
|
|
"disk_sweep_cost": 0.089164506,
|
|
"cumulative_total_cost": 0.57113804,
|
|
"usable": true,
|
|
"matching_rows_now": 77.6360508,
|
|
"intersect_covering_with_this_index": false,
|
|
"chosen": true
|
|
},
|
|
{
|
|
"index": "key3",
|
|
"index_scan_cost": 0.240986767,
|
|
"cumulated_index_scan_cost": 0.722960301,
|
|
"disk_sweep_cost": 0,
|
|
"cumulative_total_cost": 0.722960301,
|
|
"usable": true,
|
|
"matching_rows_now": 2.687185191,
|
|
"intersect_covering_with_this_index": true,
|
|
"chosen": false,
|
|
"cause": "does not reduce cost"
|
|
}
|
|
],
|
|
"clustered_pk":
|
|
{
|
|
"clustered_pk_added_to_intersect": false,
|
|
"cause": "no clustered pk index"
|
|
},
|
|
"rows": 77,
|
|
"cost": 0.573622393,
|
|
"covering": false,
|
|
"chosen": true
|
|
}
|
|
}
|
|
]
|
|
select JSON_DETAILED(JSON_EXTRACT(trace, '$**.chosen_range_access_summary')) AS JS from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
|
|
JS
|
|
[
|
|
{
|
|
"range_access_plan":
|
|
{
|
|
"type": "index_roworder_intersect",
|
|
"rows": 77,
|
|
"cost": 0.573622393,
|
|
"covering": false,
|
|
"clustered_pk_scan": false,
|
|
"intersect_of":
|
|
[
|
|
{
|
|
"type": "range_scan",
|
|
"index": "key1",
|
|
"rows": 2243,
|
|
"ranges":
|
|
["(100) <= (key1) <= (100)"]
|
|
},
|
|
{
|
|
"type": "range_scan",
|
|
"index": "key2",
|
|
"rows": 2243,
|
|
"ranges":
|
|
["(100) <= (key2) <= (100)"]
|
|
}
|
|
]
|
|
},
|
|
"rows_for_plan": 77,
|
|
"cost_for_plan": 0.573622393,
|
|
"chosen": true
|
|
}
|
|
]
|
|
# ROR-union(ROR-intersection, ROR-range)
|
|
explain select key1,key2,key3,key4 from t1 where key1=100 and key2=100 or key3=100 and key4=100;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 index_merge key1,key2,key3,key4 key1,key2,key3,key4 5,5,5,5 NULL 154 Using union(intersect(key1,key2),intersect(key3,key4)); Using where
|
|
select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) AS JS from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
|
|
JS
|
|
[
|
|
{
|
|
"range_scan_alternatives":
|
|
[],
|
|
"analyzing_roworder_intersect":
|
|
{
|
|
"cause": "too few roworder scans"
|
|
},
|
|
"analyzing_index_merge_union":
|
|
[
|
|
{
|
|
"indexes_to_merge":
|
|
[
|
|
{
|
|
"range_scan_alternatives":
|
|
[
|
|
{
|
|
"index": "key1",
|
|
"ranges":
|
|
["(100) <= (key1) <= (100)"],
|
|
"rowid_ordered": true,
|
|
"using_mrr": false,
|
|
"index_only": true,
|
|
"rows": 2243,
|
|
"cost": 0.312922694,
|
|
"chosen": true
|
|
},
|
|
{
|
|
"index": "key2",
|
|
"ranges":
|
|
["(100) <= (key2) <= (100)"],
|
|
"rowid_ordered": true,
|
|
"using_mrr": false,
|
|
"index_only": true,
|
|
"rows": 2243,
|
|
"cost": 0.312922694,
|
|
"chosen": false,
|
|
"cause": "cost"
|
|
}
|
|
],
|
|
"index_to_merge": "key1",
|
|
"cumulated_cost": 0.312922694
|
|
},
|
|
{
|
|
"range_scan_alternatives":
|
|
[
|
|
{
|
|
"index": "key3",
|
|
"ranges":
|
|
["(100) <= (key3) <= (100)"],
|
|
"rowid_ordered": true,
|
|
"using_mrr": false,
|
|
"index_only": true,
|
|
"rows": 2243,
|
|
"cost": 0.312922694,
|
|
"chosen": true
|
|
},
|
|
{
|
|
"index": "key4",
|
|
"ranges":
|
|
["(100) <= (key4) <= (100)"],
|
|
"rowid_ordered": true,
|
|
"using_mrr": false,
|
|
"index_only": true,
|
|
"rows": 2243,
|
|
"cost": 0.312922694,
|
|
"chosen": false,
|
|
"cause": "cost"
|
|
}
|
|
],
|
|
"index_to_merge": "key3",
|
|
"cumulated_cost": 0.625845388
|
|
}
|
|
],
|
|
"cost_of_reading_ranges": 0.625845388,
|
|
"use_roworder_union": true,
|
|
"cause": "always cheaper than non roworder retrieval",
|
|
"analyzing_roworder_scans":
|
|
[
|
|
{
|
|
"type": "range_scan",
|
|
"index": "key1",
|
|
"rows": 2243,
|
|
"ranges":
|
|
["(100) <= (key1) <= (100)"],
|
|
"analyzing_roworder_intersect":
|
|
{
|
|
"intersecting_indexes":
|
|
[
|
|
{
|
|
"index": "key1",
|
|
"index_scan_cost": 0.240986767,
|
|
"cumulated_index_scan_cost": 0.240986767,
|
|
"disk_sweep_cost": 2.564386012,
|
|
"cumulative_total_cost": 2.805372779,
|
|
"usable": true,
|
|
"matching_rows_now": 2243,
|
|
"intersect_covering_with_this_index": false,
|
|
"chosen": true
|
|
},
|
|
{
|
|
"index": "key2",
|
|
"index_scan_cost": 0.240986767,
|
|
"cumulated_index_scan_cost": 0.481973534,
|
|
"disk_sweep_cost": 0.089164506,
|
|
"cumulative_total_cost": 0.57113804,
|
|
"usable": true,
|
|
"matching_rows_now": 77.6360508,
|
|
"intersect_covering_with_this_index": false,
|
|
"chosen": true
|
|
}
|
|
],
|
|
"clustered_pk":
|
|
{
|
|
"clustered_pk_added_to_intersect": false,
|
|
"cause": "no clustered pk index"
|
|
},
|
|
"rows": 77,
|
|
"cost": 0.573622393,
|
|
"covering": false,
|
|
"chosen": true
|
|
}
|
|
},
|
|
{
|
|
"type": "range_scan",
|
|
"index": "key3",
|
|
"rows": 2243,
|
|
"ranges":
|
|
["(100) <= (key3) <= (100)"],
|
|
"analyzing_roworder_intersect":
|
|
{
|
|
"intersecting_indexes":
|
|
[
|
|
{
|
|
"index": "key3",
|
|
"index_scan_cost": 0.240986767,
|
|
"cumulated_index_scan_cost": 0.240986767,
|
|
"disk_sweep_cost": 2.564386012,
|
|
"cumulative_total_cost": 2.805372779,
|
|
"usable": true,
|
|
"matching_rows_now": 2243,
|
|
"intersect_covering_with_this_index": false,
|
|
"chosen": true
|
|
},
|
|
{
|
|
"index": "key4",
|
|
"index_scan_cost": 0.240986767,
|
|
"cumulated_index_scan_cost": 0.481973534,
|
|
"disk_sweep_cost": 0.089164506,
|
|
"cumulative_total_cost": 0.57113804,
|
|
"usable": true,
|
|
"matching_rows_now": 77.6360508,
|
|
"intersect_covering_with_this_index": false,
|
|
"chosen": true
|
|
}
|
|
],
|
|
"clustered_pk":
|
|
{
|
|
"clustered_pk_added_to_intersect": false,
|
|
"cause": "no clustered pk index"
|
|
},
|
|
"rows": 77,
|
|
"cost": 0.573622393,
|
|
"covering": false,
|
|
"chosen": true
|
|
}
|
|
}
|
|
],
|
|
"index_roworder_union_cost": 1.135493366,
|
|
"members": 2,
|
|
"chosen": true
|
|
}
|
|
]
|
|
}
|
|
]
|
|
select JSON_DETAILED(JSON_EXTRACT(trace, '$**.chosen_range_access_summary')) AS JS from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
|
|
JS
|
|
[
|
|
{
|
|
"range_access_plan":
|
|
{
|
|
"type": "index_roworder_union",
|
|
"union_of":
|
|
[
|
|
{
|
|
"type": "index_roworder_intersect",
|
|
"rows": 77,
|
|
"cost": 0.573622393,
|
|
"covering": false,
|
|
"clustered_pk_scan": false,
|
|
"intersect_of":
|
|
[
|
|
{
|
|
"type": "range_scan",
|
|
"index": "key1",
|
|
"rows": 2243,
|
|
"ranges":
|
|
["(100) <= (key1) <= (100)"]
|
|
},
|
|
{
|
|
"type": "range_scan",
|
|
"index": "key2",
|
|
"rows": 2243,
|
|
"ranges":
|
|
["(100) <= (key2) <= (100)"]
|
|
}
|
|
]
|
|
},
|
|
{
|
|
"type": "index_roworder_intersect",
|
|
"rows": 77,
|
|
"cost": 0.573622393,
|
|
"covering": false,
|
|
"clustered_pk_scan": false,
|
|
"intersect_of":
|
|
[
|
|
{
|
|
"type": "range_scan",
|
|
"index": "key3",
|
|
"rows": 2243,
|
|
"ranges":
|
|
["(100) <= (key3) <= (100)"]
|
|
},
|
|
{
|
|
"type": "range_scan",
|
|
"index": "key4",
|
|
"rows": 2243,
|
|
"ranges":
|
|
["(100) <= (key4) <= (100)"]
|
|
}
|
|
]
|
|
}
|
|
]
|
|
},
|
|
"rows_for_plan": 154,
|
|
"cost_for_plan": 1.135493366,
|
|
"chosen": true
|
|
}
|
|
]
|
|
drop table t0,t1;
|
|
set optimizer_trace="enabled=off";
|