6929 lines
257 KiB
Plaintext
6929 lines
257 KiB
Plaintext
SET optimizer_trace_max_mem_size=1048576;
|
|
SET end_markers_in_json=on;
|
|
SET optimizer_trace="enabled=on,one_line=off";
|
|
CREATE TABLE t1
|
|
(
|
|
key1 INT NOT NULL,
|
|
INDEX i1(key1)
|
|
);
|
|
Inserting 1024 records into t1
|
|
ALTER TABLE t1 ADD key2 INT NOT NULL, ADD INDEX i2(key2);
|
|
ALTER TABLE t1 ADD key3 INT NOT NULL, ADD INDEX i3(key3);
|
|
ALTER TABLE t1 ADD key4 INT NOT NULL, ADD INDEX i4(key4);
|
|
ALTER TABLE t1 ADD key5 INT NOT NULL, ADD INDEX i5(key5);
|
|
ALTER TABLE t1 ADD key6 INT NOT NULL, ADD INDEX i6(key6);
|
|
ALTER TABLE t1 ADD key7 INT NOT NULL, ADD INDEX i7(key7);
|
|
ALTER TABLE t1 ADD key8 INT NOT NULL, ADD INDEX i8(key8);
|
|
UPDATE t1 SET
|
|
key2=key1,
|
|
key3=key1,
|
|
key4=key1,
|
|
key5=key1,
|
|
key6=key1,
|
|
key7=key1,
|
|
key8=1024-key1;
|
|
CREATE TABLE t2 (
|
|
key1a INT NOT NULL,
|
|
key1b INT NOT NULL,
|
|
key2 INT NOT NULL,
|
|
key2_1 INT NOT NULL,
|
|
key2_2 INT NOT NULL,
|
|
key3 INT NOT NULL,
|
|
primary key i1a (key1a, key1b),
|
|
INDEX i1b (key1b, key1a),
|
|
INDEX i2_1(key2, key2_1),
|
|
INDEX i2_2(key2, key2_1)
|
|
);
|
|
Warnings:
|
|
Warning 1831 Duplicate index 'i2_2' defined on the table 'test.t2'. This is deprecated and will be disallowed in a future release.
|
|
INSERT INTO t2 SELECT key1,key1,key1 div 10, key1 % 10, key1 % 10, key1 FROM t1;
|
|
|
|
EXPLAIN SELECT * FROM t1 WHERE key2 < 5 OR key2 > 1020;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 NULL range i2 i2 4 NULL 47 100.00 Using index condition
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`key1` AS `key1`,`test`.`t1`.`key2` AS `key2`,`test`.`t1`.`key3` AS `key3`,`test`.`t1`.`key4` AS `key4`,`test`.`t1`.`key5` AS `key5`,`test`.`t1`.`key6` AS `key6`,`test`.`t1`.`key7` AS `key7`,`test`.`t1`.`key8` AS `key8` from `test`.`t1` where ((`test`.`t1`.`key2` < 5) or (`test`.`t1`.`key2` > 1020))
|
|
|
|
SELECT * FROM information_schema.OPTIMIZER_TRACE;
|
|
QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
|
|
EXPLAIN SELECT * FROM t1 WHERE key2 < 5 OR key2 > 1020 {
|
|
"steps": [
|
|
{
|
|
"join_preparation": {
|
|
"select#": 1,
|
|
"steps": [
|
|
{
|
|
"expanded_query": "/* select#1 */ select `t1`.`key1` AS `key1`,`t1`.`key2` AS `key2`,`t1`.`key3` AS `key3`,`t1`.`key4` AS `key4`,`t1`.`key5` AS `key5`,`t1`.`key6` AS `key6`,`t1`.`key7` AS `key7`,`t1`.`key8` AS `key8` from `t1` where ((`t1`.`key2` < 5) or (`t1`.`key2` > 1020))"
|
|
}
|
|
] /* steps */
|
|
} /* join_preparation */
|
|
},
|
|
{
|
|
"join_optimization": {
|
|
"select#": 1,
|
|
"steps": [
|
|
{
|
|
"condition_processing": {
|
|
"condition": "WHERE",
|
|
"original_condition": "((`t1`.`key2` < 5) or (`t1`.`key2` > 1020))",
|
|
"steps": [
|
|
{
|
|
"transformation": "equality_propagation",
|
|
"resulting_condition": "((`t1`.`key2` < 5) or (`t1`.`key2` > 1020))"
|
|
},
|
|
{
|
|
"transformation": "constant_propagation",
|
|
"resulting_condition": "((`t1`.`key2` < 5) or (`t1`.`key2` > 1020))"
|
|
},
|
|
{
|
|
"transformation": "trivial_condition_removal",
|
|
"resulting_condition": "((`t1`.`key2` < 5) or (`t1`.`key2` > 1020))"
|
|
}
|
|
] /* steps */
|
|
} /* condition_processing */
|
|
},
|
|
{
|
|
"substitute_generated_columns": {
|
|
} /* substitute_generated_columns */
|
|
},
|
|
{
|
|
"table_dependencies": [
|
|
{
|
|
"table": "`t1`",
|
|
"row_may_be_null": false,
|
|
"map_bit": 0,
|
|
"depends_on_map_bits": [
|
|
] /* depends_on_map_bits */
|
|
}
|
|
] /* table_dependencies */
|
|
},
|
|
{
|
|
"ref_optimizer_key_uses": [
|
|
] /* ref_optimizer_key_uses */
|
|
},
|
|
{
|
|
"rows_estimation": [
|
|
{
|
|
"table": "`t1`",
|
|
"range_analysis": {
|
|
"table_scan": {
|
|
"rows": 1024,
|
|
"cost": 107.06
|
|
} /* table_scan */,
|
|
"potential_range_indexes": [
|
|
{
|
|
"index": "i1",
|
|
"usable": false,
|
|
"cause": "not_applicable"
|
|
},
|
|
{
|
|
"index": "i2",
|
|
"usable": true,
|
|
"key_parts": [
|
|
"key2"
|
|
] /* key_parts */
|
|
},
|
|
{
|
|
"index": "i3",
|
|
"usable": false,
|
|
"cause": "not_applicable"
|
|
},
|
|
{
|
|
"index": "i4",
|
|
"usable": false,
|
|
"cause": "not_applicable"
|
|
},
|
|
{
|
|
"index": "i5",
|
|
"usable": false,
|
|
"cause": "not_applicable"
|
|
},
|
|
{
|
|
"index": "i6",
|
|
"usable": false,
|
|
"cause": "not_applicable"
|
|
},
|
|
{
|
|
"index": "i7",
|
|
"usable": false,
|
|
"cause": "not_applicable"
|
|
},
|
|
{
|
|
"index": "i8",
|
|
"usable": false,
|
|
"cause": "not_applicable"
|
|
}
|
|
] /* potential_range_indexes */,
|
|
"setup_range_conditions": [
|
|
] /* setup_range_conditions */,
|
|
"group_index_range": {
|
|
"chosen": false,
|
|
"cause": "not_group_by_or_distinct"
|
|
} /* group_index_range */,
|
|
"skip_scan_range": {
|
|
"potential_skip_scan_indexes": [
|
|
{
|
|
"index": "i2",
|
|
"usable": false,
|
|
"cause": "query_references_nonkey_column"
|
|
}
|
|
] /* potential_skip_scan_indexes */
|
|
} /* skip_scan_range */,
|
|
"analyzing_range_alternatives": {
|
|
"range_scan_alternatives": [
|
|
{
|
|
"index": "i2",
|
|
"ranges": [
|
|
"key2 < 5",
|
|
"1020 < key2"
|
|
] /* ranges */,
|
|
"index_dives_for_eq_ranges": true,
|
|
"rowid_ordered": false,
|
|
"using_mrr": false,
|
|
"index_only": false,
|
|
"rows": 47,
|
|
"cost": 16.96,
|
|
"chosen": true
|
|
}
|
|
] /* range_scan_alternatives */,
|
|
"analyzing_roworder_intersect": {
|
|
"usable": false,
|
|
"cause": "too_few_roworder_scans"
|
|
} /* analyzing_roworder_intersect */
|
|
} /* analyzing_range_alternatives */,
|
|
"chosen_range_access_summary": {
|
|
"range_access_plan": {
|
|
"type": "range_scan",
|
|
"index": "i2",
|
|
"rows": 47,
|
|
"ranges": [
|
|
"key2 < 5",
|
|
"1020 < key2"
|
|
] /* ranges */
|
|
} /* range_access_plan */,
|
|
"rows_for_plan": 47,
|
|
"cost_for_plan": 16.96,
|
|
"chosen": true
|
|
} /* chosen_range_access_summary */
|
|
} /* range_analysis */
|
|
}
|
|
] /* rows_estimation */
|
|
},
|
|
{
|
|
"considered_execution_plans": [
|
|
{
|
|
"plan_prefix": [
|
|
] /* plan_prefix */,
|
|
"table": "`t1`",
|
|
"best_access_path": {
|
|
"considered_access_paths": [
|
|
{
|
|
"rows_to_scan": 47,
|
|
"filtering_effect": [
|
|
] /* filtering_effect */,
|
|
"final_filtering_effect": 1,
|
|
"access_type": "range",
|
|
"range_details": {
|
|
"used_index": "i2"
|
|
} /* range_details */,
|
|
"resulting_rows": 47,
|
|
"cost": 21.66,
|
|
"chosen": true
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */,
|
|
"condition_filtering_pct": 100,
|
|
"rows_for_plan": 47,
|
|
"cost_for_plan": 21.66,
|
|
"chosen": true
|
|
}
|
|
] /* considered_execution_plans */
|
|
},
|
|
{
|
|
"attaching_conditions_to_tables": {
|
|
"original_condition": "((`t1`.`key2` < 5) or (`t1`.`key2` > 1020))",
|
|
"attached_conditions_computation": [
|
|
] /* attached_conditions_computation */,
|
|
"attached_conditions_summary": [
|
|
{
|
|
"table": "`t1`",
|
|
"attached": "((`t1`.`key2` < 5) or (`t1`.`key2` > 1020))"
|
|
}
|
|
] /* attached_conditions_summary */
|
|
} /* attaching_conditions_to_tables */
|
|
},
|
|
{
|
|
"finalizing_table_conditions": [
|
|
{
|
|
"table": "`t1`",
|
|
"original_table_condition": "((`t1`.`key2` < 5) or (`t1`.`key2` > 1020))",
|
|
"final_table_condition ": "((`t1`.`key2` < 5) or (`t1`.`key2` > 1020))"
|
|
}
|
|
] /* finalizing_table_conditions */
|
|
},
|
|
{
|
|
"refine_plan": [
|
|
{
|
|
"table": "`t1`",
|
|
"pushed_index_condition": "((`t1`.`key2` < 5) or (`t1`.`key2` > 1020))",
|
|
"table_condition_attached": null
|
|
}
|
|
] /* refine_plan */
|
|
}
|
|
] /* steps */
|
|
} /* join_optimization */
|
|
},
|
|
{
|
|
"join_explain": {
|
|
"select#": 1,
|
|
"steps": [
|
|
] /* steps */
|
|
} /* join_explain */
|
|
}
|
|
] /* steps */
|
|
} 0 0
|
|
set @@optimizer_trace_features="range_optimizer=off";
|
|
|
|
EXPLAIN SELECT * FROM t1 WHERE key2 < 5 OR key2 > 1020;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 NULL range i2 i2 4 NULL 47 100.00 Using index condition
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`key1` AS `key1`,`test`.`t1`.`key2` AS `key2`,`test`.`t1`.`key3` AS `key3`,`test`.`t1`.`key4` AS `key4`,`test`.`t1`.`key5` AS `key5`,`test`.`t1`.`key6` AS `key6`,`test`.`t1`.`key7` AS `key7`,`test`.`t1`.`key8` AS `key8` from `test`.`t1` where ((`test`.`t1`.`key2` < 5) or (`test`.`t1`.`key2` > 1020))
|
|
|
|
SELECT * FROM information_schema.OPTIMIZER_TRACE;
|
|
QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
|
|
EXPLAIN SELECT * FROM t1 WHERE key2 < 5 OR key2 > 1020 {
|
|
"steps": [
|
|
{
|
|
"join_preparation": {
|
|
"select#": 1,
|
|
"steps": [
|
|
{
|
|
"expanded_query": "/* select#1 */ select `t1`.`key1` AS `key1`,`t1`.`key2` AS `key2`,`t1`.`key3` AS `key3`,`t1`.`key4` AS `key4`,`t1`.`key5` AS `key5`,`t1`.`key6` AS `key6`,`t1`.`key7` AS `key7`,`t1`.`key8` AS `key8` from `t1` where ((`t1`.`key2` < 5) or (`t1`.`key2` > 1020))"
|
|
}
|
|
] /* steps */
|
|
} /* join_preparation */
|
|
},
|
|
{
|
|
"join_optimization": {
|
|
"select#": 1,
|
|
"steps": [
|
|
{
|
|
"condition_processing": {
|
|
"condition": "WHERE",
|
|
"original_condition": "((`t1`.`key2` < 5) or (`t1`.`key2` > 1020))",
|
|
"steps": [
|
|
{
|
|
"transformation": "equality_propagation",
|
|
"resulting_condition": "((`t1`.`key2` < 5) or (`t1`.`key2` > 1020))"
|
|
},
|
|
{
|
|
"transformation": "constant_propagation",
|
|
"resulting_condition": "((`t1`.`key2` < 5) or (`t1`.`key2` > 1020))"
|
|
},
|
|
{
|
|
"transformation": "trivial_condition_removal",
|
|
"resulting_condition": "((`t1`.`key2` < 5) or (`t1`.`key2` > 1020))"
|
|
}
|
|
] /* steps */
|
|
} /* condition_processing */
|
|
},
|
|
{
|
|
"substitute_generated_columns": {
|
|
} /* substitute_generated_columns */
|
|
},
|
|
{
|
|
"table_dependencies": [
|
|
{
|
|
"table": "`t1`",
|
|
"row_may_be_null": false,
|
|
"map_bit": 0,
|
|
"depends_on_map_bits": [
|
|
] /* depends_on_map_bits */
|
|
}
|
|
] /* table_dependencies */
|
|
},
|
|
{
|
|
"ref_optimizer_key_uses": [
|
|
] /* ref_optimizer_key_uses */
|
|
},
|
|
{
|
|
"rows_estimation": [
|
|
{
|
|
"table": "`t1`",
|
|
"range_analysis": {
|
|
"table_scan": {
|
|
"rows": 1024,
|
|
"cost": 107.06
|
|
} /* table_scan */,
|
|
"potential_range_indexes": "...",
|
|
"setup_range_conditions": [
|
|
] /* setup_range_conditions */,
|
|
"group_index_range": "...",
|
|
"skip_scan_range": "...",
|
|
"analyzing_range_alternatives": "...",
|
|
"chosen_range_access_summary": {
|
|
"range_access_plan": {
|
|
"type": "range_scan",
|
|
"index": "i2",
|
|
"rows": 47,
|
|
"ranges": [
|
|
"key2 < 5",
|
|
"1020 < key2"
|
|
] /* ranges */
|
|
} /* range_access_plan */,
|
|
"rows_for_plan": 47,
|
|
"cost_for_plan": 16.96,
|
|
"chosen": true
|
|
} /* chosen_range_access_summary */
|
|
} /* range_analysis */
|
|
}
|
|
] /* rows_estimation */
|
|
},
|
|
{
|
|
"considered_execution_plans": [
|
|
{
|
|
"plan_prefix": [
|
|
] /* plan_prefix */,
|
|
"table": "`t1`",
|
|
"best_access_path": {
|
|
"considered_access_paths": [
|
|
{
|
|
"rows_to_scan": 47,
|
|
"filtering_effect": [
|
|
] /* filtering_effect */,
|
|
"final_filtering_effect": 1,
|
|
"access_type": "range",
|
|
"range_details": {
|
|
"used_index": "i2"
|
|
} /* range_details */,
|
|
"resulting_rows": 47,
|
|
"cost": 21.66,
|
|
"chosen": true
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */,
|
|
"condition_filtering_pct": 100,
|
|
"rows_for_plan": 47,
|
|
"cost_for_plan": 21.66,
|
|
"chosen": true
|
|
}
|
|
] /* considered_execution_plans */
|
|
},
|
|
{
|
|
"attaching_conditions_to_tables": {
|
|
"original_condition": "((`t1`.`key2` < 5) or (`t1`.`key2` > 1020))",
|
|
"attached_conditions_computation": [
|
|
] /* attached_conditions_computation */,
|
|
"attached_conditions_summary": [
|
|
{
|
|
"table": "`t1`",
|
|
"attached": "((`t1`.`key2` < 5) or (`t1`.`key2` > 1020))"
|
|
}
|
|
] /* attached_conditions_summary */
|
|
} /* attaching_conditions_to_tables */
|
|
},
|
|
{
|
|
"finalizing_table_conditions": [
|
|
{
|
|
"table": "`t1`",
|
|
"original_table_condition": "((`t1`.`key2` < 5) or (`t1`.`key2` > 1020))",
|
|
"final_table_condition ": "((`t1`.`key2` < 5) or (`t1`.`key2` > 1020))"
|
|
}
|
|
] /* finalizing_table_conditions */
|
|
},
|
|
{
|
|
"refine_plan": [
|
|
{
|
|
"table": "`t1`",
|
|
"pushed_index_condition": "((`t1`.`key2` < 5) or (`t1`.`key2` > 1020))",
|
|
"table_condition_attached": null
|
|
}
|
|
] /* refine_plan */
|
|
}
|
|
] /* steps */
|
|
} /* join_optimization */
|
|
},
|
|
{
|
|
"join_explain": {
|
|
"select#": 1,
|
|
"steps": [
|
|
] /* steps */
|
|
} /* join_explain */
|
|
}
|
|
] /* steps */
|
|
} 0 0
|
|
set @@optimizer_trace_features="range_optimizer=on";
|
|
|
|
EXPLAIN SELECT * FROM t1 WHERE key1 < 3 OR key2 > 1020;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 NULL index_merge i1,i2 i1,i2 4,4 NULL 45 100.00 Using sort_union(i1,i2); Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`key1` AS `key1`,`test`.`t1`.`key2` AS `key2`,`test`.`t1`.`key3` AS `key3`,`test`.`t1`.`key4` AS `key4`,`test`.`t1`.`key5` AS `key5`,`test`.`t1`.`key6` AS `key6`,`test`.`t1`.`key7` AS `key7`,`test`.`t1`.`key8` AS `key8` from `test`.`t1` where ((`test`.`t1`.`key1` < 3) or (`test`.`t1`.`key2` > 1020))
|
|
|
|
SELECT * FROM information_schema.OPTIMIZER_TRACE;
|
|
QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
|
|
EXPLAIN SELECT * FROM t1 WHERE key1 < 3 OR key2 > 1020 {
|
|
"steps": [
|
|
{
|
|
"join_preparation": {
|
|
"select#": 1,
|
|
"steps": [
|
|
{
|
|
"expanded_query": "/* select#1 */ select `t1`.`key1` AS `key1`,`t1`.`key2` AS `key2`,`t1`.`key3` AS `key3`,`t1`.`key4` AS `key4`,`t1`.`key5` AS `key5`,`t1`.`key6` AS `key6`,`t1`.`key7` AS `key7`,`t1`.`key8` AS `key8` from `t1` where ((`t1`.`key1` < 3) or (`t1`.`key2` > 1020))"
|
|
}
|
|
] /* steps */
|
|
} /* join_preparation */
|
|
},
|
|
{
|
|
"join_optimization": {
|
|
"select#": 1,
|
|
"steps": [
|
|
{
|
|
"condition_processing": {
|
|
"condition": "WHERE",
|
|
"original_condition": "((`t1`.`key1` < 3) or (`t1`.`key2` > 1020))",
|
|
"steps": [
|
|
{
|
|
"transformation": "equality_propagation",
|
|
"resulting_condition": "((`t1`.`key1` < 3) or (`t1`.`key2` > 1020))"
|
|
},
|
|
{
|
|
"transformation": "constant_propagation",
|
|
"resulting_condition": "((`t1`.`key1` < 3) or (`t1`.`key2` > 1020))"
|
|
},
|
|
{
|
|
"transformation": "trivial_condition_removal",
|
|
"resulting_condition": "((`t1`.`key1` < 3) or (`t1`.`key2` > 1020))"
|
|
}
|
|
] /* steps */
|
|
} /* condition_processing */
|
|
},
|
|
{
|
|
"substitute_generated_columns": {
|
|
} /* substitute_generated_columns */
|
|
},
|
|
{
|
|
"table_dependencies": [
|
|
{
|
|
"table": "`t1`",
|
|
"row_may_be_null": false,
|
|
"map_bit": 0,
|
|
"depends_on_map_bits": [
|
|
] /* depends_on_map_bits */
|
|
}
|
|
] /* table_dependencies */
|
|
},
|
|
{
|
|
"ref_optimizer_key_uses": [
|
|
] /* ref_optimizer_key_uses */
|
|
},
|
|
{
|
|
"rows_estimation": [
|
|
{
|
|
"table": "`t1`",
|
|
"range_analysis": {
|
|
"table_scan": {
|
|
"rows": 1024,
|
|
"cost": 107.06
|
|
} /* table_scan */,
|
|
"potential_range_indexes": [
|
|
{
|
|
"index": "i1",
|
|
"usable": true,
|
|
"key_parts": [
|
|
"key1"
|
|
] /* key_parts */
|
|
},
|
|
{
|
|
"index": "i2",
|
|
"usable": true,
|
|
"key_parts": [
|
|
"key2"
|
|
] /* key_parts */
|
|
},
|
|
{
|
|
"index": "i3",
|
|
"usable": false,
|
|
"cause": "not_applicable"
|
|
},
|
|
{
|
|
"index": "i4",
|
|
"usable": false,
|
|
"cause": "not_applicable"
|
|
},
|
|
{
|
|
"index": "i5",
|
|
"usable": false,
|
|
"cause": "not_applicable"
|
|
},
|
|
{
|
|
"index": "i6",
|
|
"usable": false,
|
|
"cause": "not_applicable"
|
|
},
|
|
{
|
|
"index": "i7",
|
|
"usable": false,
|
|
"cause": "not_applicable"
|
|
},
|
|
{
|
|
"index": "i8",
|
|
"usable": false,
|
|
"cause": "not_applicable"
|
|
}
|
|
] /* potential_range_indexes */,
|
|
"setup_range_conditions": [
|
|
] /* setup_range_conditions */,
|
|
"group_index_range": {
|
|
"chosen": false,
|
|
"cause": "not_group_by_or_distinct"
|
|
} /* group_index_range */,
|
|
"skip_scan_range": {
|
|
"potential_skip_scan_indexes": [
|
|
{
|
|
"index": "i1",
|
|
"usable": false,
|
|
"cause": "query_references_nonkey_column"
|
|
},
|
|
{
|
|
"index": "i2",
|
|
"usable": false,
|
|
"cause": "query_references_nonkey_column"
|
|
}
|
|
] /* potential_skip_scan_indexes */
|
|
} /* skip_scan_range */,
|
|
"analyzing_range_alternatives": {
|
|
"range_scan_alternatives": [
|
|
] /* range_scan_alternatives */,
|
|
"analyzing_roworder_intersect": {
|
|
"usable": false,
|
|
"cause": "too_few_roworder_scans"
|
|
} /* analyzing_roworder_intersect */
|
|
} /* analyzing_range_alternatives */,
|
|
"analyzing_index_merge_union": [
|
|
{
|
|
"indexes_to_merge": [
|
|
{
|
|
"range_scan_alternatives": [
|
|
{
|
|
"index": "i1",
|
|
"ranges": [
|
|
"key1 < 3"
|
|
] /* ranges */,
|
|
"index_dives_for_eq_ranges": true,
|
|
"rowid_ordered": false,
|
|
"using_mrr": false,
|
|
"index_only": true,
|
|
"rows": 3,
|
|
"cost": 0.5706,
|
|
"chosen": true
|
|
}
|
|
] /* range_scan_alternatives */,
|
|
"index_to_merge": "i1",
|
|
"cumulated_cost": 0.5706
|
|
},
|
|
{
|
|
"range_scan_alternatives": [
|
|
{
|
|
"index": "i2",
|
|
"ranges": [
|
|
"1020 < key2"
|
|
] /* ranges */,
|
|
"index_dives_for_eq_ranges": true,
|
|
"rowid_ordered": false,
|
|
"using_mrr": false,
|
|
"index_only": true,
|
|
"rows": 42,
|
|
"cost": 4.6781,
|
|
"chosen": true
|
|
}
|
|
] /* range_scan_alternatives */,
|
|
"index_to_merge": "i2",
|
|
"cumulated_cost": 5.2487
|
|
}
|
|
] /* indexes_to_merge */,
|
|
"cost_of_reading_ranges": 5.2487,
|
|
"cost_sort_rowid_and_read_disk": 2.2388,
|
|
"cost_duplicate_removal": 19.181,
|
|
"total_cost": 26.668
|
|
}
|
|
] /* analyzing_index_merge_union */,
|
|
"chosen_range_access_summary": {
|
|
"range_access_plan": {
|
|
"type": "index_merge",
|
|
"index_merge_of": [
|
|
{
|
|
"type": "range_scan",
|
|
"index": "i1",
|
|
"rows": 3,
|
|
"ranges": [
|
|
"key1 < 3"
|
|
] /* ranges */
|
|
},
|
|
{
|
|
"type": "range_scan",
|
|
"index": "i2",
|
|
"rows": 42,
|
|
"ranges": [
|
|
"1020 < key2"
|
|
] /* ranges */
|
|
}
|
|
] /* index_merge_of */
|
|
} /* range_access_plan */,
|
|
"rows_for_plan": 45,
|
|
"cost_for_plan": 26.668,
|
|
"chosen": true
|
|
} /* chosen_range_access_summary */
|
|
} /* range_analysis */
|
|
}
|
|
] /* rows_estimation */
|
|
},
|
|
{
|
|
"considered_execution_plans": [
|
|
{
|
|
"plan_prefix": [
|
|
] /* plan_prefix */,
|
|
"table": "`t1`",
|
|
"best_access_path": {
|
|
"considered_access_paths": [
|
|
{
|
|
"rows_to_scan": 45,
|
|
"filtering_effect": [
|
|
] /* filtering_effect */,
|
|
"final_filtering_effect": 1,
|
|
"access_type": "range",
|
|
"range_details": {
|
|
"used_index": "sort_union(i1,i2)"
|
|
} /* range_details */,
|
|
"resulting_rows": 45,
|
|
"cost": 31.168,
|
|
"chosen": true
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */,
|
|
"condition_filtering_pct": 100,
|
|
"rows_for_plan": 45,
|
|
"cost_for_plan": 31.168,
|
|
"chosen": true
|
|
}
|
|
] /* considered_execution_plans */
|
|
},
|
|
{
|
|
"attaching_conditions_to_tables": {
|
|
"original_condition": "((`t1`.`key1` < 3) or (`t1`.`key2` > 1020))",
|
|
"attached_conditions_computation": [
|
|
] /* attached_conditions_computation */,
|
|
"attached_conditions_summary": [
|
|
{
|
|
"table": "`t1`",
|
|
"attached": "((`t1`.`key1` < 3) or (`t1`.`key2` > 1020))"
|
|
}
|
|
] /* attached_conditions_summary */
|
|
} /* attaching_conditions_to_tables */
|
|
},
|
|
{
|
|
"finalizing_table_conditions": [
|
|
{
|
|
"table": "`t1`",
|
|
"original_table_condition": "((`t1`.`key1` < 3) or (`t1`.`key2` > 1020))",
|
|
"final_table_condition ": "((`t1`.`key1` < 3) or (`t1`.`key2` > 1020))"
|
|
}
|
|
] /* finalizing_table_conditions */
|
|
},
|
|
{
|
|
"refine_plan": [
|
|
{
|
|
"table": "`t1`"
|
|
}
|
|
] /* refine_plan */
|
|
}
|
|
] /* steps */
|
|
} /* join_optimization */
|
|
},
|
|
{
|
|
"join_explain": {
|
|
"select#": 1,
|
|
"steps": [
|
|
] /* steps */
|
|
} /* join_explain */
|
|
}
|
|
] /* steps */
|
|
} 0 0
|
|
|
|
EXPLAIN SELECT key2, MIN(key2_1) FROM t2 GROUP BY key2;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t2 NULL range i2_1,i2_2 i2_1 4 NULL 103 100.00 Using index for group-by
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t2`.`key2` AS `key2`,min(`test`.`t2`.`key2_1`) AS `MIN(key2_1)` from `test`.`t2` group by `test`.`t2`.`key2`
|
|
|
|
SELECT * FROM information_schema.OPTIMIZER_TRACE;
|
|
QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
|
|
EXPLAIN SELECT key2, MIN(key2_1) FROM t2 GROUP BY key2 {
|
|
"steps": [
|
|
{
|
|
"join_preparation": {
|
|
"select#": 1,
|
|
"steps": [
|
|
{
|
|
"expanded_query": "/* select#1 */ select `t2`.`key2` AS `key2`,min(`t2`.`key2_1`) AS `MIN(key2_1)` from `t2` group by `t2`.`key2`"
|
|
}
|
|
] /* steps */
|
|
} /* join_preparation */
|
|
},
|
|
{
|
|
"join_optimization": {
|
|
"select#": 1,
|
|
"steps": [
|
|
{
|
|
"substitute_generated_columns": {
|
|
} /* substitute_generated_columns */
|
|
},
|
|
{
|
|
"table_dependencies": [
|
|
{
|
|
"table": "`t2`",
|
|
"row_may_be_null": false,
|
|
"map_bit": 0,
|
|
"depends_on_map_bits": [
|
|
] /* depends_on_map_bits */
|
|
}
|
|
] /* table_dependencies */
|
|
},
|
|
{
|
|
"rows_estimation": [
|
|
{
|
|
"table": "`t2`",
|
|
"const_keys_added": {
|
|
"keys": [
|
|
"i2_1",
|
|
"i2_2"
|
|
] /* keys */,
|
|
"cause": "group_by"
|
|
} /* const_keys_added */,
|
|
"range_analysis": {
|
|
"table_scan": {
|
|
"rows": 1024,
|
|
"cost": 106.56
|
|
} /* table_scan */,
|
|
"potential_range_indexes": [
|
|
{
|
|
"index": "PRIMARY",
|
|
"usable": false,
|
|
"cause": "not_applicable"
|
|
},
|
|
{
|
|
"index": "i1b",
|
|
"usable": false,
|
|
"cause": "not_applicable"
|
|
},
|
|
{
|
|
"index": "i2_1",
|
|
"usable": true,
|
|
"key_parts": [
|
|
"key2",
|
|
"key2_1"
|
|
] /* key_parts */
|
|
},
|
|
{
|
|
"index": "i2_2",
|
|
"usable": true,
|
|
"key_parts": [
|
|
"key2",
|
|
"key2_1"
|
|
] /* key_parts */
|
|
}
|
|
] /* potential_range_indexes */,
|
|
"best_covering_index_scan": {
|
|
"index": "i2_1",
|
|
"cost": 109.96,
|
|
"chosen": false,
|
|
"cause": "cost"
|
|
} /* best_covering_index_scan */,
|
|
"group_index_range": {
|
|
"potential_group_range_indexes": [
|
|
{
|
|
"index": "i2_1",
|
|
"covering": true,
|
|
"rows": 103,
|
|
"cost": 28.1
|
|
},
|
|
{
|
|
"index": "i2_2",
|
|
"covering": true,
|
|
"rows": 103,
|
|
"cost": 28.1
|
|
}
|
|
] /* potential_group_range_indexes */
|
|
} /* group_index_range */,
|
|
"best_group_range_summary": {
|
|
"type": "index_group",
|
|
"index": "i2_1",
|
|
"group_attribute": "key2_1",
|
|
"min_aggregate": true,
|
|
"max_aggregate": false,
|
|
"distinct_aggregate": false,
|
|
"rows": 103,
|
|
"cost": 28.1,
|
|
"key_parts_used_for_access": [
|
|
"key2"
|
|
] /* key_parts_used_for_access */,
|
|
"ranges": [
|
|
] /* ranges */,
|
|
"chosen": true
|
|
} /* best_group_range_summary */,
|
|
"skip_scan_range": {
|
|
"chosen": false,
|
|
"cause": "has_group_by"
|
|
} /* skip_scan_range */,
|
|
"chosen_range_access_summary": {
|
|
"range_access_plan": {
|
|
"type": "index_group",
|
|
"index": "i2_1",
|
|
"group_attribute": "key2_1",
|
|
"min_aggregate": true,
|
|
"max_aggregate": false,
|
|
"distinct_aggregate": false,
|
|
"rows": 103,
|
|
"cost": 28.1,
|
|
"key_parts_used_for_access": [
|
|
"key2"
|
|
] /* key_parts_used_for_access */,
|
|
"ranges": [
|
|
] /* ranges */
|
|
} /* range_access_plan */,
|
|
"rows_for_plan": 103,
|
|
"cost_for_plan": 28.1,
|
|
"chosen": true
|
|
} /* chosen_range_access_summary */
|
|
} /* range_analysis */
|
|
}
|
|
] /* rows_estimation */
|
|
},
|
|
{
|
|
"considered_execution_plans": [
|
|
{
|
|
"plan_prefix": [
|
|
] /* plan_prefix */,
|
|
"table": "`t2`",
|
|
"best_access_path": {
|
|
"considered_access_paths": [
|
|
{
|
|
"rows_to_scan": 103,
|
|
"access_type": "range",
|
|
"range_details": {
|
|
"used_index": "index_for_group_by(i2_1)"
|
|
} /* range_details */,
|
|
"resulting_rows": 103,
|
|
"cost": 38.4,
|
|
"chosen": true,
|
|
"use_tmp_table": true
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */,
|
|
"condition_filtering_pct": 100,
|
|
"rows_for_plan": 103,
|
|
"cost_for_plan": 38.4,
|
|
"sort_cost": 103,
|
|
"new_cost_for_plan": 141.4,
|
|
"chosen": true
|
|
}
|
|
] /* considered_execution_plans */
|
|
},
|
|
{
|
|
"attaching_conditions_to_tables": {
|
|
"original_condition": null,
|
|
"attached_conditions_computation": [
|
|
] /* attached_conditions_computation */,
|
|
"attached_conditions_summary": [
|
|
{
|
|
"table": "`t2`",
|
|
"attached": null
|
|
}
|
|
] /* attached_conditions_summary */
|
|
} /* attaching_conditions_to_tables */
|
|
},
|
|
{
|
|
"optimizing_distinct_group_by_order_by": {
|
|
"simplifying_group_by": {
|
|
"original_clause": "`t2`.`key2`",
|
|
"items": [
|
|
{
|
|
"item": "`t2`.`key2`"
|
|
}
|
|
] /* items */,
|
|
"resulting_clause_is_simple": true,
|
|
"resulting_clause": "`t2`.`key2`"
|
|
} /* simplifying_group_by */
|
|
} /* optimizing_distinct_group_by_order_by */
|
|
},
|
|
{
|
|
"reconsidering_access_paths_for_index_ordering": {
|
|
"clause": "GROUP BY",
|
|
"steps": [
|
|
] /* steps */,
|
|
"index_order_summary": {
|
|
"table": "`t2`",
|
|
"index_provides_order": true,
|
|
"order_direction": "asc",
|
|
"index": "i2_1",
|
|
"plan_changed": false
|
|
} /* index_order_summary */
|
|
} /* reconsidering_access_paths_for_index_ordering */
|
|
},
|
|
{
|
|
"finalizing_table_conditions": [
|
|
] /* finalizing_table_conditions */
|
|
},
|
|
{
|
|
"refine_plan": [
|
|
{
|
|
"table": "`t2`"
|
|
}
|
|
] /* refine_plan */
|
|
},
|
|
{
|
|
"considering_tmp_tables": [
|
|
] /* considering_tmp_tables */
|
|
}
|
|
] /* steps */
|
|
} /* join_optimization */
|
|
},
|
|
{
|
|
"join_explain": {
|
|
"select#": 1,
|
|
"steps": [
|
|
] /* steps */
|
|
} /* join_explain */
|
|
}
|
|
] /* steps */
|
|
} 0 0
|
|
EXPLAIN SELECT DISTINCT key2 FROM t2;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t2 NULL range i2_1,i2_2 i2_1 4 NULL 103 100.00 Using index for group-by
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select distinct `test`.`t2`.`key2` AS `key2` from `test`.`t2`
|
|
|
|
SELECT * FROM information_schema.OPTIMIZER_TRACE;
|
|
QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
|
|
EXPLAIN SELECT DISTINCT key2 FROM t2 {
|
|
"steps": [
|
|
{
|
|
"join_preparation": {
|
|
"select#": 1,
|
|
"steps": [
|
|
{
|
|
"expanded_query": "/* select#1 */ select distinct `t2`.`key2` AS `key2` from `t2`"
|
|
}
|
|
] /* steps */
|
|
} /* join_preparation */
|
|
},
|
|
{
|
|
"join_optimization": {
|
|
"select#": 1,
|
|
"steps": [
|
|
{
|
|
"table_dependencies": [
|
|
{
|
|
"table": "`t2`",
|
|
"row_may_be_null": false,
|
|
"map_bit": 0,
|
|
"depends_on_map_bits": [
|
|
] /* depends_on_map_bits */
|
|
}
|
|
] /* table_dependencies */
|
|
},
|
|
{
|
|
"rows_estimation": [
|
|
{
|
|
"table": "`t2`",
|
|
"const_keys_added": {
|
|
"keys": [
|
|
"i2_1",
|
|
"i2_2"
|
|
] /* keys */,
|
|
"cause": "distinct"
|
|
} /* const_keys_added */,
|
|
"range_analysis": {
|
|
"table_scan": {
|
|
"rows": 1024,
|
|
"cost": 106.56
|
|
} /* table_scan */,
|
|
"potential_range_indexes": [
|
|
{
|
|
"index": "PRIMARY",
|
|
"usable": false,
|
|
"cause": "not_applicable"
|
|
},
|
|
{
|
|
"index": "i1b",
|
|
"usable": false,
|
|
"cause": "not_applicable"
|
|
},
|
|
{
|
|
"index": "i2_1",
|
|
"usable": true,
|
|
"key_parts": [
|
|
"key2",
|
|
"key2_1"
|
|
] /* key_parts */
|
|
},
|
|
{
|
|
"index": "i2_2",
|
|
"usable": true,
|
|
"key_parts": [
|
|
"key2",
|
|
"key2_1"
|
|
] /* key_parts */
|
|
}
|
|
] /* potential_range_indexes */,
|
|
"best_covering_index_scan": {
|
|
"index": "i2_1",
|
|
"cost": 109.96,
|
|
"chosen": false,
|
|
"cause": "cost"
|
|
} /* best_covering_index_scan */,
|
|
"group_index_range": {
|
|
"distinct_query": true,
|
|
"potential_group_range_indexes": [
|
|
{
|
|
"index": "i2_1",
|
|
"covering": true,
|
|
"rows": 103,
|
|
"cost": 28.1
|
|
},
|
|
{
|
|
"index": "i2_2",
|
|
"covering": true,
|
|
"rows": 103,
|
|
"cost": 28.1
|
|
}
|
|
] /* potential_group_range_indexes */
|
|
} /* group_index_range */,
|
|
"best_group_range_summary": {
|
|
"type": "index_group",
|
|
"index": "i2_1",
|
|
"group_attribute": null,
|
|
"min_aggregate": false,
|
|
"max_aggregate": false,
|
|
"distinct_aggregate": false,
|
|
"rows": 103,
|
|
"cost": 28.1,
|
|
"key_parts_used_for_access": [
|
|
"key2"
|
|
] /* key_parts_used_for_access */,
|
|
"ranges": [
|
|
] /* ranges */,
|
|
"chosen": true
|
|
} /* best_group_range_summary */,
|
|
"skip_scan_range": {
|
|
"chosen": false,
|
|
"cause": "disjuntive_predicate_present"
|
|
} /* skip_scan_range */,
|
|
"chosen_range_access_summary": {
|
|
"range_access_plan": {
|
|
"type": "index_group",
|
|
"index": "i2_1",
|
|
"group_attribute": null,
|
|
"min_aggregate": false,
|
|
"max_aggregate": false,
|
|
"distinct_aggregate": false,
|
|
"rows": 103,
|
|
"cost": 28.1,
|
|
"key_parts_used_for_access": [
|
|
"key2"
|
|
] /* key_parts_used_for_access */,
|
|
"ranges": [
|
|
] /* ranges */
|
|
} /* range_access_plan */,
|
|
"rows_for_plan": 103,
|
|
"cost_for_plan": 28.1,
|
|
"chosen": true
|
|
} /* chosen_range_access_summary */
|
|
} /* range_analysis */
|
|
}
|
|
] /* rows_estimation */
|
|
},
|
|
{
|
|
"considered_execution_plans": [
|
|
{
|
|
"plan_prefix": [
|
|
] /* plan_prefix */,
|
|
"table": "`t2`",
|
|
"best_access_path": {
|
|
"considered_access_paths": [
|
|
{
|
|
"rows_to_scan": 103,
|
|
"access_type": "range",
|
|
"range_details": {
|
|
"used_index": "index_for_group_by(i2_1)"
|
|
} /* range_details */,
|
|
"resulting_rows": 103,
|
|
"cost": 38.4,
|
|
"chosen": true
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */,
|
|
"condition_filtering_pct": 100,
|
|
"rows_for_plan": 103,
|
|
"cost_for_plan": 38.4,
|
|
"chosen": true
|
|
}
|
|
] /* considered_execution_plans */
|
|
},
|
|
{
|
|
"attaching_conditions_to_tables": {
|
|
"original_condition": null,
|
|
"attached_conditions_computation": [
|
|
] /* attached_conditions_computation */,
|
|
"attached_conditions_summary": [
|
|
{
|
|
"table": "`t2`",
|
|
"attached": null
|
|
}
|
|
] /* attached_conditions_summary */
|
|
} /* attaching_conditions_to_tables */
|
|
},
|
|
{
|
|
"optimizing_distinct_group_by_order_by": {
|
|
"changed_distinct_to_group_by": true,
|
|
"simplifying_group_by": {
|
|
"original_clause": "`t2`.`key2`",
|
|
"items": [
|
|
{
|
|
"item": "`t2`.`key2`"
|
|
}
|
|
] /* items */,
|
|
"resulting_clause_is_simple": true,
|
|
"resulting_clause": "`t2`.`key2`"
|
|
} /* simplifying_group_by */
|
|
} /* optimizing_distinct_group_by_order_by */
|
|
},
|
|
{
|
|
"reconsidering_access_paths_for_index_ordering": {
|
|
"clause": "GROUP BY",
|
|
"steps": [
|
|
] /* steps */,
|
|
"index_order_summary": {
|
|
"table": "`t2`",
|
|
"index_provides_order": true,
|
|
"order_direction": "asc",
|
|
"index": "i2_1",
|
|
"plan_changed": false
|
|
} /* index_order_summary */
|
|
} /* reconsidering_access_paths_for_index_ordering */
|
|
},
|
|
{
|
|
"finalizing_table_conditions": [
|
|
] /* finalizing_table_conditions */
|
|
},
|
|
{
|
|
"refine_plan": [
|
|
{
|
|
"table": "`t2`"
|
|
}
|
|
] /* refine_plan */
|
|
},
|
|
{
|
|
"considering_tmp_tables": [
|
|
] /* considering_tmp_tables */
|
|
}
|
|
] /* steps */
|
|
} /* join_optimization */
|
|
},
|
|
{
|
|
"join_explain": {
|
|
"select#": 1,
|
|
"steps": [
|
|
] /* steps */
|
|
} /* join_explain */
|
|
}
|
|
] /* steps */
|
|
} 0 0
|
|
|
|
EXPLAIN SELECT key2, MIN(key2_1) FROM t2
|
|
WHERE key2 = 5 or key2 = 4 or key2 = 3 or key2 = 2 or key2 = 1
|
|
GROUP BY key2;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t2 NULL range i2_1,i2_2 i2_1 4 NULL 47 100.00 Using where; Using index
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t2`.`key2` AS `key2`,min(`test`.`t2`.`key2_1`) AS `MIN(key2_1)` from `test`.`t2` where ((`test`.`t2`.`key2` = 5) or (`test`.`t2`.`key2` = 4) or (`test`.`t2`.`key2` = 3) or (`test`.`t2`.`key2` = 2) or (`test`.`t2`.`key2` = 1)) group by `test`.`t2`.`key2`
|
|
|
|
SELECT * FROM information_schema.OPTIMIZER_TRACE;
|
|
QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
|
|
EXPLAIN SELECT key2, MIN(key2_1) FROM t2
|
|
WHERE key2 = 5 or key2 = 4 or key2 = 3 or key2 = 2 or key2 = 1
|
|
GROUP BY key2 {
|
|
"steps": [
|
|
{
|
|
"join_preparation": {
|
|
"select#": 1,
|
|
"steps": [
|
|
{
|
|
"expanded_query": "/* select#1 */ select `t2`.`key2` AS `key2`,min(`t2`.`key2_1`) AS `MIN(key2_1)` from `t2` where ((`t2`.`key2` = 5) or (`t2`.`key2` = 4) or (`t2`.`key2` = 3) or (`t2`.`key2` = 2) or (`t2`.`key2` = 1)) group by `t2`.`key2`"
|
|
}
|
|
] /* steps */
|
|
} /* join_preparation */
|
|
},
|
|
{
|
|
"join_optimization": {
|
|
"select#": 1,
|
|
"steps": [
|
|
{
|
|
"condition_processing": {
|
|
"condition": "WHERE",
|
|
"original_condition": "((`t2`.`key2` = 5) or (`t2`.`key2` = 4) or (`t2`.`key2` = 3) or (`t2`.`key2` = 2) or (`t2`.`key2` = 1))",
|
|
"steps": [
|
|
{
|
|
"transformation": "equality_propagation",
|
|
"resulting_condition": "(multiple equal(5, `t2`.`key2`) or multiple equal(4, `t2`.`key2`) or multiple equal(3, `t2`.`key2`) or multiple equal(2, `t2`.`key2`) or multiple equal(1, `t2`.`key2`))"
|
|
},
|
|
{
|
|
"transformation": "constant_propagation",
|
|
"resulting_condition": "(multiple equal(5, `t2`.`key2`) or multiple equal(4, `t2`.`key2`) or multiple equal(3, `t2`.`key2`) or multiple equal(2, `t2`.`key2`) or multiple equal(1, `t2`.`key2`))"
|
|
},
|
|
{
|
|
"transformation": "trivial_condition_removal",
|
|
"resulting_condition": "(multiple equal(5, `t2`.`key2`) or multiple equal(4, `t2`.`key2`) or multiple equal(3, `t2`.`key2`) or multiple equal(2, `t2`.`key2`) or multiple equal(1, `t2`.`key2`))"
|
|
}
|
|
] /* steps */
|
|
} /* condition_processing */
|
|
},
|
|
{
|
|
"substitute_generated_columns": {
|
|
} /* substitute_generated_columns */
|
|
},
|
|
{
|
|
"table_dependencies": [
|
|
{
|
|
"table": "`t2`",
|
|
"row_may_be_null": false,
|
|
"map_bit": 0,
|
|
"depends_on_map_bits": [
|
|
] /* depends_on_map_bits */
|
|
}
|
|
] /* table_dependencies */
|
|
},
|
|
{
|
|
"ref_optimizer_key_uses": [
|
|
] /* ref_optimizer_key_uses */
|
|
},
|
|
{
|
|
"rows_estimation": [
|
|
{
|
|
"table": "`t2`",
|
|
"range_analysis": {
|
|
"table_scan": {
|
|
"rows": 1024,
|
|
"cost": 106.56
|
|
} /* table_scan */,
|
|
"potential_range_indexes": [
|
|
{
|
|
"index": "PRIMARY",
|
|
"usable": false,
|
|
"cause": "not_applicable"
|
|
},
|
|
{
|
|
"index": "i1b",
|
|
"usable": false,
|
|
"cause": "not_applicable"
|
|
},
|
|
{
|
|
"index": "i2_1",
|
|
"usable": true,
|
|
"key_parts": [
|
|
"key2",
|
|
"key2_1"
|
|
] /* key_parts */
|
|
},
|
|
{
|
|
"index": "i2_2",
|
|
"usable": true,
|
|
"key_parts": [
|
|
"key2",
|
|
"key2_1"
|
|
] /* key_parts */
|
|
}
|
|
] /* potential_range_indexes */,
|
|
"best_covering_index_scan": {
|
|
"index": "i2_1",
|
|
"cost": 109.96,
|
|
"chosen": false,
|
|
"cause": "cost"
|
|
} /* best_covering_index_scan */,
|
|
"setup_range_conditions": [
|
|
] /* setup_range_conditions */,
|
|
"group_index_range": {
|
|
"potential_group_range_indexes": [
|
|
{
|
|
"index": "i2_1",
|
|
"covering": true,
|
|
"index_dives_for_eq_ranges": true,
|
|
"ranges": [
|
|
"1 <= key2 <= 1",
|
|
"2 <= key2 <= 2",
|
|
"3 <= key2 <= 3",
|
|
"4 <= key2 <= 4",
|
|
"5 <= key2 <= 5"
|
|
] /* ranges */,
|
|
"rows": 5,
|
|
"cost": 8.5
|
|
},
|
|
{
|
|
"index": "i2_2",
|
|
"covering": true,
|
|
"index_dives_for_eq_ranges": true,
|
|
"ranges": [
|
|
"1 <= key2 <= 1",
|
|
"2 <= key2 <= 2",
|
|
"3 <= key2 <= 3",
|
|
"4 <= key2 <= 4",
|
|
"5 <= key2 <= 5"
|
|
] /* ranges */,
|
|
"rows": 5,
|
|
"cost": 8.5
|
|
}
|
|
] /* potential_group_range_indexes */
|
|
} /* group_index_range */,
|
|
"best_group_range_summary": {
|
|
"type": "index_group",
|
|
"index": "i2_1",
|
|
"group_attribute": "key2_1",
|
|
"min_aggregate": true,
|
|
"max_aggregate": false,
|
|
"distinct_aggregate": false,
|
|
"rows": 5,
|
|
"cost": 8.5,
|
|
"key_parts_used_for_access": [
|
|
"key2"
|
|
] /* key_parts_used_for_access */,
|
|
"ranges": [
|
|
"1 <= key2 <= 1",
|
|
"2 <= key2 <= 2",
|
|
"3 <= key2 <= 3",
|
|
"4 <= key2 <= 4",
|
|
"5 <= key2 <= 5"
|
|
] /* ranges */,
|
|
"chosen": true
|
|
} /* best_group_range_summary */,
|
|
"skip_scan_range": {
|
|
"chosen": false,
|
|
"cause": "has_group_by"
|
|
} /* skip_scan_range */,
|
|
"analyzing_range_alternatives": {
|
|
"range_scan_alternatives": [
|
|
{
|
|
"index": "i2_1",
|
|
"ranges": [
|
|
"1 <= key2 <= 1",
|
|
"2 <= key2 <= 2",
|
|
"3 <= key2 <= 3",
|
|
"4 <= key2 <= 4",
|
|
"5 <= key2 <= 5"
|
|
] /* ranges */,
|
|
"index_dives_for_eq_ranges": true,
|
|
"rowid_ordered": false,
|
|
"using_mrr": false,
|
|
"index_only": true,
|
|
"rows": 47,
|
|
"cost": 5.2886,
|
|
"chosen": true
|
|
},
|
|
{
|
|
"index": "i2_2",
|
|
"ranges": [
|
|
"1 <= key2 <= 1",
|
|
"2 <= key2 <= 2",
|
|
"3 <= key2 <= 3",
|
|
"4 <= key2 <= 4",
|
|
"5 <= key2 <= 5"
|
|
] /* ranges */,
|
|
"index_dives_for_eq_ranges": true,
|
|
"rowid_ordered": false,
|
|
"using_mrr": false,
|
|
"index_only": true,
|
|
"rows": 47,
|
|
"cost": 5.2886,
|
|
"chosen": false,
|
|
"cause": "cost"
|
|
}
|
|
] /* range_scan_alternatives */,
|
|
"analyzing_roworder_intersect": {
|
|
"usable": false,
|
|
"cause": "too_few_roworder_scans"
|
|
} /* analyzing_roworder_intersect */
|
|
} /* analyzing_range_alternatives */,
|
|
"chosen_range_access_summary": {
|
|
"range_access_plan": {
|
|
"type": "range_scan",
|
|
"index": "i2_1",
|
|
"rows": 47,
|
|
"ranges": [
|
|
"1 <= key2 <= 1",
|
|
"2 <= key2 <= 2",
|
|
"3 <= key2 <= 3",
|
|
"4 <= key2 <= 4",
|
|
"5 <= key2 <= 5"
|
|
] /* ranges */
|
|
} /* range_access_plan */,
|
|
"rows_for_plan": 47,
|
|
"cost_for_plan": 5.2886,
|
|
"chosen": true
|
|
} /* chosen_range_access_summary */
|
|
} /* range_analysis */
|
|
}
|
|
] /* rows_estimation */
|
|
},
|
|
{
|
|
"considered_execution_plans": [
|
|
{
|
|
"plan_prefix": [
|
|
] /* plan_prefix */,
|
|
"table": "`t2`",
|
|
"best_access_path": {
|
|
"considered_access_paths": [
|
|
{
|
|
"rows_to_scan": 47,
|
|
"filtering_effect": [
|
|
] /* filtering_effect */,
|
|
"final_filtering_effect": 1,
|
|
"access_type": "range",
|
|
"range_details": {
|
|
"used_index": "i2_1"
|
|
} /* range_details */,
|
|
"resulting_rows": 47,
|
|
"cost": 9.9886,
|
|
"chosen": true,
|
|
"use_tmp_table": true
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */,
|
|
"condition_filtering_pct": 100,
|
|
"rows_for_plan": 47,
|
|
"cost_for_plan": 9.9886,
|
|
"sort_cost": 47,
|
|
"new_cost_for_plan": 56.989,
|
|
"chosen": true
|
|
}
|
|
] /* considered_execution_plans */
|
|
},
|
|
{
|
|
"attaching_conditions_to_tables": {
|
|
"original_condition": "((`t2`.`key2` = 5) or (`t2`.`key2` = 4) or (`t2`.`key2` = 3) or (`t2`.`key2` = 2) or (`t2`.`key2` = 1))",
|
|
"attached_conditions_computation": [
|
|
] /* attached_conditions_computation */,
|
|
"attached_conditions_summary": [
|
|
{
|
|
"table": "`t2`",
|
|
"attached": "((`t2`.`key2` = 5) or (`t2`.`key2` = 4) or (`t2`.`key2` = 3) or (`t2`.`key2` = 2) or (`t2`.`key2` = 1))"
|
|
}
|
|
] /* attached_conditions_summary */
|
|
} /* attaching_conditions_to_tables */
|
|
},
|
|
{
|
|
"optimizing_distinct_group_by_order_by": {
|
|
"simplifying_group_by": {
|
|
"original_clause": "`t2`.`key2`",
|
|
"items": [
|
|
{
|
|
"item": "`t2`.`key2`"
|
|
}
|
|
] /* items */,
|
|
"resulting_clause_is_simple": true,
|
|
"resulting_clause": "`t2`.`key2`"
|
|
} /* simplifying_group_by */
|
|
} /* optimizing_distinct_group_by_order_by */
|
|
},
|
|
{
|
|
"reconsidering_access_paths_for_index_ordering": {
|
|
"clause": "GROUP BY",
|
|
"steps": [
|
|
] /* steps */,
|
|
"index_order_summary": {
|
|
"table": "`t2`",
|
|
"index_provides_order": true,
|
|
"order_direction": "asc",
|
|
"index": "i2_1",
|
|
"plan_changed": false
|
|
} /* index_order_summary */
|
|
} /* reconsidering_access_paths_for_index_ordering */
|
|
},
|
|
{
|
|
"finalizing_table_conditions": [
|
|
{
|
|
"table": "`t2`",
|
|
"original_table_condition": "((`t2`.`key2` = 5) or (`t2`.`key2` = 4) or (`t2`.`key2` = 3) or (`t2`.`key2` = 2) or (`t2`.`key2` = 1))",
|
|
"final_table_condition ": "((`t2`.`key2` = 5) or (`t2`.`key2` = 4) or (`t2`.`key2` = 3) or (`t2`.`key2` = 2) or (`t2`.`key2` = 1))"
|
|
}
|
|
] /* finalizing_table_conditions */
|
|
},
|
|
{
|
|
"refine_plan": [
|
|
{
|
|
"table": "`t2`"
|
|
}
|
|
] /* refine_plan */
|
|
},
|
|
{
|
|
"considering_tmp_tables": [
|
|
] /* considering_tmp_tables */
|
|
}
|
|
] /* steps */
|
|
} /* join_optimization */
|
|
},
|
|
{
|
|
"join_explain": {
|
|
"select#": 1,
|
|
"steps": [
|
|
] /* steps */
|
|
} /* join_explain */
|
|
}
|
|
] /* steps */
|
|
} 0 0
|
|
|
|
EXPLAIN SELECT * FROM t2 WHERE key2 = 1 AND (key2_1 = 1 OR key3 = 5);
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t2 NULL ref i2_1,i2_2 i2_1 4 const 10 19.00 Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t2`.`key1a` AS `key1a`,`test`.`t2`.`key1b` AS `key1b`,`test`.`t2`.`key2` AS `key2`,`test`.`t2`.`key2_1` AS `key2_1`,`test`.`t2`.`key2_2` AS `key2_2`,`test`.`t2`.`key3` AS `key3` from `test`.`t2` where ((`test`.`t2`.`key2` = 1) and ((`test`.`t2`.`key2_1` = 1) or (`test`.`t2`.`key3` = 5)))
|
|
|
|
SELECT * FROM information_schema.OPTIMIZER_TRACE;
|
|
QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
|
|
EXPLAIN SELECT * FROM t2 WHERE key2 = 1 AND (key2_1 = 1 OR key3 = 5) {
|
|
"steps": [
|
|
{
|
|
"join_preparation": {
|
|
"select#": 1,
|
|
"steps": [
|
|
{
|
|
"expanded_query": "/* select#1 */ select `t2`.`key1a` AS `key1a`,`t2`.`key1b` AS `key1b`,`t2`.`key2` AS `key2`,`t2`.`key2_1` AS `key2_1`,`t2`.`key2_2` AS `key2_2`,`t2`.`key3` AS `key3` from `t2` where ((`t2`.`key2` = 1) and ((`t2`.`key2_1` = 1) or (`t2`.`key3` = 5)))"
|
|
}
|
|
] /* steps */
|
|
} /* join_preparation */
|
|
},
|
|
{
|
|
"join_optimization": {
|
|
"select#": 1,
|
|
"steps": [
|
|
{
|
|
"condition_processing": {
|
|
"condition": "WHERE",
|
|
"original_condition": "((`t2`.`key2` = 1) and ((`t2`.`key2_1` = 1) or (`t2`.`key3` = 5)))",
|
|
"steps": [
|
|
{
|
|
"transformation": "equality_propagation",
|
|
"resulting_condition": "((multiple equal(1, `t2`.`key2_1`) or multiple equal(5, `t2`.`key3`)) and multiple equal(1, `t2`.`key2`))"
|
|
},
|
|
{
|
|
"transformation": "constant_propagation",
|
|
"resulting_condition": "((multiple equal(1, `t2`.`key2_1`) or multiple equal(5, `t2`.`key3`)) and multiple equal(1, `t2`.`key2`))"
|
|
},
|
|
{
|
|
"transformation": "trivial_condition_removal",
|
|
"resulting_condition": "((multiple equal(1, `t2`.`key2_1`) or multiple equal(5, `t2`.`key3`)) and multiple equal(1, `t2`.`key2`))"
|
|
}
|
|
] /* steps */
|
|
} /* condition_processing */
|
|
},
|
|
{
|
|
"substitute_generated_columns": {
|
|
} /* substitute_generated_columns */
|
|
},
|
|
{
|
|
"table_dependencies": [
|
|
{
|
|
"table": "`t2`",
|
|
"row_may_be_null": false,
|
|
"map_bit": 0,
|
|
"depends_on_map_bits": [
|
|
] /* depends_on_map_bits */
|
|
}
|
|
] /* table_dependencies */
|
|
},
|
|
{
|
|
"ref_optimizer_key_uses": [
|
|
{
|
|
"table": "`t2`",
|
|
"field": "key2",
|
|
"equals": "1",
|
|
"null_rejecting": false
|
|
},
|
|
{
|
|
"table": "`t2`",
|
|
"field": "key2",
|
|
"equals": "1",
|
|
"null_rejecting": false
|
|
}
|
|
] /* ref_optimizer_key_uses */
|
|
},
|
|
{
|
|
"rows_estimation": [
|
|
{
|
|
"table": "`t2`",
|
|
"range_analysis": {
|
|
"table_scan": {
|
|
"rows": 1024,
|
|
"cost": 106.56
|
|
} /* table_scan */,
|
|
"potential_range_indexes": [
|
|
{
|
|
"index": "PRIMARY",
|
|
"usable": false,
|
|
"cause": "not_applicable"
|
|
},
|
|
{
|
|
"index": "i1b",
|
|
"usable": false,
|
|
"cause": "not_applicable"
|
|
},
|
|
{
|
|
"index": "i2_1",
|
|
"usable": true,
|
|
"key_parts": [
|
|
"key2",
|
|
"key2_1"
|
|
] /* key_parts */
|
|
},
|
|
{
|
|
"index": "i2_2",
|
|
"usable": true,
|
|
"key_parts": [
|
|
"key2",
|
|
"key2_1"
|
|
] /* key_parts */
|
|
}
|
|
] /* potential_range_indexes */,
|
|
"setup_range_conditions": [
|
|
] /* setup_range_conditions */,
|
|
"group_index_range": {
|
|
"chosen": false,
|
|
"cause": "not_group_by_or_distinct"
|
|
} /* group_index_range */,
|
|
"skip_scan_range": {
|
|
"potential_skip_scan_indexes": [
|
|
{
|
|
"index": "i2_1",
|
|
"usable": false,
|
|
"cause": "query_references_nonkey_column"
|
|
},
|
|
{
|
|
"index": "i2_2",
|
|
"usable": false,
|
|
"cause": "query_references_nonkey_column"
|
|
}
|
|
] /* potential_skip_scan_indexes */
|
|
} /* skip_scan_range */,
|
|
"analyzing_range_alternatives": {
|
|
"range_scan_alternatives": [
|
|
{
|
|
"index": "i2_1",
|
|
"ranges": [
|
|
"1 <= key2 <= 1"
|
|
] /* ranges */,
|
|
"index_dives_for_eq_ranges": true,
|
|
"rowid_ordered": false,
|
|
"using_mrr": false,
|
|
"index_only": false,
|
|
"rows": 10,
|
|
"cost": 3.76,
|
|
"chosen": true
|
|
},
|
|
{
|
|
"index": "i2_2",
|
|
"ranges": [
|
|
"1 <= key2 <= 1"
|
|
] /* ranges */,
|
|
"index_dives_for_eq_ranges": true,
|
|
"rowid_ordered": false,
|
|
"using_mrr": false,
|
|
"index_only": false,
|
|
"rows": 10,
|
|
"cost": 3.76,
|
|
"chosen": false,
|
|
"cause": "cost"
|
|
}
|
|
] /* range_scan_alternatives */,
|
|
"analyzing_roworder_intersect": {
|
|
"usable": false,
|
|
"cause": "too_few_roworder_scans"
|
|
} /* analyzing_roworder_intersect */
|
|
} /* analyzing_range_alternatives */,
|
|
"chosen_range_access_summary": {
|
|
"range_access_plan": {
|
|
"type": "range_scan",
|
|
"index": "i2_1",
|
|
"rows": 10,
|
|
"ranges": [
|
|
"1 <= key2 <= 1"
|
|
] /* ranges */
|
|
} /* range_access_plan */,
|
|
"rows_for_plan": 10,
|
|
"cost_for_plan": 3.76,
|
|
"chosen": true
|
|
} /* chosen_range_access_summary */
|
|
} /* range_analysis */
|
|
}
|
|
] /* rows_estimation */
|
|
},
|
|
{
|
|
"considered_execution_plans": [
|
|
{
|
|
"plan_prefix": [
|
|
] /* plan_prefix */,
|
|
"table": "`t2`",
|
|
"best_access_path": {
|
|
"considered_access_paths": [
|
|
{
|
|
"access_type": "ref",
|
|
"index": "i2_1",
|
|
"rows": 10,
|
|
"cost": 3.5,
|
|
"chosen": true
|
|
},
|
|
{
|
|
"access_type": "ref",
|
|
"index": "i2_2",
|
|
"rows": 10,
|
|
"cost": 3.5,
|
|
"chosen": false
|
|
},
|
|
{
|
|
"access_type": "range",
|
|
"range_details": {
|
|
"used_index": "i2_1"
|
|
} /* range_details */,
|
|
"chosen": false,
|
|
"cause": "heuristic_index_cheaper"
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */,
|
|
"condition_filtering_pct": 19,
|
|
"rows_for_plan": 1.9,
|
|
"cost_for_plan": 3.5,
|
|
"chosen": true
|
|
}
|
|
] /* considered_execution_plans */
|
|
},
|
|
{
|
|
"attaching_conditions_to_tables": {
|
|
"original_condition": "((`t2`.`key2` = 1) and ((`t2`.`key2_1` = 1) or (`t2`.`key3` = 5)))",
|
|
"attached_conditions_computation": [
|
|
] /* attached_conditions_computation */,
|
|
"attached_conditions_summary": [
|
|
{
|
|
"table": "`t2`",
|
|
"attached": "((`t2`.`key2` = 1) and ((`t2`.`key2_1` = 1) or (`t2`.`key3` = 5)))"
|
|
}
|
|
] /* attached_conditions_summary */
|
|
} /* attaching_conditions_to_tables */
|
|
},
|
|
{
|
|
"finalizing_table_conditions": [
|
|
{
|
|
"table": "`t2`",
|
|
"original_table_condition": "((`t2`.`key2` = 1) and ((`t2`.`key2_1` = 1) or (`t2`.`key3` = 5)))",
|
|
"final_table_condition ": "((`t2`.`key2_1` = 1) or (`t2`.`key3` = 5))"
|
|
}
|
|
] /* finalizing_table_conditions */
|
|
},
|
|
{
|
|
"refine_plan": [
|
|
{
|
|
"table": "`t2`"
|
|
}
|
|
] /* refine_plan */
|
|
}
|
|
] /* steps */
|
|
} /* join_optimization */
|
|
},
|
|
{
|
|
"join_explain": {
|
|
"select#": 1,
|
|
"steps": [
|
|
] /* steps */
|
|
} /* join_explain */
|
|
}
|
|
] /* steps */
|
|
} 0 0
|
|
|
|
EXPLAIN SELECT * FROM t1 WHERE key2=10 OR key3=3 OR key4 <=> null;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 NULL index_merge i2,i3,i4 i2,i3 4,4 NULL 2 100.00 Using union(i2,i3); Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`key1` AS `key1`,`test`.`t1`.`key2` AS `key2`,`test`.`t1`.`key3` AS `key3`,`test`.`t1`.`key4` AS `key4`,`test`.`t1`.`key5` AS `key5`,`test`.`t1`.`key6` AS `key6`,`test`.`t1`.`key7` AS `key7`,`test`.`t1`.`key8` AS `key8` from `test`.`t1` where ((`test`.`t1`.`key2` = 10) or (`test`.`t1`.`key3` = 3) or (`test`.`t1`.`key4` <=> NULL))
|
|
|
|
SELECT * FROM information_schema.OPTIMIZER_TRACE;
|
|
QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
|
|
EXPLAIN SELECT * FROM t1 WHERE key2=10 OR key3=3 OR key4 <=> null {
|
|
"steps": [
|
|
{
|
|
"join_preparation": {
|
|
"select#": 1,
|
|
"steps": [
|
|
{
|
|
"expanded_query": "/* select#1 */ select `t1`.`key1` AS `key1`,`t1`.`key2` AS `key2`,`t1`.`key3` AS `key3`,`t1`.`key4` AS `key4`,`t1`.`key5` AS `key5`,`t1`.`key6` AS `key6`,`t1`.`key7` AS `key7`,`t1`.`key8` AS `key8` from `t1` where ((`t1`.`key2` = 10) or (`t1`.`key3` = 3) or (`t1`.`key4` <=> NULL))"
|
|
}
|
|
] /* steps */
|
|
} /* join_preparation */
|
|
},
|
|
{
|
|
"join_optimization": {
|
|
"select#": 1,
|
|
"steps": [
|
|
{
|
|
"condition_processing": {
|
|
"condition": "WHERE",
|
|
"original_condition": "((`t1`.`key2` = 10) or (`t1`.`key3` = 3) or (`t1`.`key4` <=> NULL))",
|
|
"steps": [
|
|
{
|
|
"transformation": "equality_propagation",
|
|
"resulting_condition": "(multiple equal(10, `t1`.`key2`) or multiple equal(3, `t1`.`key3`) or (`t1`.`key4` <=> NULL))"
|
|
},
|
|
{
|
|
"transformation": "constant_propagation",
|
|
"resulting_condition": "(multiple equal(10, `t1`.`key2`) or multiple equal(3, `t1`.`key3`) or (`t1`.`key4` <=> NULL))"
|
|
},
|
|
{
|
|
"transformation": "trivial_condition_removal",
|
|
"resulting_condition": "(multiple equal(10, `t1`.`key2`) or multiple equal(3, `t1`.`key3`) or (`t1`.`key4` <=> NULL))"
|
|
}
|
|
] /* steps */
|
|
} /* condition_processing */
|
|
},
|
|
{
|
|
"substitute_generated_columns": {
|
|
} /* substitute_generated_columns */
|
|
},
|
|
{
|
|
"table_dependencies": [
|
|
{
|
|
"table": "`t1`",
|
|
"row_may_be_null": false,
|
|
"map_bit": 0,
|
|
"depends_on_map_bits": [
|
|
] /* depends_on_map_bits */
|
|
}
|
|
] /* table_dependencies */
|
|
},
|
|
{
|
|
"ref_optimizer_key_uses": [
|
|
] /* ref_optimizer_key_uses */
|
|
},
|
|
{
|
|
"rows_estimation": [
|
|
{
|
|
"table": "`t1`",
|
|
"range_analysis": {
|
|
"table_scan": {
|
|
"rows": 1024,
|
|
"cost": 107.06
|
|
} /* table_scan */,
|
|
"potential_range_indexes": [
|
|
{
|
|
"index": "i1",
|
|
"usable": false,
|
|
"cause": "not_applicable"
|
|
},
|
|
{
|
|
"index": "i2",
|
|
"usable": true,
|
|
"key_parts": [
|
|
"key2"
|
|
] /* key_parts */
|
|
},
|
|
{
|
|
"index": "i3",
|
|
"usable": true,
|
|
"key_parts": [
|
|
"key3"
|
|
] /* key_parts */
|
|
},
|
|
{
|
|
"index": "i4",
|
|
"usable": true,
|
|
"key_parts": [
|
|
"key4"
|
|
] /* key_parts */
|
|
},
|
|
{
|
|
"index": "i5",
|
|
"usable": false,
|
|
"cause": "not_applicable"
|
|
},
|
|
{
|
|
"index": "i6",
|
|
"usable": false,
|
|
"cause": "not_applicable"
|
|
},
|
|
{
|
|
"index": "i7",
|
|
"usable": false,
|
|
"cause": "not_applicable"
|
|
},
|
|
{
|
|
"index": "i8",
|
|
"usable": false,
|
|
"cause": "not_applicable"
|
|
}
|
|
] /* potential_range_indexes */,
|
|
"setup_range_conditions": [
|
|
{
|
|
"impossible_condition": {
|
|
"cause": "null_field_in_non_null_column"
|
|
} /* impossible_condition */
|
|
}
|
|
] /* setup_range_conditions */,
|
|
"group_index_range": {
|
|
"chosen": false,
|
|
"cause": "not_group_by_or_distinct"
|
|
} /* group_index_range */,
|
|
"skip_scan_range": {
|
|
"potential_skip_scan_indexes": [
|
|
{
|
|
"index": "i2",
|
|
"usable": false,
|
|
"cause": "query_references_nonkey_column"
|
|
},
|
|
{
|
|
"index": "i3",
|
|
"usable": false,
|
|
"cause": "query_references_nonkey_column"
|
|
},
|
|
{
|
|
"index": "i4",
|
|
"usable": false,
|
|
"cause": "query_references_nonkey_column"
|
|
}
|
|
] /* potential_skip_scan_indexes */
|
|
} /* skip_scan_range */,
|
|
"analyzing_range_alternatives": {
|
|
"range_scan_alternatives": [
|
|
] /* range_scan_alternatives */,
|
|
"analyzing_roworder_intersect": {
|
|
"usable": false,
|
|
"cause": "too_few_roworder_scans"
|
|
} /* analyzing_roworder_intersect */
|
|
} /* analyzing_range_alternatives */,
|
|
"analyzing_index_merge_union": [
|
|
{
|
|
"indexes_to_merge": [
|
|
{
|
|
"range_scan_alternatives": [
|
|
{
|
|
"index": "i2",
|
|
"ranges": [
|
|
"10 <= key2 <= 10"
|
|
] /* ranges */,
|
|
"index_dives_for_eq_ranges": true,
|
|
"rowid_ordered": true,
|
|
"using_mrr": false,
|
|
"index_only": true,
|
|
"rows": 1,
|
|
"cost": 0.36,
|
|
"chosen": true
|
|
}
|
|
] /* range_scan_alternatives */,
|
|
"index_to_merge": "i2",
|
|
"cumulated_cost": 0.36
|
|
},
|
|
{
|
|
"range_scan_alternatives": [
|
|
{
|
|
"index": "i3",
|
|
"ranges": [
|
|
"3 <= key3 <= 3"
|
|
] /* ranges */,
|
|
"index_dives_for_eq_ranges": true,
|
|
"rowid_ordered": true,
|
|
"using_mrr": false,
|
|
"index_only": true,
|
|
"rows": 1,
|
|
"cost": 0.36,
|
|
"chosen": true
|
|
}
|
|
] /* range_scan_alternatives */,
|
|
"index_to_merge": "i3",
|
|
"cumulated_cost": 0.72
|
|
}
|
|
] /* indexes_to_merge */,
|
|
"cost_of_reading_ranges": 0.72,
|
|
"use_roworder_union": true,
|
|
"cause": "always_cheaper_than_not_roworder_retrieval",
|
|
"analyzing_roworder_scans": [
|
|
{
|
|
"type": "range_scan",
|
|
"index": "i2",
|
|
"rows": 1,
|
|
"ranges": [
|
|
"10 <= key2 <= 10"
|
|
] /* ranges */,
|
|
"analyzing_roworder_intersect": {
|
|
"usable": false,
|
|
"cause": "too_few_roworder_scans"
|
|
} /* analyzing_roworder_intersect */
|
|
},
|
|
{
|
|
"type": "range_scan",
|
|
"index": "i3",
|
|
"rows": 1,
|
|
"ranges": [
|
|
"3 <= key3 <= 3"
|
|
] /* ranges */,
|
|
"analyzing_roworder_intersect": {
|
|
"usable": false,
|
|
"cause": "too_few_roworder_scans"
|
|
} /* analyzing_roworder_intersect */
|
|
}
|
|
] /* analyzing_roworder_scans */,
|
|
"index_roworder_union_cost": 1.2922,
|
|
"members": 2,
|
|
"chosen": true
|
|
}
|
|
] /* analyzing_index_merge_union */,
|
|
"chosen_range_access_summary": {
|
|
"range_access_plan": {
|
|
"type": "index_roworder_union",
|
|
"union_of": [
|
|
{
|
|
"type": "range_scan",
|
|
"index": "i2",
|
|
"rows": 1,
|
|
"ranges": [
|
|
"10 <= key2 <= 10"
|
|
] /* ranges */
|
|
},
|
|
{
|
|
"type": "range_scan",
|
|
"index": "i3",
|
|
"rows": 1,
|
|
"ranges": [
|
|
"3 <= key3 <= 3"
|
|
] /* ranges */
|
|
}
|
|
] /* union_of */
|
|
} /* range_access_plan */,
|
|
"rows_for_plan": 2,
|
|
"cost_for_plan": 1.2922,
|
|
"chosen": true
|
|
} /* chosen_range_access_summary */
|
|
} /* range_analysis */
|
|
}
|
|
] /* rows_estimation */
|
|
},
|
|
{
|
|
"considered_execution_plans": [
|
|
{
|
|
"plan_prefix": [
|
|
] /* plan_prefix */,
|
|
"table": "`t1`",
|
|
"best_access_path": {
|
|
"considered_access_paths": [
|
|
{
|
|
"rows_to_scan": 2,
|
|
"filtering_effect": [
|
|
] /* filtering_effect */,
|
|
"final_filtering_effect": 1,
|
|
"access_type": "range",
|
|
"range_details": {
|
|
"used_index": "union(i2,i3)"
|
|
} /* range_details */,
|
|
"resulting_rows": 2,
|
|
"cost": 1.4922,
|
|
"chosen": true
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */,
|
|
"condition_filtering_pct": 100,
|
|
"rows_for_plan": 2,
|
|
"cost_for_plan": 1.4922,
|
|
"chosen": true
|
|
}
|
|
] /* considered_execution_plans */
|
|
},
|
|
{
|
|
"attaching_conditions_to_tables": {
|
|
"original_condition": "((`t1`.`key2` = 10) or (`t1`.`key3` = 3) or (`t1`.`key4` <=> NULL))",
|
|
"attached_conditions_computation": [
|
|
] /* attached_conditions_computation */,
|
|
"attached_conditions_summary": [
|
|
{
|
|
"table": "`t1`",
|
|
"attached": "((`t1`.`key2` = 10) or (`t1`.`key3` = 3) or (`t1`.`key4` <=> NULL))"
|
|
}
|
|
] /* attached_conditions_summary */
|
|
} /* attaching_conditions_to_tables */
|
|
},
|
|
{
|
|
"finalizing_table_conditions": [
|
|
{
|
|
"table": "`t1`",
|
|
"original_table_condition": "((`t1`.`key2` = 10) or (`t1`.`key3` = 3) or (`t1`.`key4` <=> NULL))",
|
|
"final_table_condition ": "((`t1`.`key2` = 10) or (`t1`.`key3` = 3) or (`t1`.`key4` <=> NULL))"
|
|
}
|
|
] /* finalizing_table_conditions */
|
|
},
|
|
{
|
|
"refine_plan": [
|
|
{
|
|
"table": "`t1`"
|
|
}
|
|
] /* refine_plan */
|
|
}
|
|
] /* steps */
|
|
} /* join_optimization */
|
|
},
|
|
{
|
|
"join_explain": {
|
|
"select#": 1,
|
|
"steps": [
|
|
] /* steps */
|
|
} /* join_explain */
|
|
}
|
|
] /* steps */
|
|
} 0 0
|
|
|
|
EXPLAIN SELECT * FROM t2 WHERE key2_1 < 79 OR key2 = 2;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t2 NULL ALL i2_1,i2_2 NULL NULL NULL 1024 33.98 Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t2`.`key1a` AS `key1a`,`test`.`t2`.`key1b` AS `key1b`,`test`.`t2`.`key2` AS `key2`,`test`.`t2`.`key2_1` AS `key2_1`,`test`.`t2`.`key2_2` AS `key2_2`,`test`.`t2`.`key3` AS `key3` from `test`.`t2` where ((`test`.`t2`.`key2_1` < 79) or (`test`.`t2`.`key2` = 2))
|
|
|
|
SELECT * FROM information_schema.OPTIMIZER_TRACE;
|
|
QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
|
|
EXPLAIN SELECT * FROM t2 WHERE key2_1 < 79 OR key2 = 2 {
|
|
"steps": [
|
|
{
|
|
"join_preparation": {
|
|
"select#": 1,
|
|
"steps": [
|
|
{
|
|
"expanded_query": "/* select#1 */ select `t2`.`key1a` AS `key1a`,`t2`.`key1b` AS `key1b`,`t2`.`key2` AS `key2`,`t2`.`key2_1` AS `key2_1`,`t2`.`key2_2` AS `key2_2`,`t2`.`key3` AS `key3` from `t2` where ((`t2`.`key2_1` < 79) or (`t2`.`key2` = 2))"
|
|
}
|
|
] /* steps */
|
|
} /* join_preparation */
|
|
},
|
|
{
|
|
"join_optimization": {
|
|
"select#": 1,
|
|
"steps": [
|
|
{
|
|
"condition_processing": {
|
|
"condition": "WHERE",
|
|
"original_condition": "((`t2`.`key2_1` < 79) or (`t2`.`key2` = 2))",
|
|
"steps": [
|
|
{
|
|
"transformation": "equality_propagation",
|
|
"resulting_condition": "((`t2`.`key2_1` < 79) or multiple equal(2, `t2`.`key2`))"
|
|
},
|
|
{
|
|
"transformation": "constant_propagation",
|
|
"resulting_condition": "((`t2`.`key2_1` < 79) or multiple equal(2, `t2`.`key2`))"
|
|
},
|
|
{
|
|
"transformation": "trivial_condition_removal",
|
|
"resulting_condition": "((`t2`.`key2_1` < 79) or multiple equal(2, `t2`.`key2`))"
|
|
}
|
|
] /* steps */
|
|
} /* condition_processing */
|
|
},
|
|
{
|
|
"substitute_generated_columns": {
|
|
} /* substitute_generated_columns */
|
|
},
|
|
{
|
|
"table_dependencies": [
|
|
{
|
|
"table": "`t2`",
|
|
"row_may_be_null": false,
|
|
"map_bit": 0,
|
|
"depends_on_map_bits": [
|
|
] /* depends_on_map_bits */
|
|
}
|
|
] /* table_dependencies */
|
|
},
|
|
{
|
|
"ref_optimizer_key_uses": [
|
|
] /* ref_optimizer_key_uses */
|
|
},
|
|
{
|
|
"rows_estimation": [
|
|
{
|
|
"table": "`t2`",
|
|
"range_analysis": {
|
|
"table_scan": {
|
|
"rows": 1024,
|
|
"cost": 106.56
|
|
} /* table_scan */,
|
|
"potential_range_indexes": [
|
|
{
|
|
"index": "PRIMARY",
|
|
"usable": false,
|
|
"cause": "not_applicable"
|
|
},
|
|
{
|
|
"index": "i1b",
|
|
"usable": false,
|
|
"cause": "not_applicable"
|
|
},
|
|
{
|
|
"index": "i2_1",
|
|
"usable": true,
|
|
"key_parts": [
|
|
"key2",
|
|
"key2_1"
|
|
] /* key_parts */
|
|
},
|
|
{
|
|
"index": "i2_2",
|
|
"usable": true,
|
|
"key_parts": [
|
|
"key2",
|
|
"key2_1"
|
|
] /* key_parts */
|
|
}
|
|
] /* potential_range_indexes */,
|
|
"setup_range_conditions": [
|
|
] /* setup_range_conditions */,
|
|
"range_scan_possible": false,
|
|
"cause": "condition_always_true",
|
|
"group_index_range": {
|
|
"chosen": false,
|
|
"cause": "not_group_by_or_distinct"
|
|
} /* group_index_range */,
|
|
"skip_scan_range": {
|
|
"chosen": false,
|
|
"cause": "disjuntive_predicate_present"
|
|
} /* skip_scan_range */
|
|
} /* range_analysis */
|
|
}
|
|
] /* rows_estimation */
|
|
},
|
|
{
|
|
"considered_execution_plans": [
|
|
{
|
|
"plan_prefix": [
|
|
] /* plan_prefix */,
|
|
"table": "`t2`",
|
|
"best_access_path": {
|
|
"considered_access_paths": [
|
|
{
|
|
"rows_to_scan": 1024,
|
|
"filtering_effect": [
|
|
] /* filtering_effect */,
|
|
"final_filtering_effect": 0.3398,
|
|
"access_type": "scan",
|
|
"resulting_rows": 347.97,
|
|
"cost": 104.46,
|
|
"chosen": true
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */,
|
|
"condition_filtering_pct": 100,
|
|
"rows_for_plan": 347.97,
|
|
"cost_for_plan": 104.46,
|
|
"chosen": true
|
|
}
|
|
] /* considered_execution_plans */
|
|
},
|
|
{
|
|
"attaching_conditions_to_tables": {
|
|
"original_condition": "((`t2`.`key2_1` < 79) or (`t2`.`key2` = 2))",
|
|
"attached_conditions_computation": [
|
|
] /* attached_conditions_computation */,
|
|
"attached_conditions_summary": [
|
|
{
|
|
"table": "`t2`",
|
|
"attached": "((`t2`.`key2_1` < 79) or (`t2`.`key2` = 2))"
|
|
}
|
|
] /* attached_conditions_summary */
|
|
} /* attaching_conditions_to_tables */
|
|
},
|
|
{
|
|
"finalizing_table_conditions": [
|
|
{
|
|
"table": "`t2`",
|
|
"original_table_condition": "((`t2`.`key2_1` < 79) or (`t2`.`key2` = 2))",
|
|
"final_table_condition ": "((`t2`.`key2_1` < 79) or (`t2`.`key2` = 2))"
|
|
}
|
|
] /* finalizing_table_conditions */
|
|
},
|
|
{
|
|
"refine_plan": [
|
|
{
|
|
"table": "`t2`"
|
|
}
|
|
] /* refine_plan */
|
|
}
|
|
] /* steps */
|
|
} /* join_optimization */
|
|
},
|
|
{
|
|
"join_explain": {
|
|
"select#": 1,
|
|
"steps": [
|
|
] /* steps */
|
|
} /* join_explain */
|
|
}
|
|
] /* steps */
|
|
} 0 0
|
|
|
|
EXPLAIN SELECT * FROM t2 WHERE key1a = 5 and key1b < 10;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t2 NULL range PRIMARY,i1b PRIMARY 8 NULL 1 100.00 Using index condition
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t2`.`key1a` AS `key1a`,`test`.`t2`.`key1b` AS `key1b`,`test`.`t2`.`key2` AS `key2`,`test`.`t2`.`key2_1` AS `key2_1`,`test`.`t2`.`key2_2` AS `key2_2`,`test`.`t2`.`key3` AS `key3` from `test`.`t2` where ((`test`.`t2`.`key1a` = 5) and (`test`.`t2`.`key1b` < 10))
|
|
|
|
SELECT * FROM information_schema.OPTIMIZER_TRACE;
|
|
QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
|
|
EXPLAIN SELECT * FROM t2 WHERE key1a = 5 and key1b < 10 {
|
|
"steps": [
|
|
{
|
|
"join_preparation": {
|
|
"select#": 1,
|
|
"steps": [
|
|
{
|
|
"expanded_query": "/* select#1 */ select `t2`.`key1a` AS `key1a`,`t2`.`key1b` AS `key1b`,`t2`.`key2` AS `key2`,`t2`.`key2_1` AS `key2_1`,`t2`.`key2_2` AS `key2_2`,`t2`.`key3` AS `key3` from `t2` where ((`t2`.`key1a` = 5) and (`t2`.`key1b` < 10))"
|
|
}
|
|
] /* steps */
|
|
} /* join_preparation */
|
|
},
|
|
{
|
|
"join_optimization": {
|
|
"select#": 1,
|
|
"steps": [
|
|
{
|
|
"condition_processing": {
|
|
"condition": "WHERE",
|
|
"original_condition": "((`t2`.`key1a` = 5) and (`t2`.`key1b` < 10))",
|
|
"steps": [
|
|
{
|
|
"transformation": "equality_propagation",
|
|
"resulting_condition": "((`t2`.`key1b` < 10) and multiple equal(5, `t2`.`key1a`))"
|
|
},
|
|
{
|
|
"transformation": "constant_propagation",
|
|
"resulting_condition": "((`t2`.`key1b` < 10) and multiple equal(5, `t2`.`key1a`))"
|
|
},
|
|
{
|
|
"transformation": "trivial_condition_removal",
|
|
"resulting_condition": "((`t2`.`key1b` < 10) and multiple equal(5, `t2`.`key1a`))"
|
|
}
|
|
] /* steps */
|
|
} /* condition_processing */
|
|
},
|
|
{
|
|
"substitute_generated_columns": {
|
|
} /* substitute_generated_columns */
|
|
},
|
|
{
|
|
"table_dependencies": [
|
|
{
|
|
"table": "`t2`",
|
|
"row_may_be_null": false,
|
|
"map_bit": 0,
|
|
"depends_on_map_bits": [
|
|
] /* depends_on_map_bits */
|
|
}
|
|
] /* table_dependencies */
|
|
},
|
|
{
|
|
"ref_optimizer_key_uses": [
|
|
{
|
|
"table": "`t2`",
|
|
"field": "key1a",
|
|
"equals": "5",
|
|
"null_rejecting": false
|
|
}
|
|
] /* ref_optimizer_key_uses */
|
|
},
|
|
{
|
|
"rows_estimation": [
|
|
{
|
|
"table": "`t2`",
|
|
"range_analysis": {
|
|
"table_scan": {
|
|
"rows": 1024,
|
|
"cost": 106.56
|
|
} /* table_scan */,
|
|
"potential_range_indexes": [
|
|
{
|
|
"index": "PRIMARY",
|
|
"usable": true,
|
|
"key_parts": [
|
|
"key1a",
|
|
"key1b"
|
|
] /* key_parts */
|
|
},
|
|
{
|
|
"index": "i1b",
|
|
"usable": true,
|
|
"key_parts": [
|
|
"key1b",
|
|
"key1a"
|
|
] /* key_parts */
|
|
},
|
|
{
|
|
"index": "i2_1",
|
|
"usable": false,
|
|
"cause": "not_applicable"
|
|
},
|
|
{
|
|
"index": "i2_2",
|
|
"usable": false,
|
|
"cause": "not_applicable"
|
|
}
|
|
] /* potential_range_indexes */,
|
|
"setup_range_conditions": [
|
|
] /* setup_range_conditions */,
|
|
"group_index_range": {
|
|
"chosen": false,
|
|
"cause": "not_group_by_or_distinct"
|
|
} /* group_index_range */,
|
|
"skip_scan_range": {
|
|
"potential_skip_scan_indexes": [
|
|
{
|
|
"index": "PRIMARY",
|
|
"usable": false,
|
|
"cause": "query_references_nonkey_column"
|
|
},
|
|
{
|
|
"index": "i1b",
|
|
"usable": false,
|
|
"cause": "query_references_nonkey_column"
|
|
}
|
|
] /* potential_skip_scan_indexes */
|
|
} /* skip_scan_range */,
|
|
"analyzing_range_alternatives": {
|
|
"range_scan_alternatives": [
|
|
{
|
|
"index": "PRIMARY",
|
|
"ranges": [
|
|
"5 <= key1a <= 5 AND key1b < 10"
|
|
] /* ranges */,
|
|
"index_dives_for_eq_ranges": true,
|
|
"rowid_ordered": false,
|
|
"using_mrr": false,
|
|
"index_only": false,
|
|
"rows": 1,
|
|
"cost": 0.61,
|
|
"chosen": true
|
|
},
|
|
{
|
|
"index": "i1b",
|
|
"ranges": [
|
|
"key1b < 10"
|
|
] /* ranges */,
|
|
"index_dives_for_eq_ranges": true,
|
|
"rowid_ordered": false,
|
|
"using_mrr": false,
|
|
"index_only": false,
|
|
"rows": 9,
|
|
"cost": 3.41,
|
|
"chosen": false,
|
|
"cause": "cost"
|
|
}
|
|
] /* range_scan_alternatives */,
|
|
"analyzing_roworder_intersect": {
|
|
"usable": false,
|
|
"cause": "too_few_roworder_scans"
|
|
} /* analyzing_roworder_intersect */
|
|
} /* analyzing_range_alternatives */,
|
|
"chosen_range_access_summary": {
|
|
"range_access_plan": {
|
|
"type": "range_scan",
|
|
"index": "PRIMARY",
|
|
"rows": 1,
|
|
"ranges": [
|
|
"5 <= key1a <= 5 AND key1b < 10"
|
|
] /* ranges */
|
|
} /* range_access_plan */,
|
|
"rows_for_plan": 1,
|
|
"cost_for_plan": 0.61,
|
|
"chosen": true
|
|
} /* chosen_range_access_summary */
|
|
} /* range_analysis */
|
|
}
|
|
] /* rows_estimation */
|
|
},
|
|
{
|
|
"considered_execution_plans": [
|
|
{
|
|
"plan_prefix": [
|
|
] /* plan_prefix */,
|
|
"table": "`t2`",
|
|
"best_access_path": {
|
|
"considered_access_paths": [
|
|
{
|
|
"access_type": "ref",
|
|
"index": "PRIMARY",
|
|
"rows": 10,
|
|
"cost": 3.56,
|
|
"chosen": true
|
|
},
|
|
{
|
|
"rows_to_scan": 1,
|
|
"filtering_effect": [
|
|
] /* filtering_effect */,
|
|
"final_filtering_effect": 1,
|
|
"access_type": "range",
|
|
"range_details": {
|
|
"used_index": "PRIMARY"
|
|
} /* range_details */,
|
|
"resulting_rows": 1,
|
|
"cost": 0.71,
|
|
"chosen": true
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */,
|
|
"condition_filtering_pct": 100,
|
|
"rows_for_plan": 1,
|
|
"cost_for_plan": 0.71,
|
|
"chosen": true
|
|
}
|
|
] /* considered_execution_plans */
|
|
},
|
|
{
|
|
"attaching_conditions_to_tables": {
|
|
"original_condition": "((`t2`.`key1a` = 5) and (`t2`.`key1b` < 10))",
|
|
"attached_conditions_computation": [
|
|
] /* attached_conditions_computation */,
|
|
"attached_conditions_summary": [
|
|
{
|
|
"table": "`t2`",
|
|
"attached": "((`t2`.`key1a` = 5) and (`t2`.`key1b` < 10))"
|
|
}
|
|
] /* attached_conditions_summary */
|
|
} /* attaching_conditions_to_tables */
|
|
},
|
|
{
|
|
"finalizing_table_conditions": [
|
|
{
|
|
"table": "`t2`",
|
|
"original_table_condition": "((`t2`.`key1a` = 5) and (`t2`.`key1b` < 10))",
|
|
"final_table_condition ": "((`t2`.`key1a` = 5) and (`t2`.`key1b` < 10))"
|
|
}
|
|
] /* finalizing_table_conditions */
|
|
},
|
|
{
|
|
"refine_plan": [
|
|
{
|
|
"table": "`t2`",
|
|
"pushed_index_condition": "((`t2`.`key1a` = 5) and (`t2`.`key1b` < 10))",
|
|
"table_condition_attached": null
|
|
}
|
|
] /* refine_plan */
|
|
}
|
|
] /* steps */
|
|
} /* join_optimization */
|
|
},
|
|
{
|
|
"join_explain": {
|
|
"select#": 1,
|
|
"steps": [
|
|
] /* steps */
|
|
} /* join_explain */
|
|
}
|
|
] /* steps */
|
|
} 0 0
|
|
|
|
EXPLAIN SELECT * FROM t2 WHERE (key1a = 5 and key1b < 10 and key1b > 2) or
|
|
(key1a = 4 and key1b < 7 and key1b > 3);
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t2 NULL range PRIMARY,i1b PRIMARY 8 NULL 2 100.00 Using index condition
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t2`.`key1a` AS `key1a`,`test`.`t2`.`key1b` AS `key1b`,`test`.`t2`.`key2` AS `key2`,`test`.`t2`.`key2_1` AS `key2_1`,`test`.`t2`.`key2_2` AS `key2_2`,`test`.`t2`.`key3` AS `key3` from `test`.`t2` where (((`test`.`t2`.`key1a` = 5) and (`test`.`t2`.`key1b` < 10) and (`test`.`t2`.`key1b` > 2)) or ((`test`.`t2`.`key1a` = 4) and (`test`.`t2`.`key1b` < 7) and (`test`.`t2`.`key1b` > 3)))
|
|
|
|
SELECT * FROM information_schema.OPTIMIZER_TRACE;
|
|
QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
|
|
EXPLAIN SELECT * FROM t2 WHERE (key1a = 5 and key1b < 10 and key1b > 2) or
|
|
(key1a = 4 and key1b < 7 and key1b > 3) {
|
|
"steps": [
|
|
{
|
|
"join_preparation": {
|
|
"select#": 1,
|
|
"steps": [
|
|
{
|
|
"expanded_query": "/* select#1 */ select `t2`.`key1a` AS `key1a`,`t2`.`key1b` AS `key1b`,`t2`.`key2` AS `key2`,`t2`.`key2_1` AS `key2_1`,`t2`.`key2_2` AS `key2_2`,`t2`.`key3` AS `key3` from `t2` where (((`t2`.`key1a` = 5) and (`t2`.`key1b` < 10) and (`t2`.`key1b` > 2)) or ((`t2`.`key1a` = 4) and (`t2`.`key1b` < 7) and (`t2`.`key1b` > 3)))"
|
|
}
|
|
] /* steps */
|
|
} /* join_preparation */
|
|
},
|
|
{
|
|
"join_optimization": {
|
|
"select#": 1,
|
|
"steps": [
|
|
{
|
|
"condition_processing": {
|
|
"condition": "WHERE",
|
|
"original_condition": "(((`t2`.`key1a` = 5) and (`t2`.`key1b` < 10) and (`t2`.`key1b` > 2)) or ((`t2`.`key1a` = 4) and (`t2`.`key1b` < 7) and (`t2`.`key1b` > 3)))",
|
|
"steps": [
|
|
{
|
|
"transformation": "equality_propagation",
|
|
"resulting_condition": "(((`t2`.`key1b` < 10) and (`t2`.`key1b` > 2) and multiple equal(5, `t2`.`key1a`)) or ((`t2`.`key1b` < 7) and (`t2`.`key1b` > 3) and multiple equal(4, `t2`.`key1a`)))"
|
|
},
|
|
{
|
|
"transformation": "constant_propagation",
|
|
"resulting_condition": "(((`t2`.`key1b` < 10) and (`t2`.`key1b` > 2) and multiple equal(5, `t2`.`key1a`)) or ((`t2`.`key1b` < 7) and (`t2`.`key1b` > 3) and multiple equal(4, `t2`.`key1a`)))"
|
|
},
|
|
{
|
|
"transformation": "trivial_condition_removal",
|
|
"resulting_condition": "(((`t2`.`key1b` < 10) and (`t2`.`key1b` > 2) and multiple equal(5, `t2`.`key1a`)) or ((`t2`.`key1b` < 7) and (`t2`.`key1b` > 3) and multiple equal(4, `t2`.`key1a`)))"
|
|
}
|
|
] /* steps */
|
|
} /* condition_processing */
|
|
},
|
|
{
|
|
"substitute_generated_columns": {
|
|
} /* substitute_generated_columns */
|
|
},
|
|
{
|
|
"table_dependencies": [
|
|
{
|
|
"table": "`t2`",
|
|
"row_may_be_null": false,
|
|
"map_bit": 0,
|
|
"depends_on_map_bits": [
|
|
] /* depends_on_map_bits */
|
|
}
|
|
] /* table_dependencies */
|
|
},
|
|
{
|
|
"ref_optimizer_key_uses": [
|
|
] /* ref_optimizer_key_uses */
|
|
},
|
|
{
|
|
"rows_estimation": [
|
|
{
|
|
"table": "`t2`",
|
|
"range_analysis": {
|
|
"table_scan": {
|
|
"rows": 1024,
|
|
"cost": 106.56
|
|
} /* table_scan */,
|
|
"potential_range_indexes": [
|
|
{
|
|
"index": "PRIMARY",
|
|
"usable": true,
|
|
"key_parts": [
|
|
"key1a",
|
|
"key1b"
|
|
] /* key_parts */
|
|
},
|
|
{
|
|
"index": "i1b",
|
|
"usable": true,
|
|
"key_parts": [
|
|
"key1b",
|
|
"key1a"
|
|
] /* key_parts */
|
|
},
|
|
{
|
|
"index": "i2_1",
|
|
"usable": false,
|
|
"cause": "not_applicable"
|
|
},
|
|
{
|
|
"index": "i2_2",
|
|
"usable": false,
|
|
"cause": "not_applicable"
|
|
}
|
|
] /* potential_range_indexes */,
|
|
"setup_range_conditions": [
|
|
] /* setup_range_conditions */,
|
|
"group_index_range": {
|
|
"chosen": false,
|
|
"cause": "not_group_by_or_distinct"
|
|
} /* group_index_range */,
|
|
"skip_scan_range": {
|
|
"potential_skip_scan_indexes": [
|
|
{
|
|
"index": "PRIMARY",
|
|
"usable": false,
|
|
"cause": "query_references_nonkey_column"
|
|
},
|
|
{
|
|
"index": "i1b",
|
|
"usable": false,
|
|
"cause": "query_references_nonkey_column"
|
|
}
|
|
] /* potential_skip_scan_indexes */
|
|
} /* skip_scan_range */,
|
|
"analyzing_range_alternatives": {
|
|
"range_scan_alternatives": [
|
|
{
|
|
"index": "PRIMARY",
|
|
"ranges": [
|
|
"4 <= key1a <= 4 AND 3 < key1b < 7",
|
|
"5 <= key1a <= 5 AND 2 < key1b < 10"
|
|
] /* ranges */,
|
|
"index_dives_for_eq_ranges": true,
|
|
"rowid_ordered": false,
|
|
"using_mrr": false,
|
|
"index_only": false,
|
|
"rows": 2,
|
|
"cost": 1.21,
|
|
"chosen": true
|
|
},
|
|
{
|
|
"index": "i1b",
|
|
"ranges": [
|
|
"2 < key1b <= 3",
|
|
"3 < key1b < 7",
|
|
"7 <= key1b < 10"
|
|
] /* ranges */,
|
|
"index_dives_for_eq_ranges": true,
|
|
"rowid_ordered": false,
|
|
"using_mrr": false,
|
|
"index_only": false,
|
|
"rows": 6,
|
|
"cost": 2.86,
|
|
"chosen": false,
|
|
"cause": "cost"
|
|
}
|
|
] /* range_scan_alternatives */,
|
|
"analyzing_roworder_intersect": {
|
|
"usable": false,
|
|
"cause": "too_few_roworder_scans"
|
|
} /* analyzing_roworder_intersect */
|
|
} /* analyzing_range_alternatives */,
|
|
"chosen_range_access_summary": {
|
|
"range_access_plan": {
|
|
"type": "range_scan",
|
|
"index": "PRIMARY",
|
|
"rows": 2,
|
|
"ranges": [
|
|
"4 <= key1a <= 4 AND 3 < key1b < 7",
|
|
"5 <= key1a <= 5 AND 2 < key1b < 10"
|
|
] /* ranges */
|
|
} /* range_access_plan */,
|
|
"rows_for_plan": 2,
|
|
"cost_for_plan": 1.21,
|
|
"chosen": true
|
|
} /* chosen_range_access_summary */
|
|
} /* range_analysis */
|
|
}
|
|
] /* rows_estimation */
|
|
},
|
|
{
|
|
"considered_execution_plans": [
|
|
{
|
|
"plan_prefix": [
|
|
] /* plan_prefix */,
|
|
"table": "`t2`",
|
|
"best_access_path": {
|
|
"considered_access_paths": [
|
|
{
|
|
"rows_to_scan": 2,
|
|
"filtering_effect": [
|
|
] /* filtering_effect */,
|
|
"final_filtering_effect": 1,
|
|
"access_type": "range",
|
|
"range_details": {
|
|
"used_index": "PRIMARY"
|
|
} /* range_details */,
|
|
"resulting_rows": 2,
|
|
"cost": 1.41,
|
|
"chosen": true
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */,
|
|
"condition_filtering_pct": 100,
|
|
"rows_for_plan": 2,
|
|
"cost_for_plan": 1.41,
|
|
"chosen": true
|
|
}
|
|
] /* considered_execution_plans */
|
|
},
|
|
{
|
|
"attaching_conditions_to_tables": {
|
|
"original_condition": "(((`t2`.`key1a` = 5) and (`t2`.`key1b` < 10) and (`t2`.`key1b` > 2)) or ((`t2`.`key1a` = 4) and (`t2`.`key1b` < 7) and (`t2`.`key1b` > 3)))",
|
|
"attached_conditions_computation": [
|
|
] /* attached_conditions_computation */,
|
|
"attached_conditions_summary": [
|
|
{
|
|
"table": "`t2`",
|
|
"attached": "(((`t2`.`key1a` = 5) and (`t2`.`key1b` < 10) and (`t2`.`key1b` > 2)) or ((`t2`.`key1a` = 4) and (`t2`.`key1b` < 7) and (`t2`.`key1b` > 3)))"
|
|
}
|
|
] /* attached_conditions_summary */
|
|
} /* attaching_conditions_to_tables */
|
|
},
|
|
{
|
|
"finalizing_table_conditions": [
|
|
{
|
|
"table": "`t2`",
|
|
"original_table_condition": "(((`t2`.`key1a` = 5) and (`t2`.`key1b` < 10) and (`t2`.`key1b` > 2)) or ((`t2`.`key1a` = 4) and (`t2`.`key1b` < 7) and (`t2`.`key1b` > 3)))",
|
|
"final_table_condition ": "(((`t2`.`key1a` = 5) and (`t2`.`key1b` < 10) and (`t2`.`key1b` > 2)) or ((`t2`.`key1a` = 4) and (`t2`.`key1b` < 7) and (`t2`.`key1b` > 3)))"
|
|
}
|
|
] /* finalizing_table_conditions */
|
|
},
|
|
{
|
|
"refine_plan": [
|
|
{
|
|
"table": "`t2`",
|
|
"pushed_index_condition": "(((`t2`.`key1a` = 5) and (`t2`.`key1b` < 10) and (`t2`.`key1b` > 2)) or ((`t2`.`key1a` = 4) and (`t2`.`key1b` < 7) and (`t2`.`key1b` > 3)))",
|
|
"table_condition_attached": null
|
|
}
|
|
] /* refine_plan */
|
|
}
|
|
] /* steps */
|
|
} /* join_optimization */
|
|
},
|
|
{
|
|
"join_explain": {
|
|
"select#": 1,
|
|
"steps": [
|
|
] /* steps */
|
|
} /* join_explain */
|
|
}
|
|
] /* steps */
|
|
} 0 0
|
|
|
|
EXPLAIN SELECT * FROM t2 WHERE (key1b < 10 and key1b > 7) and
|
|
(key1a = 4 or key1a = 5);
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t2 NULL range PRIMARY,i1b i1b 4 NULL 2 19.00 Using index condition
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t2`.`key1a` AS `key1a`,`test`.`t2`.`key1b` AS `key1b`,`test`.`t2`.`key2` AS `key2`,`test`.`t2`.`key2_1` AS `key2_1`,`test`.`t2`.`key2_2` AS `key2_2`,`test`.`t2`.`key3` AS `key3` from `test`.`t2` where ((`test`.`t2`.`key1b` < 10) and (`test`.`t2`.`key1b` > 7) and ((`test`.`t2`.`key1a` = 4) or (`test`.`t2`.`key1a` = 5)))
|
|
|
|
SELECT * FROM information_schema.OPTIMIZER_TRACE;
|
|
QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
|
|
EXPLAIN SELECT * FROM t2 WHERE (key1b < 10 and key1b > 7) and
|
|
(key1a = 4 or key1a = 5) {
|
|
"steps": [
|
|
{
|
|
"join_preparation": {
|
|
"select#": 1,
|
|
"steps": [
|
|
{
|
|
"expanded_query": "/* select#1 */ select `t2`.`key1a` AS `key1a`,`t2`.`key1b` AS `key1b`,`t2`.`key2` AS `key2`,`t2`.`key2_1` AS `key2_1`,`t2`.`key2_2` AS `key2_2`,`t2`.`key3` AS `key3` from `t2` where ((`t2`.`key1b` < 10) and (`t2`.`key1b` > 7) and ((`t2`.`key1a` = 4) or (`t2`.`key1a` = 5)))"
|
|
}
|
|
] /* steps */
|
|
} /* join_preparation */
|
|
},
|
|
{
|
|
"join_optimization": {
|
|
"select#": 1,
|
|
"steps": [
|
|
{
|
|
"condition_processing": {
|
|
"condition": "WHERE",
|
|
"original_condition": "((`t2`.`key1b` < 10) and (`t2`.`key1b` > 7) and ((`t2`.`key1a` = 4) or (`t2`.`key1a` = 5)))",
|
|
"steps": [
|
|
{
|
|
"transformation": "equality_propagation",
|
|
"resulting_condition": "((`t2`.`key1b` < 10) and (`t2`.`key1b` > 7) and (multiple equal(4, `t2`.`key1a`) or multiple equal(5, `t2`.`key1a`)))"
|
|
},
|
|
{
|
|
"transformation": "constant_propagation",
|
|
"resulting_condition": "((`t2`.`key1b` < 10) and (`t2`.`key1b` > 7) and (multiple equal(4, `t2`.`key1a`) or multiple equal(5, `t2`.`key1a`)))"
|
|
},
|
|
{
|
|
"transformation": "trivial_condition_removal",
|
|
"resulting_condition": "((`t2`.`key1b` < 10) and (`t2`.`key1b` > 7) and (multiple equal(4, `t2`.`key1a`) or multiple equal(5, `t2`.`key1a`)))"
|
|
}
|
|
] /* steps */
|
|
} /* condition_processing */
|
|
},
|
|
{
|
|
"substitute_generated_columns": {
|
|
} /* substitute_generated_columns */
|
|
},
|
|
{
|
|
"table_dependencies": [
|
|
{
|
|
"table": "`t2`",
|
|
"row_may_be_null": false,
|
|
"map_bit": 0,
|
|
"depends_on_map_bits": [
|
|
] /* depends_on_map_bits */
|
|
}
|
|
] /* table_dependencies */
|
|
},
|
|
{
|
|
"ref_optimizer_key_uses": [
|
|
] /* ref_optimizer_key_uses */
|
|
},
|
|
{
|
|
"rows_estimation": [
|
|
{
|
|
"table": "`t2`",
|
|
"range_analysis": {
|
|
"table_scan": {
|
|
"rows": 1024,
|
|
"cost": 106.56
|
|
} /* table_scan */,
|
|
"potential_range_indexes": [
|
|
{
|
|
"index": "PRIMARY",
|
|
"usable": true,
|
|
"key_parts": [
|
|
"key1a",
|
|
"key1b"
|
|
] /* key_parts */
|
|
},
|
|
{
|
|
"index": "i1b",
|
|
"usable": true,
|
|
"key_parts": [
|
|
"key1b",
|
|
"key1a"
|
|
] /* key_parts */
|
|
},
|
|
{
|
|
"index": "i2_1",
|
|
"usable": false,
|
|
"cause": "not_applicable"
|
|
},
|
|
{
|
|
"index": "i2_2",
|
|
"usable": false,
|
|
"cause": "not_applicable"
|
|
}
|
|
] /* potential_range_indexes */,
|
|
"setup_range_conditions": [
|
|
] /* setup_range_conditions */,
|
|
"group_index_range": {
|
|
"chosen": false,
|
|
"cause": "not_group_by_or_distinct"
|
|
} /* group_index_range */,
|
|
"skip_scan_range": {
|
|
"potential_skip_scan_indexes": [
|
|
{
|
|
"index": "PRIMARY",
|
|
"usable": false,
|
|
"cause": "query_references_nonkey_column"
|
|
},
|
|
{
|
|
"index": "i1b",
|
|
"usable": false,
|
|
"cause": "query_references_nonkey_column"
|
|
}
|
|
] /* potential_skip_scan_indexes */
|
|
} /* skip_scan_range */,
|
|
"analyzing_range_alternatives": {
|
|
"range_scan_alternatives": [
|
|
{
|
|
"index": "PRIMARY",
|
|
"ranges": [
|
|
"4 <= key1a <= 4 AND 7 < key1b < 10",
|
|
"5 <= key1a <= 5 AND 7 < key1b < 10"
|
|
] /* ranges */,
|
|
"index_dives_for_eq_ranges": true,
|
|
"rowid_ordered": false,
|
|
"using_mrr": false,
|
|
"index_only": false,
|
|
"rows": 2,
|
|
"cost": 1.21,
|
|
"chosen": true
|
|
},
|
|
{
|
|
"index": "i1b",
|
|
"ranges": [
|
|
"7 < key1b < 10"
|
|
] /* ranges */,
|
|
"index_dives_for_eq_ranges": true,
|
|
"rowid_ordered": false,
|
|
"using_mrr": false,
|
|
"index_only": false,
|
|
"rows": 2,
|
|
"cost": 0.96,
|
|
"chosen": true
|
|
}
|
|
] /* range_scan_alternatives */,
|
|
"analyzing_roworder_intersect": {
|
|
"usable": false,
|
|
"cause": "too_few_roworder_scans"
|
|
} /* analyzing_roworder_intersect */
|
|
} /* analyzing_range_alternatives */,
|
|
"chosen_range_access_summary": {
|
|
"range_access_plan": {
|
|
"type": "range_scan",
|
|
"index": "i1b",
|
|
"rows": 2,
|
|
"ranges": [
|
|
"7 < key1b < 10"
|
|
] /* ranges */
|
|
} /* range_access_plan */,
|
|
"rows_for_plan": 2,
|
|
"cost_for_plan": 0.96,
|
|
"chosen": true
|
|
} /* chosen_range_access_summary */
|
|
} /* range_analysis */
|
|
}
|
|
] /* rows_estimation */
|
|
},
|
|
{
|
|
"considered_execution_plans": [
|
|
{
|
|
"plan_prefix": [
|
|
] /* plan_prefix */,
|
|
"table": "`t2`",
|
|
"best_access_path": {
|
|
"considered_access_paths": [
|
|
{
|
|
"rows_to_scan": 2,
|
|
"filtering_effect": [
|
|
] /* filtering_effect */,
|
|
"final_filtering_effect": 0.19,
|
|
"access_type": "range",
|
|
"range_details": {
|
|
"used_index": "i1b"
|
|
} /* range_details */,
|
|
"resulting_rows": 0.38,
|
|
"cost": 1.16,
|
|
"chosen": true
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */,
|
|
"condition_filtering_pct": 100,
|
|
"rows_for_plan": 0.38,
|
|
"cost_for_plan": 1.16,
|
|
"chosen": true
|
|
}
|
|
] /* considered_execution_plans */
|
|
},
|
|
{
|
|
"attaching_conditions_to_tables": {
|
|
"original_condition": "((`t2`.`key1b` < 10) and (`t2`.`key1b` > 7) and ((`t2`.`key1a` = 4) or (`t2`.`key1a` = 5)))",
|
|
"attached_conditions_computation": [
|
|
] /* attached_conditions_computation */,
|
|
"attached_conditions_summary": [
|
|
{
|
|
"table": "`t2`",
|
|
"attached": "((`t2`.`key1b` < 10) and (`t2`.`key1b` > 7) and ((`t2`.`key1a` = 4) or (`t2`.`key1a` = 5)))"
|
|
}
|
|
] /* attached_conditions_summary */
|
|
} /* attaching_conditions_to_tables */
|
|
},
|
|
{
|
|
"finalizing_table_conditions": [
|
|
{
|
|
"table": "`t2`",
|
|
"original_table_condition": "((`t2`.`key1b` < 10) and (`t2`.`key1b` > 7) and ((`t2`.`key1a` = 4) or (`t2`.`key1a` = 5)))",
|
|
"final_table_condition ": "((`t2`.`key1b` < 10) and (`t2`.`key1b` > 7) and ((`t2`.`key1a` = 4) or (`t2`.`key1a` = 5)))"
|
|
}
|
|
] /* finalizing_table_conditions */
|
|
},
|
|
{
|
|
"refine_plan": [
|
|
{
|
|
"table": "`t2`",
|
|
"pushed_index_condition": "((`t2`.`key1b` < 10) and (`t2`.`key1b` > 7) and ((`t2`.`key1a` = 4) or (`t2`.`key1a` = 5)))",
|
|
"table_condition_attached": null
|
|
}
|
|
] /* refine_plan */
|
|
}
|
|
] /* steps */
|
|
} /* join_optimization */
|
|
},
|
|
{
|
|
"join_explain": {
|
|
"select#": 1,
|
|
"steps": [
|
|
] /* steps */
|
|
} /* join_explain */
|
|
}
|
|
] /* steps */
|
|
} 0 0
|
|
|
|
EXPLAIN SELECT * FROM t1 WHERE (key1 > 1 OR key2 > 2);
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 NULL ALL i1,i2 NULL NULL NULL 1024 55.55 Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`key1` AS `key1`,`test`.`t1`.`key2` AS `key2`,`test`.`t1`.`key3` AS `key3`,`test`.`t1`.`key4` AS `key4`,`test`.`t1`.`key5` AS `key5`,`test`.`t1`.`key6` AS `key6`,`test`.`t1`.`key7` AS `key7`,`test`.`t1`.`key8` AS `key8` from `test`.`t1` where ((`test`.`t1`.`key1` > 1) or (`test`.`t1`.`key2` > 2))
|
|
|
|
SELECT * FROM information_schema.OPTIMIZER_TRACE;
|
|
QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
|
|
EXPLAIN SELECT * FROM t1 WHERE (key1 > 1 OR key2 > 2) {
|
|
"steps": [
|
|
{
|
|
"join_preparation": {
|
|
"select#": 1,
|
|
"steps": [
|
|
{
|
|
"expanded_query": "/* select#1 */ select `t1`.`key1` AS `key1`,`t1`.`key2` AS `key2`,`t1`.`key3` AS `key3`,`t1`.`key4` AS `key4`,`t1`.`key5` AS `key5`,`t1`.`key6` AS `key6`,`t1`.`key7` AS `key7`,`t1`.`key8` AS `key8` from `t1` where ((`t1`.`key1` > 1) or (`t1`.`key2` > 2))"
|
|
}
|
|
] /* steps */
|
|
} /* join_preparation */
|
|
},
|
|
{
|
|
"join_optimization": {
|
|
"select#": 1,
|
|
"steps": [
|
|
{
|
|
"condition_processing": {
|
|
"condition": "WHERE",
|
|
"original_condition": "((`t1`.`key1` > 1) or (`t1`.`key2` > 2))",
|
|
"steps": [
|
|
{
|
|
"transformation": "equality_propagation",
|
|
"resulting_condition": "((`t1`.`key1` > 1) or (`t1`.`key2` > 2))"
|
|
},
|
|
{
|
|
"transformation": "constant_propagation",
|
|
"resulting_condition": "((`t1`.`key1` > 1) or (`t1`.`key2` > 2))"
|
|
},
|
|
{
|
|
"transformation": "trivial_condition_removal",
|
|
"resulting_condition": "((`t1`.`key1` > 1) or (`t1`.`key2` > 2))"
|
|
}
|
|
] /* steps */
|
|
} /* condition_processing */
|
|
},
|
|
{
|
|
"substitute_generated_columns": {
|
|
} /* substitute_generated_columns */
|
|
},
|
|
{
|
|
"table_dependencies": [
|
|
{
|
|
"table": "`t1`",
|
|
"row_may_be_null": false,
|
|
"map_bit": 0,
|
|
"depends_on_map_bits": [
|
|
] /* depends_on_map_bits */
|
|
}
|
|
] /* table_dependencies */
|
|
},
|
|
{
|
|
"ref_optimizer_key_uses": [
|
|
] /* ref_optimizer_key_uses */
|
|
},
|
|
{
|
|
"rows_estimation": [
|
|
{
|
|
"table": "`t1`",
|
|
"range_analysis": {
|
|
"table_scan": {
|
|
"rows": 1024,
|
|
"cost": 107.06
|
|
} /* table_scan */,
|
|
"potential_range_indexes": [
|
|
{
|
|
"index": "i1",
|
|
"usable": true,
|
|
"key_parts": [
|
|
"key1"
|
|
] /* key_parts */
|
|
},
|
|
{
|
|
"index": "i2",
|
|
"usable": true,
|
|
"key_parts": [
|
|
"key2"
|
|
] /* key_parts */
|
|
},
|
|
{
|
|
"index": "i3",
|
|
"usable": false,
|
|
"cause": "not_applicable"
|
|
},
|
|
{
|
|
"index": "i4",
|
|
"usable": false,
|
|
"cause": "not_applicable"
|
|
},
|
|
{
|
|
"index": "i5",
|
|
"usable": false,
|
|
"cause": "not_applicable"
|
|
},
|
|
{
|
|
"index": "i6",
|
|
"usable": false,
|
|
"cause": "not_applicable"
|
|
},
|
|
{
|
|
"index": "i7",
|
|
"usable": false,
|
|
"cause": "not_applicable"
|
|
},
|
|
{
|
|
"index": "i8",
|
|
"usable": false,
|
|
"cause": "not_applicable"
|
|
}
|
|
] /* potential_range_indexes */,
|
|
"setup_range_conditions": [
|
|
] /* setup_range_conditions */,
|
|
"group_index_range": {
|
|
"chosen": false,
|
|
"cause": "not_group_by_or_distinct"
|
|
} /* group_index_range */,
|
|
"skip_scan_range": {
|
|
"potential_skip_scan_indexes": [
|
|
{
|
|
"index": "i1",
|
|
"usable": false,
|
|
"cause": "query_references_nonkey_column"
|
|
},
|
|
{
|
|
"index": "i2",
|
|
"usable": false,
|
|
"cause": "query_references_nonkey_column"
|
|
}
|
|
] /* potential_skip_scan_indexes */
|
|
} /* skip_scan_range */,
|
|
"analyzing_range_alternatives": {
|
|
"range_scan_alternatives": [
|
|
] /* range_scan_alternatives */,
|
|
"analyzing_roworder_intersect": {
|
|
"usable": false,
|
|
"cause": "too_few_roworder_scans"
|
|
} /* analyzing_roworder_intersect */
|
|
} /* analyzing_range_alternatives */,
|
|
"analyzing_index_merge_union": [
|
|
{
|
|
"indexes_to_merge": [
|
|
{
|
|
"range_scan_alternatives": [
|
|
{
|
|
"index": "i1",
|
|
"ranges": [
|
|
"1 < key1"
|
|
] /* ranges */,
|
|
"index_dives_for_eq_ranges": true,
|
|
"rowid_ordered": false,
|
|
"using_mrr": false,
|
|
"index_only": true,
|
|
"rows": 1023,
|
|
"cost": 108,
|
|
"chosen": false,
|
|
"cause": "cost"
|
|
}
|
|
] /* range_scan_alternatives */,
|
|
"chosen": false,
|
|
"cause": "cost"
|
|
},
|
|
{
|
|
"range_scan_alternatives": [
|
|
{
|
|
"index": "i2",
|
|
"ranges": [
|
|
"2 < key2"
|
|
] /* ranges */,
|
|
"index_dives_for_eq_ranges": true,
|
|
"rowid_ordered": false,
|
|
"using_mrr": false,
|
|
"index_only": true,
|
|
"rows": 1022,
|
|
"cost": 107.89,
|
|
"chosen": false,
|
|
"cause": "cost"
|
|
}
|
|
] /* range_scan_alternatives */,
|
|
"chosen": false,
|
|
"cause": "cost"
|
|
}
|
|
] /* indexes_to_merge */,
|
|
"cost_of_reading_ranges": 0,
|
|
"chosen": false,
|
|
"cause": "cost"
|
|
}
|
|
] /* analyzing_index_merge_union */
|
|
} /* range_analysis */
|
|
}
|
|
] /* rows_estimation */
|
|
},
|
|
{
|
|
"considered_execution_plans": [
|
|
{
|
|
"plan_prefix": [
|
|
] /* plan_prefix */,
|
|
"table": "`t1`",
|
|
"best_access_path": {
|
|
"considered_access_paths": [
|
|
{
|
|
"rows_to_scan": 1024,
|
|
"filtering_effect": [
|
|
] /* filtering_effect */,
|
|
"final_filtering_effect": 0.5555,
|
|
"access_type": "scan",
|
|
"resulting_rows": 568.84,
|
|
"cost": 104.96,
|
|
"chosen": true
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */,
|
|
"condition_filtering_pct": 100,
|
|
"rows_for_plan": 568.84,
|
|
"cost_for_plan": 104.96,
|
|
"chosen": true
|
|
}
|
|
] /* considered_execution_plans */
|
|
},
|
|
{
|
|
"attaching_conditions_to_tables": {
|
|
"original_condition": "((`t1`.`key1` > 1) or (`t1`.`key2` > 2))",
|
|
"attached_conditions_computation": [
|
|
] /* attached_conditions_computation */,
|
|
"attached_conditions_summary": [
|
|
{
|
|
"table": "`t1`",
|
|
"attached": "((`t1`.`key1` > 1) or (`t1`.`key2` > 2))"
|
|
}
|
|
] /* attached_conditions_summary */
|
|
} /* attaching_conditions_to_tables */
|
|
},
|
|
{
|
|
"finalizing_table_conditions": [
|
|
{
|
|
"table": "`t1`",
|
|
"original_table_condition": "((`t1`.`key1` > 1) or (`t1`.`key2` > 2))",
|
|
"final_table_condition ": "((`t1`.`key1` > 1) or (`t1`.`key2` > 2))"
|
|
}
|
|
] /* finalizing_table_conditions */
|
|
},
|
|
{
|
|
"refine_plan": [
|
|
{
|
|
"table": "`t1`"
|
|
}
|
|
] /* refine_plan */
|
|
}
|
|
] /* steps */
|
|
} /* join_optimization */
|
|
},
|
|
{
|
|
"join_explain": {
|
|
"select#": 1,
|
|
"steps": [
|
|
] /* steps */
|
|
} /* join_explain */
|
|
}
|
|
] /* steps */
|
|
} 0 0
|
|
|
|
EXPLAIN SELECT STRAIGHT_JOIN * FROM t1, t2
|
|
WHERE t1.key1=t2.key1a AND t1.key2 > 1020;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 NULL range i1,i2 i2 4 NULL 42 100.00 Using index condition
|
|
1 SIMPLE t2 NULL ref PRIMARY PRIMARY 4 test.t1.key1 10 100.00 NULL
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select straight_join `test`.`t1`.`key1` AS `key1`,`test`.`t1`.`key2` AS `key2`,`test`.`t1`.`key3` AS `key3`,`test`.`t1`.`key4` AS `key4`,`test`.`t1`.`key5` AS `key5`,`test`.`t1`.`key6` AS `key6`,`test`.`t1`.`key7` AS `key7`,`test`.`t1`.`key8` AS `key8`,`test`.`t2`.`key1a` AS `key1a`,`test`.`t2`.`key1b` AS `key1b`,`test`.`t2`.`key2` AS `key2`,`test`.`t2`.`key2_1` AS `key2_1`,`test`.`t2`.`key2_2` AS `key2_2`,`test`.`t2`.`key3` AS `key3` from `test`.`t1` join `test`.`t2` where ((`test`.`t2`.`key1a` = `test`.`t1`.`key1`) and (`test`.`t1`.`key2` > 1020))
|
|
|
|
SELECT * FROM information_schema.OPTIMIZER_TRACE;
|
|
QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
|
|
EXPLAIN SELECT STRAIGHT_JOIN * FROM t1, t2
|
|
WHERE t1.key1=t2.key1a AND t1.key2 > 1020 {
|
|
"steps": [
|
|
{
|
|
"join_preparation": {
|
|
"select#": 1,
|
|
"steps": [
|
|
{
|
|
"expanded_query": "/* select#1 */ select straight_join `t1`.`key1` AS `key1`,`t1`.`key2` AS `key2`,`t1`.`key3` AS `key3`,`t1`.`key4` AS `key4`,`t1`.`key5` AS `key5`,`t1`.`key6` AS `key6`,`t1`.`key7` AS `key7`,`t1`.`key8` AS `key8`,`t2`.`key1a` AS `key1a`,`t2`.`key1b` AS `key1b`,`t2`.`key2` AS `key2`,`t2`.`key2_1` AS `key2_1`,`t2`.`key2_2` AS `key2_2`,`t2`.`key3` AS `key3` from `t1` join `t2` where ((`t1`.`key1` = `t2`.`key1a`) and (`t1`.`key2` > 1020))"
|
|
}
|
|
] /* steps */
|
|
} /* join_preparation */
|
|
},
|
|
{
|
|
"join_optimization": {
|
|
"select#": 1,
|
|
"steps": [
|
|
{
|
|
"condition_processing": {
|
|
"condition": "WHERE",
|
|
"original_condition": "((`t1`.`key1` = `t2`.`key1a`) and (`t1`.`key2` > 1020))",
|
|
"steps": [
|
|
{
|
|
"transformation": "equality_propagation",
|
|
"resulting_condition": "((`t1`.`key2` > 1020) and multiple equal(`t1`.`key1`, `t2`.`key1a`))"
|
|
},
|
|
{
|
|
"transformation": "constant_propagation",
|
|
"resulting_condition": "((`t1`.`key2` > 1020) and multiple equal(`t1`.`key1`, `t2`.`key1a`))"
|
|
},
|
|
{
|
|
"transformation": "trivial_condition_removal",
|
|
"resulting_condition": "((`t1`.`key2` > 1020) and multiple equal(`t1`.`key1`, `t2`.`key1a`))"
|
|
}
|
|
] /* steps */
|
|
} /* condition_processing */
|
|
},
|
|
{
|
|
"substitute_generated_columns": {
|
|
} /* substitute_generated_columns */
|
|
},
|
|
{
|
|
"table_dependencies": [
|
|
{
|
|
"table": "`t1`",
|
|
"row_may_be_null": false,
|
|
"map_bit": 0,
|
|
"depends_on_map_bits": [
|
|
] /* depends_on_map_bits */
|
|
},
|
|
{
|
|
"table": "`t2`",
|
|
"row_may_be_null": false,
|
|
"map_bit": 1,
|
|
"depends_on_map_bits": [
|
|
0
|
|
] /* depends_on_map_bits */
|
|
}
|
|
] /* table_dependencies */
|
|
},
|
|
{
|
|
"ref_optimizer_key_uses": [
|
|
{
|
|
"table": "`t1`",
|
|
"field": "key1",
|
|
"equals": "`t2`.`key1a`",
|
|
"null_rejecting": false
|
|
},
|
|
{
|
|
"table": "`t2`",
|
|
"field": "key1a",
|
|
"equals": "`t1`.`key1`",
|
|
"null_rejecting": false
|
|
}
|
|
] /* ref_optimizer_key_uses */
|
|
},
|
|
{
|
|
"rows_estimation": [
|
|
{
|
|
"table": "`t1`",
|
|
"range_analysis": {
|
|
"table_scan": {
|
|
"rows": 1024,
|
|
"cost": 107.06
|
|
} /* table_scan */,
|
|
"potential_range_indexes": [
|
|
{
|
|
"index": "i1",
|
|
"usable": false,
|
|
"cause": "not_applicable"
|
|
},
|
|
{
|
|
"index": "i2",
|
|
"usable": true,
|
|
"key_parts": [
|
|
"key2"
|
|
] /* key_parts */
|
|
},
|
|
{
|
|
"index": "i3",
|
|
"usable": false,
|
|
"cause": "not_applicable"
|
|
},
|
|
{
|
|
"index": "i4",
|
|
"usable": false,
|
|
"cause": "not_applicable"
|
|
},
|
|
{
|
|
"index": "i5",
|
|
"usable": false,
|
|
"cause": "not_applicable"
|
|
},
|
|
{
|
|
"index": "i6",
|
|
"usable": false,
|
|
"cause": "not_applicable"
|
|
},
|
|
{
|
|
"index": "i7",
|
|
"usable": false,
|
|
"cause": "not_applicable"
|
|
},
|
|
{
|
|
"index": "i8",
|
|
"usable": false,
|
|
"cause": "not_applicable"
|
|
}
|
|
] /* potential_range_indexes */,
|
|
"setup_range_conditions": [
|
|
] /* setup_range_conditions */,
|
|
"group_index_range": {
|
|
"chosen": false,
|
|
"cause": "not_single_table"
|
|
} /* group_index_range */,
|
|
"skip_scan_range": {
|
|
"chosen": false,
|
|
"cause": "not_single_table"
|
|
} /* skip_scan_range */,
|
|
"analyzing_range_alternatives": {
|
|
"range_scan_alternatives": [
|
|
{
|
|
"index": "i2",
|
|
"ranges": [
|
|
"1020 < key2"
|
|
] /* ranges */,
|
|
"index_dives_for_eq_ranges": true,
|
|
"rowid_ordered": false,
|
|
"using_mrr": false,
|
|
"index_only": false,
|
|
"rows": 42,
|
|
"cost": 14.96,
|
|
"chosen": true
|
|
}
|
|
] /* range_scan_alternatives */,
|
|
"analyzing_roworder_intersect": {
|
|
"usable": false,
|
|
"cause": "too_few_roworder_scans"
|
|
} /* analyzing_roworder_intersect */
|
|
} /* analyzing_range_alternatives */,
|
|
"chosen_range_access_summary": {
|
|
"range_access_plan": {
|
|
"type": "range_scan",
|
|
"index": "i2",
|
|
"rows": 42,
|
|
"ranges": [
|
|
"1020 < key2"
|
|
] /* ranges */
|
|
} /* range_access_plan */,
|
|
"rows_for_plan": 42,
|
|
"cost_for_plan": 14.96,
|
|
"chosen": true
|
|
} /* chosen_range_access_summary */
|
|
} /* range_analysis */
|
|
},
|
|
{
|
|
"table": "`t2`",
|
|
"table_scan": {
|
|
"rows": 1024,
|
|
"cost": 2.0625
|
|
} /* table_scan */
|
|
}
|
|
] /* rows_estimation */
|
|
},
|
|
{
|
|
"considered_execution_plans": [
|
|
{
|
|
"plan_prefix": [
|
|
] /* plan_prefix */,
|
|
"table": "`t1`",
|
|
"best_access_path": {
|
|
"considered_access_paths": [
|
|
{
|
|
"access_type": "ref",
|
|
"index": "i1",
|
|
"usable": false,
|
|
"chosen": false
|
|
},
|
|
{
|
|
"rows_to_scan": 42,
|
|
"filtering_effect": [
|
|
] /* filtering_effect */,
|
|
"final_filtering_effect": 1,
|
|
"access_type": "range",
|
|
"range_details": {
|
|
"used_index": "i2"
|
|
} /* range_details */,
|
|
"resulting_rows": 42,
|
|
"cost": 19.16,
|
|
"chosen": true
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */,
|
|
"condition_filtering_pct": 100,
|
|
"rows_for_plan": 42,
|
|
"cost_for_plan": 19.16
|
|
},
|
|
{
|
|
"plan_prefix": [
|
|
"`t1`"
|
|
] /* plan_prefix */,
|
|
"table": "`t2`",
|
|
"best_access_path": {
|
|
"considered_access_paths": [
|
|
{
|
|
"access_type": "ref",
|
|
"index": "PRIMARY",
|
|
"rows": 10,
|
|
"cost": 149.52,
|
|
"chosen": true
|
|
},
|
|
{
|
|
"rows_to_scan": 1024,
|
|
"filtering_effect": [
|
|
] /* filtering_effect */,
|
|
"final_filtering_effect": 1,
|
|
"access_type": "scan",
|
|
"using_join_cache": true,
|
|
"buffers_needed": 1,
|
|
"resulting_rows": 1024,
|
|
"cost": 4302.9,
|
|
"chosen": false
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */,
|
|
"condition_filtering_pct": 100,
|
|
"rows_for_plan": 420,
|
|
"cost_for_plan": 168.68
|
|
}
|
|
] /* considered_execution_plans */
|
|
},
|
|
{
|
|
"attaching_conditions_to_tables": {
|
|
"original_condition": "((`t2`.`key1a` = `t1`.`key1`) and (`t1`.`key2` > 1020))",
|
|
"attached_conditions_computation": [
|
|
] /* attached_conditions_computation */,
|
|
"attached_conditions_summary": [
|
|
{
|
|
"table": "`t1`",
|
|
"attached": "(`t1`.`key2` > 1020)"
|
|
},
|
|
{
|
|
"table": "`t2`",
|
|
"attached": "(`t2`.`key1a` = `t1`.`key1`)"
|
|
}
|
|
] /* attached_conditions_summary */
|
|
} /* attaching_conditions_to_tables */
|
|
},
|
|
{
|
|
"finalizing_table_conditions": [
|
|
{
|
|
"table": "`t1`",
|
|
"original_table_condition": "(`t1`.`key2` > 1020)",
|
|
"final_table_condition ": "(`t1`.`key2` > 1020)"
|
|
},
|
|
{
|
|
"table": "`t2`",
|
|
"original_table_condition": "(`t2`.`key1a` = `t1`.`key1`)",
|
|
"final_table_condition ": null
|
|
}
|
|
] /* finalizing_table_conditions */
|
|
},
|
|
{
|
|
"refine_plan": [
|
|
{
|
|
"table": "`t1`",
|
|
"pushed_index_condition": "(`t1`.`key2` > 1020)",
|
|
"table_condition_attached": null
|
|
},
|
|
{
|
|
"table": "`t2`"
|
|
}
|
|
] /* refine_plan */
|
|
}
|
|
] /* steps */
|
|
} /* join_optimization */
|
|
},
|
|
{
|
|
"join_explain": {
|
|
"select#": 1,
|
|
"steps": [
|
|
] /* steps */
|
|
} /* join_explain */
|
|
}
|
|
] /* steps */
|
|
} 0 0
|
|
DROP TABLE t1,t2;
|
|
CREATE TABLE t1 (
|
|
cola char(3) not null,
|
|
colb char(3) not null,
|
|
filler char(200),
|
|
key(cola),
|
|
key(colb)
|
|
) CHARSET utf8mb4;
|
|
INSERT INTO t1 VALUES ('foo','bar', 'ZZ'),('fuz','baz', 'ZZ');
|
|
Inserting records
|
|
|
|
EXPLAIN SELECT * FROM t1 WHERE cola = 'foo' AND colb = 'bar';
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 NULL index_merge cola,colb cola,colb 12,12 NULL 29 100.00 Using intersect(cola,colb); Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`cola` AS `cola`,`test`.`t1`.`colb` AS `colb`,`test`.`t1`.`filler` AS `filler` from `test`.`t1` where ((`test`.`t1`.`colb` = 'bar') and (`test`.`t1`.`cola` = 'foo'))
|
|
|
|
SELECT * FROM information_schema.OPTIMIZER_TRACE;
|
|
QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
|
|
EXPLAIN SELECT * FROM t1 WHERE cola = 'foo' AND colb = 'bar' {
|
|
"steps": [
|
|
{
|
|
"join_preparation": {
|
|
"select#": 1,
|
|
"steps": [
|
|
{
|
|
"expanded_query": "/* select#1 */ select `t1`.`cola` AS `cola`,`t1`.`colb` AS `colb`,`t1`.`filler` AS `filler` from `t1` where ((`t1`.`cola` = 'foo') and (`t1`.`colb` = 'bar'))"
|
|
}
|
|
] /* steps */
|
|
} /* join_preparation */
|
|
},
|
|
{
|
|
"join_optimization": {
|
|
"select#": 1,
|
|
"steps": [
|
|
{
|
|
"condition_processing": {
|
|
"condition": "WHERE",
|
|
"original_condition": "((`t1`.`cola` = 'foo') and (`t1`.`colb` = 'bar'))",
|
|
"steps": [
|
|
{
|
|
"transformation": "equality_propagation",
|
|
"resulting_condition": "(multiple equal('foo', `t1`.`cola`) and multiple equal('bar', `t1`.`colb`))"
|
|
},
|
|
{
|
|
"transformation": "constant_propagation",
|
|
"resulting_condition": "(multiple equal('foo', `t1`.`cola`) and multiple equal('bar', `t1`.`colb`))"
|
|
},
|
|
{
|
|
"transformation": "trivial_condition_removal",
|
|
"resulting_condition": "(multiple equal('foo', `t1`.`cola`) and multiple equal('bar', `t1`.`colb`))"
|
|
}
|
|
] /* steps */
|
|
} /* condition_processing */
|
|
},
|
|
{
|
|
"substitute_generated_columns": {
|
|
} /* substitute_generated_columns */
|
|
},
|
|
{
|
|
"table_dependencies": [
|
|
{
|
|
"table": "`t1`",
|
|
"row_may_be_null": false,
|
|
"map_bit": 0,
|
|
"depends_on_map_bits": [
|
|
] /* depends_on_map_bits */
|
|
}
|
|
] /* table_dependencies */
|
|
},
|
|
{
|
|
"ref_optimizer_key_uses": [
|
|
{
|
|
"table": "`t1`",
|
|
"field": "cola",
|
|
"equals": "'foo'",
|
|
"null_rejecting": false
|
|
},
|
|
{
|
|
"table": "`t1`",
|
|
"field": "colb",
|
|
"equals": "'bar'",
|
|
"null_rejecting": false
|
|
}
|
|
] /* ref_optimizer_key_uses */
|
|
},
|
|
{
|
|
"rows_estimation": [
|
|
{
|
|
"table": "`t1`",
|
|
"range_analysis": {
|
|
"table_scan": {
|
|
"rows": 8704,
|
|
"cost": 1311.3
|
|
} /* table_scan */,
|
|
"potential_range_indexes": [
|
|
{
|
|
"index": "cola",
|
|
"usable": true,
|
|
"key_parts": [
|
|
"cola"
|
|
] /* key_parts */
|
|
},
|
|
{
|
|
"index": "colb",
|
|
"usable": true,
|
|
"key_parts": [
|
|
"colb"
|
|
] /* key_parts */
|
|
}
|
|
] /* potential_range_indexes */,
|
|
"setup_range_conditions": [
|
|
] /* setup_range_conditions */,
|
|
"group_index_range": {
|
|
"chosen": false,
|
|
"cause": "not_group_by_or_distinct"
|
|
} /* group_index_range */,
|
|
"skip_scan_range": {
|
|
"potential_skip_scan_indexes": [
|
|
{
|
|
"index": "cola",
|
|
"usable": false,
|
|
"cause": "query_references_nonkey_column"
|
|
},
|
|
{
|
|
"index": "colb",
|
|
"usable": false,
|
|
"cause": "query_references_nonkey_column"
|
|
}
|
|
] /* potential_skip_scan_indexes */
|
|
} /* skip_scan_range */,
|
|
"analyzing_range_alternatives": {
|
|
"range_scan_alternatives": [
|
|
{
|
|
"index": "cola",
|
|
"ranges": [
|
|
"foo <= cola <= foo"
|
|
] /* ranges */,
|
|
"index_dives_for_eq_ranges": true,
|
|
"rowid_ordered": true,
|
|
"using_mrr": false,
|
|
"index_only": false,
|
|
"rows": 504,
|
|
"cost": 176.66,
|
|
"chosen": true
|
|
},
|
|
{
|
|
"index": "colb",
|
|
"ranges": [
|
|
"bar <= colb <= bar"
|
|
] /* ranges */,
|
|
"index_dives_for_eq_ranges": true,
|
|
"rowid_ordered": true,
|
|
"using_mrr": false,
|
|
"index_only": false,
|
|
"rows": 504,
|
|
"cost": 176.66,
|
|
"chosen": false,
|
|
"cause": "cost"
|
|
}
|
|
] /* range_scan_alternatives */,
|
|
"analyzing_roworder_intersect": {
|
|
"intersecting_indexes": [
|
|
{
|
|
"index": "cola",
|
|
"index_scan_cost": 4.9074,
|
|
"cumulated_index_scan_cost": 4.9074,
|
|
"disk_sweep_cost": 109.54,
|
|
"cumulated_total_cost": 114.45,
|
|
"usable": true,
|
|
"matching_rows_now": 504,
|
|
"isect_covering_with_this_index": false,
|
|
"chosen": true
|
|
},
|
|
{
|
|
"index": "colb",
|
|
"index_scan_cost": 4.9074,
|
|
"cumulated_index_scan_cost": 9.8148,
|
|
"disk_sweep_cost": 7.1924,
|
|
"cumulated_total_cost": 17.007,
|
|
"usable": true,
|
|
"matching_rows_now": 29.184,
|
|
"isect_covering_with_this_index": false,
|
|
"chosen": true
|
|
}
|
|
] /* intersecting_indexes */,
|
|
"clustered_pk": {
|
|
"clustered_pk_added_to_intersect": false,
|
|
"cause": "no_clustered_pk_index"
|
|
} /* clustered_pk */,
|
|
"rows": 29,
|
|
"cost": 17.007,
|
|
"covering": false,
|
|
"chosen": true
|
|
} /* analyzing_roworder_intersect */
|
|
} /* analyzing_range_alternatives */,
|
|
"chosen_range_access_summary": {
|
|
"range_access_plan": {
|
|
"type": "index_roworder_intersect",
|
|
"rows": 29,
|
|
"cost": 17.007,
|
|
"covering": false,
|
|
"clustered_pk_scan": false,
|
|
"intersect_of": [
|
|
{
|
|
"type": "range_scan",
|
|
"index": "cola",
|
|
"rows": 504,
|
|
"ranges": [
|
|
"foo <= cola <= foo"
|
|
] /* ranges */
|
|
},
|
|
{
|
|
"type": "range_scan",
|
|
"index": "colb",
|
|
"rows": 504,
|
|
"ranges": [
|
|
"bar <= colb <= bar"
|
|
] /* ranges */
|
|
}
|
|
] /* intersect_of */
|
|
} /* range_access_plan */,
|
|
"rows_for_plan": 29,
|
|
"cost_for_plan": 17.007,
|
|
"chosen": true
|
|
} /* chosen_range_access_summary */
|
|
} /* range_analysis */
|
|
}
|
|
] /* rows_estimation */
|
|
},
|
|
{
|
|
"considered_execution_plans": [
|
|
{
|
|
"plan_prefix": [
|
|
] /* plan_prefix */,
|
|
"table": "`t1`",
|
|
"best_access_path": {
|
|
"considered_access_paths": [
|
|
{
|
|
"access_type": "ref",
|
|
"index": "cola",
|
|
"rows": 504,
|
|
"cost": 176.4,
|
|
"chosen": true
|
|
},
|
|
{
|
|
"access_type": "ref",
|
|
"index": "colb",
|
|
"rows": 504,
|
|
"cost": 176.4,
|
|
"chosen": false
|
|
},
|
|
{
|
|
"rows_to_scan": 29,
|
|
"filtering_effect": [
|
|
] /* filtering_effect */,
|
|
"final_filtering_effect": 1,
|
|
"access_type": "range",
|
|
"range_details": {
|
|
"used_index": "intersect(cola,colb)"
|
|
} /* range_details */,
|
|
"resulting_rows": 29,
|
|
"cost": 19.907,
|
|
"chosen": true
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */,
|
|
"condition_filtering_pct": 100,
|
|
"rows_for_plan": 29,
|
|
"cost_for_plan": 19.907,
|
|
"chosen": true
|
|
}
|
|
] /* considered_execution_plans */
|
|
},
|
|
{
|
|
"attaching_conditions_to_tables": {
|
|
"original_condition": "((`t1`.`colb` = 'bar') and (`t1`.`cola` = 'foo'))",
|
|
"attached_conditions_computation": [
|
|
] /* attached_conditions_computation */,
|
|
"attached_conditions_summary": [
|
|
{
|
|
"table": "`t1`",
|
|
"attached": "((`t1`.`colb` = 'bar') and (`t1`.`cola` = 'foo'))"
|
|
}
|
|
] /* attached_conditions_summary */
|
|
} /* attaching_conditions_to_tables */
|
|
},
|
|
{
|
|
"finalizing_table_conditions": [
|
|
{
|
|
"table": "`t1`",
|
|
"original_table_condition": "((`t1`.`colb` = 'bar') and (`t1`.`cola` = 'foo'))",
|
|
"final_table_condition ": "((`t1`.`colb` = 'bar') and (`t1`.`cola` = 'foo'))"
|
|
}
|
|
] /* finalizing_table_conditions */
|
|
},
|
|
{
|
|
"refine_plan": [
|
|
{
|
|
"table": "`t1`"
|
|
}
|
|
] /* refine_plan */
|
|
}
|
|
] /* steps */
|
|
} /* join_optimization */
|
|
},
|
|
{
|
|
"join_explain": {
|
|
"select#": 1,
|
|
"steps": [
|
|
] /* steps */
|
|
} /* join_explain */
|
|
}
|
|
] /* steps */
|
|
} 0 0
|
|
|
|
EXPLAIN SELECT * FROM t1 WHERE cola = 'f\no';
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 NULL ref cola cola 12 const 1 100.00 Using index condition
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`cola` AS `cola`,`test`.`t1`.`colb` AS `colb`,`test`.`t1`.`filler` AS `filler` from `test`.`t1` where (`test`.`t1`.`cola` = 'f\no')
|
|
|
|
SELECT * FROM information_schema.OPTIMIZER_TRACE;
|
|
QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
|
|
EXPLAIN SELECT * FROM t1 WHERE cola = 'f\no' {
|
|
"steps": [
|
|
{
|
|
"join_preparation": {
|
|
"select#": 1,
|
|
"steps": [
|
|
{
|
|
"expanded_query": "/* select#1 */ select `t1`.`cola` AS `cola`,`t1`.`colb` AS `colb`,`t1`.`filler` AS `filler` from `t1` where (`t1`.`cola` = 'f\\no')"
|
|
}
|
|
] /* steps */
|
|
} /* join_preparation */
|
|
},
|
|
{
|
|
"join_optimization": {
|
|
"select#": 1,
|
|
"steps": [
|
|
{
|
|
"condition_processing": {
|
|
"condition": "WHERE",
|
|
"original_condition": "(`t1`.`cola` = 'f\\no')",
|
|
"steps": [
|
|
{
|
|
"transformation": "equality_propagation",
|
|
"resulting_condition": "multiple equal('f\\no', `t1`.`cola`)"
|
|
},
|
|
{
|
|
"transformation": "constant_propagation",
|
|
"resulting_condition": "multiple equal('f\\no', `t1`.`cola`)"
|
|
},
|
|
{
|
|
"transformation": "trivial_condition_removal",
|
|
"resulting_condition": "multiple equal('f\\no', `t1`.`cola`)"
|
|
}
|
|
] /* steps */
|
|
} /* condition_processing */
|
|
},
|
|
{
|
|
"substitute_generated_columns": {
|
|
} /* substitute_generated_columns */
|
|
},
|
|
{
|
|
"table_dependencies": [
|
|
{
|
|
"table": "`t1`",
|
|
"row_may_be_null": false,
|
|
"map_bit": 0,
|
|
"depends_on_map_bits": [
|
|
] /* depends_on_map_bits */
|
|
}
|
|
] /* table_dependencies */
|
|
},
|
|
{
|
|
"ref_optimizer_key_uses": [
|
|
{
|
|
"table": "`t1`",
|
|
"field": "cola",
|
|
"equals": "'f\\no'",
|
|
"null_rejecting": false
|
|
}
|
|
] /* ref_optimizer_key_uses */
|
|
},
|
|
{
|
|
"rows_estimation": [
|
|
{
|
|
"table": "`t1`",
|
|
"range_analysis": {
|
|
"table_scan": {
|
|
"rows": 8704,
|
|
"cost": 1311.3
|
|
} /* table_scan */,
|
|
"potential_range_indexes": [
|
|
{
|
|
"index": "cola",
|
|
"usable": true,
|
|
"key_parts": [
|
|
"cola"
|
|
] /* key_parts */
|
|
},
|
|
{
|
|
"index": "colb",
|
|
"usable": false,
|
|
"cause": "not_applicable"
|
|
}
|
|
] /* potential_range_indexes */,
|
|
"setup_range_conditions": [
|
|
] /* setup_range_conditions */,
|
|
"group_index_range": {
|
|
"chosen": false,
|
|
"cause": "not_group_by_or_distinct"
|
|
} /* group_index_range */,
|
|
"skip_scan_range": {
|
|
"potential_skip_scan_indexes": [
|
|
{
|
|
"index": "cola",
|
|
"usable": false,
|
|
"cause": "query_references_nonkey_column"
|
|
}
|
|
] /* potential_skip_scan_indexes */
|
|
} /* skip_scan_range */,
|
|
"analyzing_range_alternatives": {
|
|
"range_scan_alternatives": [
|
|
{
|
|
"index": "cola",
|
|
"ranges": [
|
|
"f\no <= cola <= f\no"
|
|
] /* ranges */,
|
|
"index_dives_for_eq_ranges": true,
|
|
"rowid_ordered": true,
|
|
"using_mrr": false,
|
|
"index_only": false,
|
|
"rows": 1,
|
|
"cost": 0.61,
|
|
"chosen": true
|
|
}
|
|
] /* range_scan_alternatives */,
|
|
"analyzing_roworder_intersect": {
|
|
"usable": false,
|
|
"cause": "too_few_roworder_scans"
|
|
} /* analyzing_roworder_intersect */
|
|
} /* analyzing_range_alternatives */,
|
|
"chosen_range_access_summary": {
|
|
"range_access_plan": {
|
|
"type": "range_scan",
|
|
"index": "cola",
|
|
"rows": 1,
|
|
"ranges": [
|
|
"f\no <= cola <= f\no"
|
|
] /* ranges */
|
|
} /* range_access_plan */,
|
|
"rows_for_plan": 1,
|
|
"cost_for_plan": 0.61,
|
|
"chosen": true
|
|
} /* chosen_range_access_summary */
|
|
} /* range_analysis */
|
|
}
|
|
] /* rows_estimation */
|
|
},
|
|
{
|
|
"considered_execution_plans": [
|
|
{
|
|
"plan_prefix": [
|
|
] /* plan_prefix */,
|
|
"table": "`t1`",
|
|
"best_access_path": {
|
|
"considered_access_paths": [
|
|
{
|
|
"access_type": "ref",
|
|
"index": "cola",
|
|
"rows": 1,
|
|
"cost": 0.35,
|
|
"chosen": true
|
|
},
|
|
{
|
|
"access_type": "range",
|
|
"range_details": {
|
|
"used_index": "cola"
|
|
} /* range_details */,
|
|
"chosen": false,
|
|
"cause": "heuristic_index_cheaper"
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */,
|
|
"condition_filtering_pct": 100,
|
|
"rows_for_plan": 1,
|
|
"cost_for_plan": 0.35,
|
|
"chosen": true
|
|
}
|
|
] /* considered_execution_plans */
|
|
},
|
|
{
|
|
"attaching_conditions_to_tables": {
|
|
"original_condition": "(`t1`.`cola` = 'f\\no')",
|
|
"attached_conditions_computation": [
|
|
] /* attached_conditions_computation */,
|
|
"attached_conditions_summary": [
|
|
{
|
|
"table": "`t1`",
|
|
"attached": "(`t1`.`cola` = 'f\\no')"
|
|
}
|
|
] /* attached_conditions_summary */
|
|
} /* attaching_conditions_to_tables */
|
|
},
|
|
{
|
|
"finalizing_table_conditions": [
|
|
{
|
|
"table": "`t1`",
|
|
"original_table_condition": "(`t1`.`cola` = 'f\\no')",
|
|
"final_table_condition ": "(`t1`.`cola` = 'f\\no')"
|
|
}
|
|
] /* finalizing_table_conditions */
|
|
},
|
|
{
|
|
"refine_plan": [
|
|
{
|
|
"table": "`t1`",
|
|
"pushed_index_condition": "(`t1`.`cola` = 'f\\no')",
|
|
"table_condition_attached": null
|
|
}
|
|
] /* refine_plan */
|
|
}
|
|
] /* steps */
|
|
} /* join_optimization */
|
|
},
|
|
{
|
|
"join_explain": {
|
|
"select#": 1,
|
|
"steps": [
|
|
] /* steps */
|
|
} /* join_explain */
|
|
}
|
|
] /* steps */
|
|
} 0 0
|
|
DROP TABLE t1;
|
|
CREATE TABLE t1(c INT);
|
|
INSERT INTO t1 VALUES (),();
|
|
CREATE TABLE t2 (b INT, KEY(b));
|
|
INSERT INTO t2 VALUES (),(),();
|
|
SET optimizer_trace_features="greedy_search=off,dynamic_range=on";
|
|
EXPLAIN SELECT 1 FROM
|
|
(SELECT 1 FROM t2,t1 WHERE b < c GROUP BY 1 LIMIT 1) AS d2;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table
|
|
2 DERIVED t1 NULL ALL NULL NULL NULL NULL 2 100.00 NULL
|
|
2 DERIVED t2 NULL ALL b NULL NULL NULL 3 33.33 Range checked for each record (index map: 0x1)
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select 1 AS `1` from (/* select#2 */ select 1 AS `1` from `test`.`t2` join `test`.`t1` where (`test`.`t2`.`b` < `test`.`t1`.`c`) group by '' limit 1) `d2`
|
|
|
|
SELECT * FROM information_schema.OPTIMIZER_TRACE;
|
|
QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
|
|
EXPLAIN SELECT 1 FROM
|
|
(SELECT 1 FROM t2,t1 WHERE b < c GROUP BY 1 LIMIT 1) AS d2 {
|
|
"steps": [
|
|
{
|
|
"join_preparation": {
|
|
"select#": 1,
|
|
"steps": [
|
|
{
|
|
"join_preparation": {
|
|
"select#": 2,
|
|
"steps": [
|
|
{
|
|
"expanded_query": "/* select#2 */ select 1 AS `1` from `t2` join `t1` where (`t2`.`b` < `t1`.`c`) group by '' limit 1"
|
|
}
|
|
] /* steps */
|
|
} /* join_preparation */
|
|
},
|
|
{
|
|
"derived": {
|
|
"table": " `d2`",
|
|
"select#": 2,
|
|
"materialized": true
|
|
} /* derived */
|
|
},
|
|
{
|
|
"expanded_query": "/* select#1 */ select 1 AS `1` from (/* select#2 */ select 1 AS `1` from `t2` join `t1` where (`t2`.`b` < `t1`.`c`) group by '' limit 1) `d2`"
|
|
}
|
|
] /* steps */
|
|
} /* join_preparation */
|
|
},
|
|
{
|
|
"join_optimization": {
|
|
"select#": 1,
|
|
"steps": [
|
|
{
|
|
"join_optimization": {
|
|
"select#": 2,
|
|
"steps": [
|
|
{
|
|
"condition_processing": {
|
|
"condition": "WHERE",
|
|
"original_condition": "(`t2`.`b` < `t1`.`c`)",
|
|
"steps": [
|
|
{
|
|
"transformation": "equality_propagation",
|
|
"resulting_condition": "(`t2`.`b` < `t1`.`c`)"
|
|
},
|
|
{
|
|
"transformation": "constant_propagation",
|
|
"resulting_condition": "(`t2`.`b` < `t1`.`c`)"
|
|
},
|
|
{
|
|
"transformation": "trivial_condition_removal",
|
|
"resulting_condition": "(`t2`.`b` < `t1`.`c`)"
|
|
}
|
|
] /* steps */
|
|
} /* condition_processing */
|
|
},
|
|
{
|
|
"substitute_generated_columns": {
|
|
} /* substitute_generated_columns */
|
|
},
|
|
{
|
|
"table_dependencies": [
|
|
{
|
|
"table": "`t2`",
|
|
"row_may_be_null": false,
|
|
"map_bit": 0,
|
|
"depends_on_map_bits": [
|
|
] /* depends_on_map_bits */
|
|
},
|
|
{
|
|
"table": "`t1`",
|
|
"row_may_be_null": false,
|
|
"map_bit": 1,
|
|
"depends_on_map_bits": [
|
|
] /* depends_on_map_bits */
|
|
}
|
|
] /* table_dependencies */
|
|
},
|
|
{
|
|
"ref_optimizer_key_uses": [
|
|
] /* ref_optimizer_key_uses */
|
|
},
|
|
{
|
|
"rows_estimation": [
|
|
{
|
|
"table": "`t2`",
|
|
"table_scan": {
|
|
"rows": 3,
|
|
"cost": 0.5013
|
|
} /* table_scan */
|
|
},
|
|
{
|
|
"table": "`t1`",
|
|
"table_scan": {
|
|
"rows": 2,
|
|
"cost": 0.5009
|
|
} /* table_scan */
|
|
}
|
|
] /* rows_estimation */
|
|
},
|
|
{
|
|
"considered_execution_plans": "..."
|
|
},
|
|
{
|
|
"attaching_conditions_to_tables": {
|
|
"original_condition": "(`t2`.`b` < `t1`.`c`)",
|
|
"attached_conditions_computation": [
|
|
{
|
|
"table": "`t2`",
|
|
"rechecking_index_usage": {
|
|
"recheck_reason": "not_first_table",
|
|
"range_analysis": {
|
|
"table_scan": {
|
|
"rows": 3,
|
|
"cost": 3.05
|
|
} /* table_scan */,
|
|
"potential_range_indexes": [
|
|
{
|
|
"index": "b",
|
|
"usable": true,
|
|
"key_parts": [
|
|
"b"
|
|
] /* key_parts */
|
|
}
|
|
] /* potential_range_indexes */,
|
|
"best_covering_index_scan": {
|
|
"index": "b",
|
|
"cost": 0.5616,
|
|
"chosen": true
|
|
} /* best_covering_index_scan */,
|
|
"setup_range_conditions": [
|
|
] /* setup_range_conditions */,
|
|
"group_index_range": {
|
|
"chosen": false,
|
|
"cause": "not_single_table"
|
|
} /* group_index_range */,
|
|
"skip_scan_range": {
|
|
"chosen": false,
|
|
"cause": "not_single_table"
|
|
} /* skip_scan_range */,
|
|
"analyzing_range_alternatives": {
|
|
"range_scan_alternatives": [
|
|
{
|
|
"index": "b",
|
|
"chosen": false,
|
|
"cause": "depends_on_unread_values"
|
|
}
|
|
] /* range_scan_alternatives */,
|
|
"analyzing_roworder_intersect": {
|
|
"usable": false,
|
|
"cause": "too_few_roworder_scans"
|
|
} /* analyzing_roworder_intersect */
|
|
} /* analyzing_range_alternatives */
|
|
} /* range_analysis */
|
|
} /* rechecking_index_usage */
|
|
}
|
|
] /* attached_conditions_computation */,
|
|
"attached_conditions_summary": [
|
|
{
|
|
"table": "`t1`",
|
|
"attached": null
|
|
},
|
|
{
|
|
"table": "`t2`",
|
|
"attached": "(`t2`.`b` < `t1`.`c`)"
|
|
}
|
|
] /* attached_conditions_summary */
|
|
} /* attaching_conditions_to_tables */
|
|
},
|
|
{
|
|
"optimizing_distinct_group_by_order_by": {
|
|
"simplifying_group_by": {
|
|
"original_clause": "''",
|
|
"items": [
|
|
{
|
|
"item": "1",
|
|
"uses_only_constant_tables": true
|
|
}
|
|
] /* items */,
|
|
"resulting_clause_is_simple": true,
|
|
"resulting_clause": ""
|
|
} /* simplifying_group_by */
|
|
} /* optimizing_distinct_group_by_order_by */
|
|
},
|
|
{
|
|
"finalizing_table_conditions": [
|
|
{
|
|
"table": "`t2`",
|
|
"original_table_condition": "(`t2`.`b` < `t1`.`c`)",
|
|
"final_table_condition ": "(`t2`.`b` < `t1`.`c`)"
|
|
}
|
|
] /* finalizing_table_conditions */
|
|
},
|
|
{
|
|
"refine_plan": [
|
|
{
|
|
"table": "`t1`"
|
|
},
|
|
{
|
|
"table": "`t2`"
|
|
}
|
|
] /* refine_plan */
|
|
}
|
|
] /* steps */
|
|
} /* join_optimization */
|
|
},
|
|
{
|
|
"creating_tmp_table": {
|
|
"tmp_table_info": {
|
|
"table": " `d2`",
|
|
"columns": 1,
|
|
"row_length": 5,
|
|
"key_length": 0,
|
|
"unique_constraint": false,
|
|
"makes_grouped_rows": false,
|
|
"cannot_insert_duplicates": false,
|
|
"location": "TempTable"
|
|
} /* tmp_table_info */
|
|
} /* creating_tmp_table */
|
|
},
|
|
{
|
|
"join_execution": {
|
|
"select#": 2,
|
|
"steps": [
|
|
{
|
|
"rows_estimation_per_outer_row": {
|
|
"table": "`t2`",
|
|
"range_analysis": {
|
|
"table_scan": {
|
|
"rows": 3,
|
|
"cost": 2.9013
|
|
} /* table_scan */,
|
|
"potential_range_indexes": [
|
|
{
|
|
"index": "b",
|
|
"usable": true,
|
|
"key_parts": [
|
|
"b"
|
|
] /* key_parts */
|
|
}
|
|
] /* potential_range_indexes */,
|
|
"best_covering_index_scan": {
|
|
"index": "b",
|
|
"cost": 0.5616,
|
|
"chosen": true
|
|
} /* best_covering_index_scan */,
|
|
"setup_range_conditions": [
|
|
{
|
|
"impossible_condition": {
|
|
"cause": "comparison_with_null_always_false"
|
|
} /* impossible_condition */
|
|
}
|
|
] /* setup_range_conditions */,
|
|
"impossible_range": true
|
|
} /* range_analysis */
|
|
} /* rows_estimation_per_outer_row */
|
|
},
|
|
{
|
|
"rows_estimation_per_outer_row": {
|
|
"table": "`t2`",
|
|
"range_analysis": {
|
|
"table_scan": {
|
|
"rows": 3,
|
|
"cost": 2.9013
|
|
} /* table_scan */,
|
|
"potential_range_indexes": [
|
|
{
|
|
"index": "b",
|
|
"usable": true,
|
|
"key_parts": [
|
|
"b"
|
|
] /* key_parts */
|
|
}
|
|
] /* potential_range_indexes */,
|
|
"best_covering_index_scan": {
|
|
"index": "b",
|
|
"cost": 0.5616,
|
|
"chosen": true
|
|
} /* best_covering_index_scan */,
|
|
"setup_range_conditions": [
|
|
{
|
|
"impossible_condition": {
|
|
"cause": "comparison_with_null_always_false"
|
|
} /* impossible_condition */
|
|
}
|
|
] /* setup_range_conditions */,
|
|
"impossible_range": true
|
|
} /* range_analysis */
|
|
} /* rows_estimation_per_outer_row */
|
|
}
|
|
] /* steps */
|
|
} /* join_execution */
|
|
},
|
|
{
|
|
"table_dependencies": [
|
|
{
|
|
"table": " `d2`",
|
|
"row_may_be_null": false,
|
|
"map_bit": 0,
|
|
"depends_on_map_bits": [
|
|
] /* depends_on_map_bits */
|
|
}
|
|
] /* table_dependencies */
|
|
},
|
|
{
|
|
"rows_estimation": [
|
|
{
|
|
"table": " `d2`",
|
|
"rows": 1,
|
|
"cost": 1,
|
|
"table_type": "system",
|
|
"empty": true
|
|
}
|
|
] /* rows_estimation */
|
|
}
|
|
] /* steps */,
|
|
"empty_result": {
|
|
"cause": "no matching row in const table"
|
|
} /* empty_result */
|
|
} /* join_optimization */
|
|
},
|
|
{
|
|
"join_explain": {
|
|
"select#": 1,
|
|
"steps": [
|
|
{
|
|
"join_explain": {
|
|
"select#": 2,
|
|
"steps": [
|
|
] /* steps */
|
|
} /* join_explain */
|
|
}
|
|
] /* steps */
|
|
} /* join_explain */
|
|
}
|
|
] /* steps */
|
|
} 0 0
|
|
|
|
SET optimizer_trace_features="greedy_search=off,dynamic_range=off";
|
|
EXPLAIN SELECT 1 FROM
|
|
(SELECT 1 FROM t2,t1 WHERE b < c GROUP BY 1 LIMIT 1) AS d2;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table
|
|
2 DERIVED t1 NULL ALL NULL NULL NULL NULL 2 100.00 NULL
|
|
2 DERIVED t2 NULL ALL b NULL NULL NULL 3 33.33 Range checked for each record (index map: 0x1)
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select 1 AS `1` from (/* select#2 */ select 1 AS `1` from `test`.`t2` join `test`.`t1` where (`test`.`t2`.`b` < `test`.`t1`.`c`) group by '' limit 1) `d2`
|
|
|
|
SELECT * FROM information_schema.OPTIMIZER_TRACE;
|
|
QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
|
|
EXPLAIN SELECT 1 FROM
|
|
(SELECT 1 FROM t2,t1 WHERE b < c GROUP BY 1 LIMIT 1) AS d2 {
|
|
"steps": [
|
|
{
|
|
"join_preparation": {
|
|
"select#": 1,
|
|
"steps": [
|
|
{
|
|
"join_preparation": {
|
|
"select#": 2,
|
|
"steps": [
|
|
{
|
|
"expanded_query": "/* select#2 */ select 1 AS `1` from `t2` join `t1` where (`t2`.`b` < `t1`.`c`) group by '' limit 1"
|
|
}
|
|
] /* steps */
|
|
} /* join_preparation */
|
|
},
|
|
{
|
|
"derived": {
|
|
"table": " `d2`",
|
|
"select#": 2,
|
|
"materialized": true
|
|
} /* derived */
|
|
},
|
|
{
|
|
"expanded_query": "/* select#1 */ select 1 AS `1` from (/* select#2 */ select 1 AS `1` from `t2` join `t1` where (`t2`.`b` < `t1`.`c`) group by '' limit 1) `d2`"
|
|
}
|
|
] /* steps */
|
|
} /* join_preparation */
|
|
},
|
|
{
|
|
"join_optimization": {
|
|
"select#": 1,
|
|
"steps": [
|
|
{
|
|
"join_optimization": {
|
|
"select#": 2,
|
|
"steps": [
|
|
{
|
|
"condition_processing": {
|
|
"condition": "WHERE",
|
|
"original_condition": "(`t2`.`b` < `t1`.`c`)",
|
|
"steps": [
|
|
{
|
|
"transformation": "equality_propagation",
|
|
"resulting_condition": "(`t2`.`b` < `t1`.`c`)"
|
|
},
|
|
{
|
|
"transformation": "constant_propagation",
|
|
"resulting_condition": "(`t2`.`b` < `t1`.`c`)"
|
|
},
|
|
{
|
|
"transformation": "trivial_condition_removal",
|
|
"resulting_condition": "(`t2`.`b` < `t1`.`c`)"
|
|
}
|
|
] /* steps */
|
|
} /* condition_processing */
|
|
},
|
|
{
|
|
"substitute_generated_columns": {
|
|
} /* substitute_generated_columns */
|
|
},
|
|
{
|
|
"table_dependencies": [
|
|
{
|
|
"table": "`t2`",
|
|
"row_may_be_null": false,
|
|
"map_bit": 0,
|
|
"depends_on_map_bits": [
|
|
] /* depends_on_map_bits */
|
|
},
|
|
{
|
|
"table": "`t1`",
|
|
"row_may_be_null": false,
|
|
"map_bit": 1,
|
|
"depends_on_map_bits": [
|
|
] /* depends_on_map_bits */
|
|
}
|
|
] /* table_dependencies */
|
|
},
|
|
{
|
|
"ref_optimizer_key_uses": [
|
|
] /* ref_optimizer_key_uses */
|
|
},
|
|
{
|
|
"rows_estimation": [
|
|
{
|
|
"table": "`t2`",
|
|
"table_scan": {
|
|
"rows": 3,
|
|
"cost": 0.5013
|
|
} /* table_scan */
|
|
},
|
|
{
|
|
"table": "`t1`",
|
|
"table_scan": {
|
|
"rows": 2,
|
|
"cost": 0.5009
|
|
} /* table_scan */
|
|
}
|
|
] /* rows_estimation */
|
|
},
|
|
{
|
|
"considered_execution_plans": "..."
|
|
},
|
|
{
|
|
"attaching_conditions_to_tables": {
|
|
"original_condition": "(`t2`.`b` < `t1`.`c`)",
|
|
"attached_conditions_computation": [
|
|
{
|
|
"table": "`t2`",
|
|
"rechecking_index_usage": {
|
|
"recheck_reason": "not_first_table",
|
|
"range_analysis": {
|
|
"table_scan": {
|
|
"rows": 3,
|
|
"cost": 3.05
|
|
} /* table_scan */,
|
|
"potential_range_indexes": [
|
|
{
|
|
"index": "b",
|
|
"usable": true,
|
|
"key_parts": [
|
|
"b"
|
|
] /* key_parts */
|
|
}
|
|
] /* potential_range_indexes */,
|
|
"best_covering_index_scan": {
|
|
"index": "b",
|
|
"cost": 0.5616,
|
|
"chosen": true
|
|
} /* best_covering_index_scan */,
|
|
"setup_range_conditions": [
|
|
] /* setup_range_conditions */,
|
|
"group_index_range": {
|
|
"chosen": false,
|
|
"cause": "not_single_table"
|
|
} /* group_index_range */,
|
|
"skip_scan_range": {
|
|
"chosen": false,
|
|
"cause": "not_single_table"
|
|
} /* skip_scan_range */,
|
|
"analyzing_range_alternatives": {
|
|
"range_scan_alternatives": [
|
|
{
|
|
"index": "b",
|
|
"chosen": false,
|
|
"cause": "depends_on_unread_values"
|
|
}
|
|
] /* range_scan_alternatives */,
|
|
"analyzing_roworder_intersect": {
|
|
"usable": false,
|
|
"cause": "too_few_roworder_scans"
|
|
} /* analyzing_roworder_intersect */
|
|
} /* analyzing_range_alternatives */
|
|
} /* range_analysis */
|
|
} /* rechecking_index_usage */
|
|
}
|
|
] /* attached_conditions_computation */,
|
|
"attached_conditions_summary": [
|
|
{
|
|
"table": "`t1`",
|
|
"attached": null
|
|
},
|
|
{
|
|
"table": "`t2`",
|
|
"attached": "(`t2`.`b` < `t1`.`c`)"
|
|
}
|
|
] /* attached_conditions_summary */
|
|
} /* attaching_conditions_to_tables */
|
|
},
|
|
{
|
|
"optimizing_distinct_group_by_order_by": {
|
|
"simplifying_group_by": {
|
|
"original_clause": "''",
|
|
"items": [
|
|
{
|
|
"item": "1",
|
|
"uses_only_constant_tables": true
|
|
}
|
|
] /* items */,
|
|
"resulting_clause_is_simple": true,
|
|
"resulting_clause": ""
|
|
} /* simplifying_group_by */
|
|
} /* optimizing_distinct_group_by_order_by */
|
|
},
|
|
{
|
|
"finalizing_table_conditions": [
|
|
{
|
|
"table": "`t2`",
|
|
"original_table_condition": "(`t2`.`b` < `t1`.`c`)",
|
|
"final_table_condition ": "(`t2`.`b` < `t1`.`c`)"
|
|
}
|
|
] /* finalizing_table_conditions */
|
|
},
|
|
{
|
|
"refine_plan": [
|
|
{
|
|
"table": "`t1`"
|
|
},
|
|
{
|
|
"table": "`t2`"
|
|
}
|
|
] /* refine_plan */
|
|
}
|
|
] /* steps */
|
|
} /* join_optimization */
|
|
},
|
|
{
|
|
"creating_tmp_table": {
|
|
"tmp_table_info": {
|
|
"table": " `d2`",
|
|
"columns": 1,
|
|
"row_length": 5,
|
|
"key_length": 0,
|
|
"unique_constraint": false,
|
|
"makes_grouped_rows": false,
|
|
"cannot_insert_duplicates": false,
|
|
"location": "TempTable"
|
|
} /* tmp_table_info */
|
|
} /* creating_tmp_table */
|
|
},
|
|
{
|
|
"join_execution": {
|
|
"select#": 2,
|
|
"steps": [
|
|
{
|
|
"rows_estimation_per_outer_row": {
|
|
"table": "`t2`",
|
|
"range_analysis": {
|
|
"table_scan": {
|
|
"rows": 3,
|
|
"cost": 2.9013
|
|
} /* table_scan */,
|
|
"potential_range_indexes": [
|
|
{
|
|
"index": "b",
|
|
"usable": true,
|
|
"key_parts": [
|
|
"b"
|
|
] /* key_parts */
|
|
}
|
|
] /* potential_range_indexes */,
|
|
"best_covering_index_scan": {
|
|
"index": "b",
|
|
"cost": 0.5616,
|
|
"chosen": true
|
|
} /* best_covering_index_scan */,
|
|
"setup_range_conditions": [
|
|
{
|
|
"impossible_condition": {
|
|
"cause": "comparison_with_null_always_false"
|
|
} /* impossible_condition */
|
|
}
|
|
] /* setup_range_conditions */,
|
|
"impossible_range": true
|
|
} /* range_analysis */
|
|
} /* rows_estimation_per_outer_row */
|
|
}
|
|
] /* steps */
|
|
} /* join_execution */
|
|
},
|
|
{
|
|
"table_dependencies": [
|
|
{
|
|
"table": " `d2`",
|
|
"row_may_be_null": false,
|
|
"map_bit": 0,
|
|
"depends_on_map_bits": [
|
|
] /* depends_on_map_bits */
|
|
}
|
|
] /* table_dependencies */
|
|
},
|
|
{
|
|
"rows_estimation": [
|
|
{
|
|
"table": " `d2`",
|
|
"rows": 1,
|
|
"cost": 1,
|
|
"table_type": "system",
|
|
"empty": true
|
|
}
|
|
] /* rows_estimation */
|
|
}
|
|
] /* steps */,
|
|
"empty_result": {
|
|
"cause": "no matching row in const table"
|
|
} /* empty_result */
|
|
} /* join_optimization */
|
|
},
|
|
{
|
|
"join_explain": {
|
|
"select#": 1,
|
|
"steps": [
|
|
{
|
|
"join_explain": {
|
|
"select#": 2,
|
|
"steps": [
|
|
] /* steps */
|
|
} /* join_explain */
|
|
}
|
|
] /* steps */
|
|
} /* join_explain */
|
|
}
|
|
] /* steps */
|
|
} 0 0
|
|
|
|
DROP TABLE t1,t2;
|
|
SET optimizer_trace_features=default;
|
|
CREATE TABLE t1 (
|
|
i1 int,
|
|
i2 int,
|
|
c char(1),
|
|
KEY k1 (i1),
|
|
KEY k2 (i1, i2)
|
|
) CHARSET utf8mb4;
|
|
INSERT INTO t1 VALUES (0,1,'2'),(3,2,'1');
|
|
EXPLAIN SELECT * FROM t1 WHERE i1 > '2' ORDER BY i1, i2;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 NULL range k1,k2 k2 5 NULL 2 100.00 Using index condition
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`i1` AS `i1`,`test`.`t1`.`i2` AS `i2`,`test`.`t1`.`c` AS `c` from `test`.`t1` where (`test`.`t1`.`i1` > 2) order by `test`.`t1`.`i1`,`test`.`t1`.`i2`
|
|
|
|
SELECT * FROM information_schema.OPTIMIZER_TRACE;
|
|
QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
|
|
EXPLAIN SELECT * FROM t1 WHERE i1 > '2' ORDER BY i1, i2 {
|
|
"steps": [
|
|
{
|
|
"join_preparation": {
|
|
"select#": 1,
|
|
"steps": [
|
|
{
|
|
"expanded_query": "/* select#1 */ select `t1`.`i1` AS `i1`,`t1`.`i2` AS `i2`,`t1`.`c` AS `c` from `t1` where (`t1`.`i1` > '2') order by `t1`.`i1`,`t1`.`i2`"
|
|
}
|
|
] /* steps */
|
|
} /* join_preparation */
|
|
},
|
|
{
|
|
"join_optimization": {
|
|
"select#": 1,
|
|
"steps": [
|
|
{
|
|
"condition_processing": {
|
|
"condition": "WHERE",
|
|
"original_condition": "(`t1`.`i1` > '2')",
|
|
"steps": [
|
|
{
|
|
"transformation": "equality_propagation",
|
|
"resulting_condition": "(`t1`.`i1` > '2')"
|
|
},
|
|
{
|
|
"transformation": "constant_propagation",
|
|
"resulting_condition": "(`t1`.`i1` > '2')"
|
|
},
|
|
{
|
|
"transformation": "trivial_condition_removal",
|
|
"resulting_condition": "(`t1`.`i1` > 2)"
|
|
}
|
|
] /* steps */
|
|
} /* condition_processing */
|
|
},
|
|
{
|
|
"substitute_generated_columns": {
|
|
} /* substitute_generated_columns */
|
|
},
|
|
{
|
|
"table_dependencies": [
|
|
{
|
|
"table": "`t1`",
|
|
"row_may_be_null": false,
|
|
"map_bit": 0,
|
|
"depends_on_map_bits": [
|
|
] /* depends_on_map_bits */
|
|
}
|
|
] /* table_dependencies */
|
|
},
|
|
{
|
|
"ref_optimizer_key_uses": [
|
|
] /* ref_optimizer_key_uses */
|
|
},
|
|
{
|
|
"rows_estimation": [
|
|
{
|
|
"table": "`t1`",
|
|
"range_analysis": {
|
|
"table_scan": {
|
|
"rows": 2,
|
|
"cost": 2.8016
|
|
} /* table_scan */,
|
|
"potential_range_indexes": [
|
|
{
|
|
"index": "k1",
|
|
"usable": true,
|
|
"key_parts": [
|
|
"i1"
|
|
] /* key_parts */
|
|
},
|
|
{
|
|
"index": "k2",
|
|
"usable": true,
|
|
"key_parts": [
|
|
"i1",
|
|
"i2"
|
|
] /* key_parts */
|
|
}
|
|
] /* potential_range_indexes */,
|
|
"setup_range_conditions": [
|
|
] /* setup_range_conditions */,
|
|
"group_index_range": {
|
|
"chosen": false,
|
|
"cause": "not_group_by_or_distinct"
|
|
} /* group_index_range */,
|
|
"skip_scan_range": {
|
|
"potential_skip_scan_indexes": [
|
|
{
|
|
"index": "k1",
|
|
"usable": false,
|
|
"cause": "query_references_nonkey_column"
|
|
},
|
|
{
|
|
"index": "k2",
|
|
"usable": false,
|
|
"cause": "query_references_nonkey_column"
|
|
}
|
|
] /* potential_skip_scan_indexes */
|
|
} /* skip_scan_range */,
|
|
"analyzing_range_alternatives": {
|
|
"range_scan_alternatives": [
|
|
{
|
|
"index": "k1",
|
|
"ranges": [
|
|
"2 < i1"
|
|
] /* ranges */,
|
|
"index_dives_for_eq_ranges": true,
|
|
"rowid_ordered": false,
|
|
"using_mrr": false,
|
|
"index_only": false,
|
|
"rows": 2,
|
|
"cost": 0.96,
|
|
"chosen": true
|
|
},
|
|
{
|
|
"index": "k2",
|
|
"ranges": [
|
|
"2 < i1"
|
|
] /* ranges */,
|
|
"index_dives_for_eq_ranges": true,
|
|
"rowid_ordered": false,
|
|
"using_mrr": false,
|
|
"index_only": false,
|
|
"rows": 2,
|
|
"cost": 0.96,
|
|
"chosen": false,
|
|
"cause": "cost"
|
|
}
|
|
] /* range_scan_alternatives */,
|
|
"analyzing_roworder_intersect": {
|
|
"usable": false,
|
|
"cause": "too_few_roworder_scans"
|
|
} /* analyzing_roworder_intersect */
|
|
} /* analyzing_range_alternatives */,
|
|
"chosen_range_access_summary": {
|
|
"range_access_plan": {
|
|
"type": "range_scan",
|
|
"index": "k1",
|
|
"rows": 2,
|
|
"ranges": [
|
|
"2 < i1"
|
|
] /* ranges */
|
|
} /* range_access_plan */,
|
|
"rows_for_plan": 2,
|
|
"cost_for_plan": 0.96,
|
|
"chosen": true
|
|
} /* chosen_range_access_summary */
|
|
} /* range_analysis */
|
|
}
|
|
] /* rows_estimation */
|
|
},
|
|
{
|
|
"considered_execution_plans": [
|
|
{
|
|
"plan_prefix": [
|
|
] /* plan_prefix */,
|
|
"table": "`t1`",
|
|
"best_access_path": {
|
|
"considered_access_paths": [
|
|
{
|
|
"rows_to_scan": 2,
|
|
"filtering_effect": [
|
|
] /* filtering_effect */,
|
|
"final_filtering_effect": 1,
|
|
"access_type": "range",
|
|
"range_details": {
|
|
"used_index": "k1"
|
|
} /* range_details */,
|
|
"resulting_rows": 2,
|
|
"cost": 1.16,
|
|
"chosen": true,
|
|
"use_tmp_table": true
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */,
|
|
"condition_filtering_pct": 100,
|
|
"rows_for_plan": 2,
|
|
"cost_for_plan": 1.16,
|
|
"sort_cost": 2,
|
|
"new_cost_for_plan": 3.16,
|
|
"chosen": true
|
|
}
|
|
] /* considered_execution_plans */
|
|
},
|
|
{
|
|
"attaching_conditions_to_tables": {
|
|
"original_condition": "(`t1`.`i1` > 2)",
|
|
"attached_conditions_computation": [
|
|
] /* attached_conditions_computation */,
|
|
"attached_conditions_summary": [
|
|
{
|
|
"table": "`t1`",
|
|
"attached": "(`t1`.`i1` > 2)"
|
|
}
|
|
] /* attached_conditions_summary */
|
|
} /* attaching_conditions_to_tables */
|
|
},
|
|
{
|
|
"optimizing_distinct_group_by_order_by": {
|
|
"simplifying_order_by": {
|
|
"original_clause": "`t1`.`i1`,`t1`.`i2`",
|
|
"items": [
|
|
{
|
|
"item": "`t1`.`i1`"
|
|
},
|
|
{
|
|
"item": "`t1`.`i2`"
|
|
}
|
|
] /* items */,
|
|
"resulting_clause_is_simple": true,
|
|
"resulting_clause": "`t1`.`i1`,`t1`.`i2`"
|
|
} /* simplifying_order_by */
|
|
} /* optimizing_distinct_group_by_order_by */
|
|
},
|
|
{
|
|
"reconsidering_access_paths_for_index_ordering": {
|
|
"clause": "ORDER BY",
|
|
"steps": [
|
|
{
|
|
"rows_estimation": {
|
|
"table": "`t1`",
|
|
"index": "k2",
|
|
"range_analysis": {
|
|
"table_scan": {
|
|
"rows": 2,
|
|
"cost": 2.8016
|
|
} /* table_scan */,
|
|
"potential_range_indexes": [
|
|
{
|
|
"index": "k1",
|
|
"usable": false,
|
|
"cause": "not_applicable"
|
|
},
|
|
{
|
|
"index": "k2",
|
|
"usable": true,
|
|
"key_parts": [
|
|
"i1",
|
|
"i2"
|
|
] /* key_parts */
|
|
}
|
|
] /* potential_range_indexes */,
|
|
"setup_range_conditions": [
|
|
] /* setup_range_conditions */,
|
|
"group_index_range": {
|
|
"chosen": false,
|
|
"cause": "not_group_by_or_distinct"
|
|
} /* group_index_range */,
|
|
"skip_scan_range": {
|
|
"potential_skip_scan_indexes": [
|
|
{
|
|
"index": "k2",
|
|
"usable": false,
|
|
"cause": "query_references_nonkey_column"
|
|
}
|
|
] /* potential_skip_scan_indexes */
|
|
} /* skip_scan_range */,
|
|
"analyzing_range_alternatives": {
|
|
"range_scan_alternatives": [
|
|
{
|
|
"index": "k2",
|
|
"ranges": [
|
|
"2 < i1"
|
|
] /* ranges */,
|
|
"index_dives_for_eq_ranges": true,
|
|
"rowid_ordered": false,
|
|
"using_mrr": false,
|
|
"index_only": false,
|
|
"rows": 2,
|
|
"cost": 0.96,
|
|
"chosen": true
|
|
}
|
|
] /* range_scan_alternatives */,
|
|
"analyzing_roworder_intersect": {
|
|
"usable": false,
|
|
"cause": "too_few_roworder_scans"
|
|
} /* analyzing_roworder_intersect */
|
|
} /* analyzing_range_alternatives */,
|
|
"chosen_range_access_summary": {
|
|
"range_access_plan": {
|
|
"type": "range_scan",
|
|
"index": "k2",
|
|
"rows": 2,
|
|
"ranges": [
|
|
"2 < i1"
|
|
] /* ranges */
|
|
} /* range_access_plan */,
|
|
"rows_for_plan": 2,
|
|
"cost_for_plan": 0.96,
|
|
"chosen": true
|
|
} /* chosen_range_access_summary */
|
|
} /* range_analysis */
|
|
} /* rows_estimation */
|
|
}
|
|
] /* steps */,
|
|
"index_order_summary": {
|
|
"table": "`t1`",
|
|
"index_provides_order": true,
|
|
"order_direction": "asc",
|
|
"index": "k2",
|
|
"plan_changed": true,
|
|
"access_type": "range"
|
|
} /* index_order_summary */
|
|
} /* reconsidering_access_paths_for_index_ordering */
|
|
},
|
|
{
|
|
"finalizing_table_conditions": [
|
|
{
|
|
"table": "`t1`",
|
|
"original_table_condition": "(`t1`.`i1` > 2)",
|
|
"final_table_condition ": "(`t1`.`i1` > 2)"
|
|
}
|
|
] /* finalizing_table_conditions */
|
|
},
|
|
{
|
|
"refine_plan": [
|
|
{
|
|
"table": "`t1`",
|
|
"pushed_index_condition": "(`t1`.`i1` > 2)",
|
|
"table_condition_attached": null
|
|
}
|
|
] /* refine_plan */
|
|
},
|
|
{
|
|
"considering_tmp_tables": [
|
|
] /* considering_tmp_tables */
|
|
}
|
|
] /* steps */
|
|
} /* join_optimization */
|
|
},
|
|
{
|
|
"join_explain": {
|
|
"select#": 1,
|
|
"steps": [
|
|
] /* steps */
|
|
} /* join_explain */
|
|
}
|
|
] /* steps */
|
|
} 0 0
|
|
|
|
EXPLAIN SELECT DISTINCT i1 FROM t1 WHERE i1 >= '1' ORDER BY i1 DESC;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 NULL index k1,k2 k1 5 NULL 2 100.00 Using where; Backward index scan; Using index
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select distinct `test`.`t1`.`i1` AS `i1` from `test`.`t1` where (`test`.`t1`.`i1` >= 1) order by `test`.`t1`.`i1` desc
|
|
|
|
SELECT * FROM information_schema.OPTIMIZER_TRACE;
|
|
QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
|
|
EXPLAIN SELECT DISTINCT i1 FROM t1 WHERE i1 >= '1' ORDER BY i1 DESC {
|
|
"steps": [
|
|
{
|
|
"join_preparation": {
|
|
"select#": 1,
|
|
"steps": [
|
|
{
|
|
"expanded_query": "/* select#1 */ select distinct `t1`.`i1` AS `i1` from `t1` where (`t1`.`i1` >= '1') order by `t1`.`i1` desc"
|
|
}
|
|
] /* steps */
|
|
} /* join_preparation */
|
|
},
|
|
{
|
|
"join_optimization": {
|
|
"select#": 1,
|
|
"steps": [
|
|
{
|
|
"condition_processing": {
|
|
"condition": "WHERE",
|
|
"original_condition": "(`t1`.`i1` >= '1')",
|
|
"steps": [
|
|
{
|
|
"transformation": "equality_propagation",
|
|
"resulting_condition": "(`t1`.`i1` >= '1')"
|
|
},
|
|
{
|
|
"transformation": "constant_propagation",
|
|
"resulting_condition": "(`t1`.`i1` >= '1')"
|
|
},
|
|
{
|
|
"transformation": "trivial_condition_removal",
|
|
"resulting_condition": "(`t1`.`i1` >= 1)"
|
|
}
|
|
] /* steps */
|
|
} /* condition_processing */
|
|
},
|
|
{
|
|
"substitute_generated_columns": {
|
|
} /* substitute_generated_columns */
|
|
},
|
|
{
|
|
"table_dependencies": [
|
|
{
|
|
"table": "`t1`",
|
|
"row_may_be_null": false,
|
|
"map_bit": 0,
|
|
"depends_on_map_bits": [
|
|
] /* depends_on_map_bits */
|
|
}
|
|
] /* table_dependencies */
|
|
},
|
|
{
|
|
"ref_optimizer_key_uses": [
|
|
] /* ref_optimizer_key_uses */
|
|
},
|
|
{
|
|
"rows_estimation": [
|
|
{
|
|
"table": "`t1`",
|
|
"range_analysis": {
|
|
"table_scan": {
|
|
"rows": 2,
|
|
"cost": 2.8016
|
|
} /* table_scan */,
|
|
"potential_range_indexes": [
|
|
{
|
|
"index": "k1",
|
|
"usable": true,
|
|
"key_parts": [
|
|
"i1"
|
|
] /* key_parts */
|
|
},
|
|
{
|
|
"index": "k2",
|
|
"usable": true,
|
|
"key_parts": [
|
|
"i1",
|
|
"i2"
|
|
] /* key_parts */
|
|
}
|
|
] /* potential_range_indexes */,
|
|
"best_covering_index_scan": {
|
|
"index": "k1",
|
|
"cost": 0.4558,
|
|
"chosen": true
|
|
} /* best_covering_index_scan */,
|
|
"setup_range_conditions": [
|
|
] /* setup_range_conditions */,
|
|
"group_index_range": {
|
|
"distinct_query": true,
|
|
"potential_group_range_indexes": [
|
|
{
|
|
"index": "k1",
|
|
"covering": true,
|
|
"index_dives_for_eq_ranges": true,
|
|
"ranges": [
|
|
"1 <= i1"
|
|
] /* ranges */,
|
|
"rows": 3,
|
|
"cost": 0.7
|
|
},
|
|
{
|
|
"index": "k2",
|
|
"covering": true,
|
|
"index_dives_for_eq_ranges": true,
|
|
"ranges": [
|
|
"1 <= i1"
|
|
] /* ranges */,
|
|
"rows": 3,
|
|
"cost": 0.7
|
|
}
|
|
] /* potential_group_range_indexes */
|
|
} /* group_index_range */,
|
|
"best_group_range_summary": {
|
|
"type": "index_group",
|
|
"index": "k1",
|
|
"group_attribute": null,
|
|
"min_aggregate": false,
|
|
"max_aggregate": false,
|
|
"distinct_aggregate": false,
|
|
"rows": 3,
|
|
"cost": 0.7,
|
|
"key_parts_used_for_access": [
|
|
"i1"
|
|
] /* key_parts_used_for_access */,
|
|
"ranges": [
|
|
"1 <= i1"
|
|
] /* ranges */,
|
|
"chosen": false,
|
|
"cause": "cost"
|
|
} /* best_group_range_summary */,
|
|
"skip_scan_range": {
|
|
"chosen": false,
|
|
"cause": "has_select_distinct"
|
|
} /* skip_scan_range */,
|
|
"analyzing_range_alternatives": {
|
|
"range_scan_alternatives": [
|
|
{
|
|
"index": "k1",
|
|
"ranges": [
|
|
"1 <= i1"
|
|
] /* ranges */,
|
|
"index_dives_for_eq_ranges": true,
|
|
"rowid_ordered": false,
|
|
"using_mrr": false,
|
|
"index_only": true,
|
|
"rows": 2,
|
|
"cost": 0.4658,
|
|
"chosen": false,
|
|
"cause": "cost"
|
|
},
|
|
{
|
|
"index": "k2",
|
|
"ranges": [
|
|
"1 <= i1"
|
|
] /* ranges */,
|
|
"index_dives_for_eq_ranges": true,
|
|
"rowid_ordered": false,
|
|
"using_mrr": false,
|
|
"index_only": true,
|
|
"rows": 2,
|
|
"cost": 0.4681,
|
|
"chosen": false,
|
|
"cause": "cost"
|
|
}
|
|
] /* range_scan_alternatives */,
|
|
"analyzing_roworder_intersect": {
|
|
"usable": false,
|
|
"cause": "too_few_roworder_scans"
|
|
} /* analyzing_roworder_intersect */
|
|
} /* analyzing_range_alternatives */
|
|
} /* range_analysis */
|
|
}
|
|
] /* rows_estimation */
|
|
},
|
|
{
|
|
"considered_execution_plans": [
|
|
{
|
|
"plan_prefix": [
|
|
] /* plan_prefix */,
|
|
"table": "`t1`",
|
|
"best_access_path": {
|
|
"considered_access_paths": [
|
|
{
|
|
"rows_to_scan": 2,
|
|
"filtering_effect": [
|
|
] /* filtering_effect */,
|
|
"final_filtering_effect": 1,
|
|
"access_type": "scan",
|
|
"resulting_rows": 2,
|
|
"cost": 0.7016,
|
|
"chosen": true,
|
|
"use_tmp_table": true
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */,
|
|
"condition_filtering_pct": 100,
|
|
"rows_for_plan": 2,
|
|
"cost_for_plan": 0.7016,
|
|
"sort_cost": 2,
|
|
"new_cost_for_plan": 2.7016,
|
|
"chosen": true
|
|
}
|
|
] /* considered_execution_plans */
|
|
},
|
|
{
|
|
"attaching_conditions_to_tables": {
|
|
"original_condition": "(`t1`.`i1` >= 1)",
|
|
"attached_conditions_computation": [
|
|
] /* attached_conditions_computation */,
|
|
"attached_conditions_summary": [
|
|
{
|
|
"table": "`t1`",
|
|
"attached": "(`t1`.`i1` >= 1)"
|
|
}
|
|
] /* attached_conditions_summary */
|
|
} /* attaching_conditions_to_tables */
|
|
},
|
|
{
|
|
"optimizing_distinct_group_by_order_by": {
|
|
"simplifying_order_by": {
|
|
"original_clause": "`t1`.`i1` desc",
|
|
"items": [
|
|
{
|
|
"item": "`t1`.`i1`"
|
|
}
|
|
] /* items */,
|
|
"resulting_clause_is_simple": true,
|
|
"resulting_clause": "`t1`.`i1` desc"
|
|
} /* simplifying_order_by */,
|
|
"unknown_key_1": {
|
|
"reconsidering_access_paths_for_index_ordering": {
|
|
"clause": "ORDER BY",
|
|
"steps": [
|
|
] /* steps */,
|
|
"index_order_summary": {
|
|
"table": "`t1`",
|
|
"index_provides_order": true,
|
|
"order_direction": "desc",
|
|
"index": "k1",
|
|
"plan_changed": false
|
|
} /* index_order_summary */
|
|
} /* reconsidering_access_paths_for_index_ordering */
|
|
},
|
|
"unknown_key_2": {
|
|
"reconsidering_access_paths_for_index_ordering": {
|
|
"clause": "GROUP BY",
|
|
"steps": [
|
|
] /* steps */,
|
|
"index_order_summary": {
|
|
"table": "`t1`",
|
|
"index_provides_order": true,
|
|
"order_direction": "desc",
|
|
"index": "k1",
|
|
"plan_changed": false
|
|
} /* index_order_summary */
|
|
} /* reconsidering_access_paths_for_index_ordering */
|
|
},
|
|
"changed_distinct_to_group_by": true,
|
|
"simplifying_group_by": {
|
|
"original_clause": "`t1`.`i1` desc",
|
|
"items": [
|
|
{
|
|
"item": "`t1`.`i1`"
|
|
}
|
|
] /* items */,
|
|
"resulting_clause_is_simple": true,
|
|
"resulting_clause": "`t1`.`i1` desc"
|
|
} /* simplifying_group_by */,
|
|
"removed_order_by": true
|
|
} /* optimizing_distinct_group_by_order_by */
|
|
},
|
|
{
|
|
"reconsidering_access_paths_for_index_ordering": {
|
|
"clause": "GROUP BY",
|
|
"steps": [
|
|
] /* steps */,
|
|
"index_order_summary": {
|
|
"table": "`t1`",
|
|
"index_provides_order": true,
|
|
"order_direction": "desc",
|
|
"index": "k1",
|
|
"plan_changed": false
|
|
} /* index_order_summary */
|
|
} /* reconsidering_access_paths_for_index_ordering */
|
|
},
|
|
{
|
|
"finalizing_table_conditions": [
|
|
{
|
|
"table": "`t1`",
|
|
"original_table_condition": "(`t1`.`i1` >= 1)",
|
|
"final_table_condition ": "(`t1`.`i1` >= 1)"
|
|
}
|
|
] /* finalizing_table_conditions */
|
|
},
|
|
{
|
|
"refine_plan": [
|
|
{
|
|
"table": "`t1`"
|
|
}
|
|
] /* refine_plan */
|
|
},
|
|
{
|
|
"considering_tmp_tables": [
|
|
] /* considering_tmp_tables */
|
|
}
|
|
] /* steps */
|
|
} /* join_optimization */
|
|
},
|
|
{
|
|
"join_explain": {
|
|
"select#": 1,
|
|
"steps": [
|
|
] /* steps */
|
|
} /* join_explain */
|
|
}
|
|
] /* steps */
|
|
} 0 0
|
|
|
|
DROP TABLE t1;
|
|
CREATE TABLE t1 (
|
|
pk INT PRIMARY KEY,
|
|
i1 INT,
|
|
i2 INT,
|
|
v varchar(1),
|
|
INDEX i1_idx (i1),
|
|
INDEX v_idx (v,i1)
|
|
) ENGINE=InnoDB, CHARSET utf8mb4;
|
|
INSERT INTO t1 VALUES (1, 1, 9,'a'), (2, 2, 8,'b'), (3, 3, 7,'c'),
|
|
(4, 4, 6,'d'), (5, 5, 5,'e');
|
|
|
|
# Covering ROR intersect not chosen: Index with more keyparts found.
|
|
EXPLAIN SELECT v FROM t1 WHERE i1 = 1 AND v = 'a' AND pk < 3;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 NULL range PRIMARY,i1_idx,v_idx v_idx 16 NULL 1 100.00 Using where; Using index
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`v` AS `v` from `test`.`t1` where ((`test`.`t1`.`v` = 'a') and (`test`.`t1`.`i1` = 1) and (`test`.`t1`.`pk` < 3))
|
|
|
|
SELECT * FROM information_schema.OPTIMIZER_TRACE;
|
|
QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
|
|
EXPLAIN SELECT v FROM t1 WHERE i1 = 1 AND v = 'a' AND pk < 3 {
|
|
"steps": [
|
|
{
|
|
"join_preparation": {
|
|
"select#": 1,
|
|
"steps": [
|
|
{
|
|
"expanded_query": "/* select#1 */ select `t1`.`v` AS `v` from `t1` where ((`t1`.`i1` = 1) and (`t1`.`v` = 'a') and (`t1`.`pk` < 3))"
|
|
}
|
|
] /* steps */
|
|
} /* join_preparation */
|
|
},
|
|
{
|
|
"join_optimization": {
|
|
"select#": 1,
|
|
"steps": [
|
|
{
|
|
"condition_processing": {
|
|
"condition": "WHERE",
|
|
"original_condition": "((`t1`.`i1` = 1) and (`t1`.`v` = 'a') and (`t1`.`pk` < 3))",
|
|
"steps": [
|
|
{
|
|
"transformation": "equality_propagation",
|
|
"resulting_condition": "((`t1`.`pk` < 3) and multiple equal(1, `t1`.`i1`) and multiple equal('a', `t1`.`v`))"
|
|
},
|
|
{
|
|
"transformation": "constant_propagation",
|
|
"resulting_condition": "((`t1`.`pk` < 3) and multiple equal(1, `t1`.`i1`) and multiple equal('a', `t1`.`v`))"
|
|
},
|
|
{
|
|
"transformation": "trivial_condition_removal",
|
|
"resulting_condition": "((`t1`.`pk` < 3) and multiple equal(1, `t1`.`i1`) and multiple equal('a', `t1`.`v`))"
|
|
}
|
|
] /* steps */
|
|
} /* condition_processing */
|
|
},
|
|
{
|
|
"substitute_generated_columns": {
|
|
} /* substitute_generated_columns */
|
|
},
|
|
{
|
|
"table_dependencies": [
|
|
{
|
|
"table": "`t1`",
|
|
"row_may_be_null": false,
|
|
"map_bit": 0,
|
|
"depends_on_map_bits": [
|
|
] /* depends_on_map_bits */
|
|
}
|
|
] /* table_dependencies */
|
|
},
|
|
{
|
|
"ref_optimizer_key_uses": [
|
|
{
|
|
"table": "`t1`",
|
|
"field": "i1",
|
|
"equals": "1",
|
|
"null_rejecting": false
|
|
},
|
|
{
|
|
"table": "`t1`",
|
|
"field": "v",
|
|
"equals": "'a'",
|
|
"null_rejecting": false
|
|
},
|
|
{
|
|
"table": "`t1`",
|
|
"field": "i1",
|
|
"equals": "1",
|
|
"null_rejecting": false
|
|
}
|
|
] /* ref_optimizer_key_uses */
|
|
},
|
|
{
|
|
"rows_estimation": [
|
|
{
|
|
"table": "`t1`",
|
|
"range_analysis": {
|
|
"table_scan": {
|
|
"rows": 5,
|
|
"cost": 2.85
|
|
} /* table_scan */,
|
|
"potential_range_indexes": [
|
|
{
|
|
"index": "PRIMARY",
|
|
"usable": true,
|
|
"key_parts": [
|
|
"pk"
|
|
] /* key_parts */
|
|
},
|
|
{
|
|
"index": "i1_idx",
|
|
"usable": true,
|
|
"key_parts": [
|
|
"i1",
|
|
"pk"
|
|
] /* key_parts */
|
|
},
|
|
{
|
|
"index": "v_idx",
|
|
"usable": true,
|
|
"key_parts": [
|
|
"v",
|
|
"i1",
|
|
"pk"
|
|
] /* key_parts */
|
|
}
|
|
] /* potential_range_indexes */,
|
|
"best_covering_index_scan": {
|
|
"index": "v_idx",
|
|
"cost": 0.7519,
|
|
"chosen": true
|
|
} /* best_covering_index_scan */,
|
|
"setup_range_conditions": [
|
|
] /* setup_range_conditions */,
|
|
"group_index_range": {
|
|
"chosen": false,
|
|
"cause": "not_group_by_or_distinct"
|
|
} /* group_index_range */,
|
|
"skip_scan_range": {
|
|
"potential_skip_scan_indexes": [
|
|
{
|
|
"index": "PRIMARY",
|
|
"usable": false,
|
|
"cause": "query_references_nonkey_column"
|
|
},
|
|
{
|
|
"index": "i1_idx",
|
|
"usable": false,
|
|
"cause": "query_references_nonkey_column"
|
|
},
|
|
{
|
|
"index": "v_idx",
|
|
"usable": false,
|
|
"cause": "prefix_not_const_equality"
|
|
}
|
|
] /* potential_skip_scan_indexes */
|
|
} /* skip_scan_range */,
|
|
"analyzing_range_alternatives": {
|
|
"range_scan_alternatives": [
|
|
{
|
|
"index": "PRIMARY",
|
|
"ranges": [
|
|
"pk < 3"
|
|
] /* ranges */,
|
|
"index_dives_for_eq_ranges": true,
|
|
"rowid_ordered": true,
|
|
"using_mrr": false,
|
|
"index_only": false,
|
|
"rows": 2,
|
|
"cost": 0.71,
|
|
"chosen": true
|
|
},
|
|
{
|
|
"index": "i1_idx",
|
|
"ranges": [
|
|
"1 <= i1 <= 1 AND pk < 3"
|
|
] /* ranges */,
|
|
"index_dives_for_eq_ranges": true,
|
|
"rowid_ordered": true,
|
|
"using_mrr": false,
|
|
"index_only": false,
|
|
"rows": 1,
|
|
"cost": 0.61,
|
|
"chosen": true
|
|
},
|
|
{
|
|
"index": "v_idx",
|
|
"ranges": [
|
|
"a <= v <= a AND 1 <= i1 <= 1 AND pk < 3"
|
|
] /* ranges */,
|
|
"index_dives_for_eq_ranges": true,
|
|
"rowid_ordered": true,
|
|
"using_mrr": false,
|
|
"index_only": true,
|
|
"rows": 1,
|
|
"cost": 0.36,
|
|
"chosen": true
|
|
}
|
|
] /* range_scan_alternatives */,
|
|
"analyzing_roworder_intersect": {
|
|
"intersecting_indexes": [
|
|
{
|
|
"index": "v_idx",
|
|
"index_scan_cost": 0.25,
|
|
"cumulated_index_scan_cost": 0.25,
|
|
"disk_sweep_cost": 0,
|
|
"cumulated_total_cost": 0.25,
|
|
"usable": true,
|
|
"matching_rows_now": 1,
|
|
"isect_covering_with_this_index": true,
|
|
"chosen": true
|
|
}
|
|
] /* intersecting_indexes */,
|
|
"clustered_pk": {
|
|
"clustered_pk_added_to_intersect": false,
|
|
"cause": "roworder_is_covering"
|
|
} /* clustered_pk */,
|
|
"chosen": false,
|
|
"cause": "too_few_indexes_to_merge"
|
|
} /* analyzing_roworder_intersect */
|
|
} /* analyzing_range_alternatives */,
|
|
"chosen_range_access_summary": {
|
|
"range_access_plan": {
|
|
"type": "range_scan",
|
|
"index": "v_idx",
|
|
"rows": 1,
|
|
"ranges": [
|
|
"a <= v <= a AND 1 <= i1 <= 1 AND pk < 3"
|
|
] /* ranges */
|
|
} /* range_access_plan */,
|
|
"rows_for_plan": 1,
|
|
"cost_for_plan": 0.36,
|
|
"chosen": true
|
|
} /* chosen_range_access_summary */
|
|
} /* range_analysis */
|
|
}
|
|
] /* rows_estimation */
|
|
},
|
|
{
|
|
"considered_execution_plans": [
|
|
{
|
|
"plan_prefix": [
|
|
] /* plan_prefix */,
|
|
"table": "`t1`",
|
|
"best_access_path": {
|
|
"considered_access_paths": [
|
|
{
|
|
"access_type": "ref",
|
|
"index": "i1_idx",
|
|
"chosen": false,
|
|
"cause": "range_uses_more_keyparts"
|
|
},
|
|
{
|
|
"access_type": "ref",
|
|
"index": "v_idx",
|
|
"chosen": false,
|
|
"cause": "range_uses_more_keyparts"
|
|
},
|
|
{
|
|
"rows_to_scan": 1,
|
|
"filtering_effect": [
|
|
] /* filtering_effect */,
|
|
"final_filtering_effect": 1,
|
|
"access_type": "range",
|
|
"range_details": {
|
|
"used_index": "v_idx"
|
|
} /* range_details */,
|
|
"resulting_rows": 1,
|
|
"cost": 0.46,
|
|
"chosen": true
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */,
|
|
"condition_filtering_pct": 100,
|
|
"rows_for_plan": 1,
|
|
"cost_for_plan": 0.46,
|
|
"chosen": true
|
|
}
|
|
] /* considered_execution_plans */
|
|
},
|
|
{
|
|
"attaching_conditions_to_tables": {
|
|
"original_condition": "((`t1`.`v` = 'a') and (`t1`.`i1` = 1) and (`t1`.`pk` < 3))",
|
|
"attached_conditions_computation": [
|
|
] /* attached_conditions_computation */,
|
|
"attached_conditions_summary": [
|
|
{
|
|
"table": "`t1`",
|
|
"attached": "((`t1`.`v` = 'a') and (`t1`.`i1` = 1) and (`t1`.`pk` < 3))"
|
|
}
|
|
] /* attached_conditions_summary */
|
|
} /* attaching_conditions_to_tables */
|
|
},
|
|
{
|
|
"finalizing_table_conditions": [
|
|
{
|
|
"table": "`t1`",
|
|
"original_table_condition": "((`t1`.`v` = 'a') and (`t1`.`i1` = 1) and (`t1`.`pk` < 3))",
|
|
"final_table_condition ": "((`t1`.`v` = 'a') and (`t1`.`i1` = 1) and (`t1`.`pk` < 3))"
|
|
}
|
|
] /* finalizing_table_conditions */
|
|
},
|
|
{
|
|
"refine_plan": [
|
|
{
|
|
"table": "`t1`"
|
|
}
|
|
] /* refine_plan */
|
|
}
|
|
] /* steps */
|
|
} /* join_optimization */
|
|
},
|
|
{
|
|
"join_explain": {
|
|
"select#": 1,
|
|
"steps": [
|
|
] /* steps */
|
|
} /* join_explain */
|
|
}
|
|
] /* steps */
|
|
} 0 0
|
|
|
|
# Chooses ROR intersect
|
|
EXPLAIN SELECT v FROM t1 WHERE i1 = 1 AND pk < 3;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 NULL index_merge PRIMARY,i1_idx,v_idx i1_idx,PRIMARY 9,4 NULL 1 100.00 Using intersect(i1_idx,PRIMARY); Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`v` AS `v` from `test`.`t1` where ((`test`.`t1`.`i1` = 1) and (`test`.`t1`.`pk` < 3))
|
|
|
|
SELECT * FROM information_schema.OPTIMIZER_TRACE;
|
|
QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
|
|
EXPLAIN SELECT v FROM t1 WHERE i1 = 1 AND pk < 3 {
|
|
"steps": [
|
|
{
|
|
"join_preparation": {
|
|
"select#": 1,
|
|
"steps": [
|
|
{
|
|
"expanded_query": "/* select#1 */ select `t1`.`v` AS `v` from `t1` where ((`t1`.`i1` = 1) and (`t1`.`pk` < 3))"
|
|
}
|
|
] /* steps */
|
|
} /* join_preparation */
|
|
},
|
|
{
|
|
"join_optimization": {
|
|
"select#": 1,
|
|
"steps": [
|
|
{
|
|
"condition_processing": {
|
|
"condition": "WHERE",
|
|
"original_condition": "((`t1`.`i1` = 1) and (`t1`.`pk` < 3))",
|
|
"steps": [
|
|
{
|
|
"transformation": "equality_propagation",
|
|
"resulting_condition": "((`t1`.`pk` < 3) and multiple equal(1, `t1`.`i1`))"
|
|
},
|
|
{
|
|
"transformation": "constant_propagation",
|
|
"resulting_condition": "((`t1`.`pk` < 3) and multiple equal(1, `t1`.`i1`))"
|
|
},
|
|
{
|
|
"transformation": "trivial_condition_removal",
|
|
"resulting_condition": "((`t1`.`pk` < 3) and multiple equal(1, `t1`.`i1`))"
|
|
}
|
|
] /* steps */
|
|
} /* condition_processing */
|
|
},
|
|
{
|
|
"substitute_generated_columns": {
|
|
} /* substitute_generated_columns */
|
|
},
|
|
{
|
|
"table_dependencies": [
|
|
{
|
|
"table": "`t1`",
|
|
"row_may_be_null": false,
|
|
"map_bit": 0,
|
|
"depends_on_map_bits": [
|
|
] /* depends_on_map_bits */
|
|
}
|
|
] /* table_dependencies */
|
|
},
|
|
{
|
|
"ref_optimizer_key_uses": [
|
|
{
|
|
"table": "`t1`",
|
|
"field": "i1",
|
|
"equals": "1",
|
|
"null_rejecting": false
|
|
}
|
|
] /* ref_optimizer_key_uses */
|
|
},
|
|
{
|
|
"rows_estimation": [
|
|
{
|
|
"table": "`t1`",
|
|
"range_analysis": {
|
|
"table_scan": {
|
|
"rows": 5,
|
|
"cost": 2.85
|
|
} /* table_scan */,
|
|
"potential_range_indexes": [
|
|
{
|
|
"index": "PRIMARY",
|
|
"usable": true,
|
|
"key_parts": [
|
|
"pk"
|
|
] /* key_parts */
|
|
},
|
|
{
|
|
"index": "i1_idx",
|
|
"usable": true,
|
|
"key_parts": [
|
|
"i1",
|
|
"pk"
|
|
] /* key_parts */
|
|
},
|
|
{
|
|
"index": "v_idx",
|
|
"usable": true,
|
|
"key_parts": [
|
|
"v",
|
|
"i1",
|
|
"pk"
|
|
] /* key_parts */
|
|
}
|
|
] /* potential_range_indexes */,
|
|
"best_covering_index_scan": {
|
|
"index": "v_idx",
|
|
"cost": 0.7519,
|
|
"chosen": true
|
|
} /* best_covering_index_scan */,
|
|
"setup_range_conditions": [
|
|
] /* setup_range_conditions */,
|
|
"group_index_range": {
|
|
"chosen": false,
|
|
"cause": "not_group_by_or_distinct"
|
|
} /* group_index_range */,
|
|
"skip_scan_range": {
|
|
"potential_skip_scan_indexes": [
|
|
{
|
|
"index": "PRIMARY",
|
|
"usable": false,
|
|
"cause": "query_references_nonkey_column"
|
|
},
|
|
{
|
|
"index": "i1_idx",
|
|
"usable": false,
|
|
"cause": "query_references_nonkey_column"
|
|
},
|
|
{
|
|
"index": "v_idx",
|
|
"tree_travel_cost": 0.15,
|
|
"num_groups": 6,
|
|
"rows": 5,
|
|
"cost": 2.8019
|
|
}
|
|
] /* potential_skip_scan_indexes */
|
|
} /* skip_scan_range */,
|
|
"best_skip_scan_summary": {
|
|
"type": "skip_scan",
|
|
"index": "v_idx",
|
|
"key_parts_used_for_access": [
|
|
"v",
|
|
"i1"
|
|
] /* key_parts_used_for_access */,
|
|
"range": [
|
|
"1 <= i1 <= 1"
|
|
] /* range */,
|
|
"chosen": false,
|
|
"cause": "cost"
|
|
} /* best_skip_scan_summary */,
|
|
"analyzing_range_alternatives": {
|
|
"range_scan_alternatives": [
|
|
{
|
|
"index": "PRIMARY",
|
|
"ranges": [
|
|
"pk < 3"
|
|
] /* ranges */,
|
|
"index_dives_for_eq_ranges": true,
|
|
"rowid_ordered": true,
|
|
"using_mrr": false,
|
|
"index_only": false,
|
|
"rows": 2,
|
|
"cost": 0.71,
|
|
"chosen": true
|
|
},
|
|
{
|
|
"index": "i1_idx",
|
|
"ranges": [
|
|
"1 <= i1 <= 1 AND pk < 3"
|
|
] /* ranges */,
|
|
"index_dives_for_eq_ranges": true,
|
|
"rowid_ordered": true,
|
|
"using_mrr": false,
|
|
"index_only": false,
|
|
"rows": 1,
|
|
"cost": 0.61,
|
|
"chosen": true
|
|
},
|
|
{
|
|
"index": "v_idx",
|
|
"chosen": false,
|
|
"cause": "no_valid_range_for_this_index"
|
|
}
|
|
] /* range_scan_alternatives */,
|
|
"analyzing_roworder_intersect": {
|
|
"intersecting_indexes": [
|
|
{
|
|
"index": "i1_idx",
|
|
"index_scan_cost": 0.25,
|
|
"cumulated_index_scan_cost": 0.25,
|
|
"disk_sweep_cost": 0.25,
|
|
"cumulated_total_cost": 0.5,
|
|
"usable": true,
|
|
"matching_rows_now": 1,
|
|
"isect_covering_with_this_index": false,
|
|
"chosen": true
|
|
}
|
|
] /* intersecting_indexes */,
|
|
"clustered_pk": {
|
|
"index_scan_cost": 0.05,
|
|
"cumulated_index_scan_cost": 0.3,
|
|
"disk_sweep_cost": 0,
|
|
"clustered_pk_scan_added_to_intersect": true,
|
|
"cumulated_cost": 0.3
|
|
} /* clustered_pk */,
|
|
"rows": 1,
|
|
"cost": 0.3,
|
|
"covering": false,
|
|
"chosen": true
|
|
} /* analyzing_roworder_intersect */
|
|
} /* analyzing_range_alternatives */,
|
|
"chosen_range_access_summary": {
|
|
"range_access_plan": {
|
|
"type": "index_roworder_intersect",
|
|
"rows": 1,
|
|
"cost": 0.3,
|
|
"covering": false,
|
|
"clustered_pk_scan": true,
|
|
"intersect_of": [
|
|
{
|
|
"type": "range_scan",
|
|
"index": "i1_idx",
|
|
"rows": 1,
|
|
"ranges": [
|
|
"1 <= i1 <= 1 AND pk < 3"
|
|
] /* ranges */
|
|
}
|
|
] /* intersect_of */
|
|
} /* range_access_plan */,
|
|
"rows_for_plan": 1,
|
|
"cost_for_plan": 0.3,
|
|
"chosen": true
|
|
} /* chosen_range_access_summary */
|
|
} /* range_analysis */
|
|
}
|
|
] /* rows_estimation */
|
|
},
|
|
{
|
|
"considered_execution_plans": [
|
|
{
|
|
"plan_prefix": [
|
|
] /* plan_prefix */,
|
|
"table": "`t1`",
|
|
"best_access_path": {
|
|
"considered_access_paths": [
|
|
{
|
|
"access_type": "ref",
|
|
"index": "i1_idx",
|
|
"chosen": false,
|
|
"cause": "range_uses_more_keyparts"
|
|
},
|
|
{
|
|
"rows_to_scan": 1,
|
|
"filtering_effect": [
|
|
] /* filtering_effect */,
|
|
"final_filtering_effect": 1,
|
|
"access_type": "range",
|
|
"range_details": {
|
|
"used_index": "intersect(i1_idx,PRIMARY)"
|
|
} /* range_details */,
|
|
"resulting_rows": 1,
|
|
"cost": 0.4,
|
|
"chosen": true
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */,
|
|
"condition_filtering_pct": 100,
|
|
"rows_for_plan": 1,
|
|
"cost_for_plan": 0.4,
|
|
"chosen": true
|
|
}
|
|
] /* considered_execution_plans */
|
|
},
|
|
{
|
|
"attaching_conditions_to_tables": {
|
|
"original_condition": "((`t1`.`i1` = 1) and (`t1`.`pk` < 3))",
|
|
"attached_conditions_computation": [
|
|
] /* attached_conditions_computation */,
|
|
"attached_conditions_summary": [
|
|
{
|
|
"table": "`t1`",
|
|
"attached": "((`t1`.`i1` = 1) and (`t1`.`pk` < 3))"
|
|
}
|
|
] /* attached_conditions_summary */
|
|
} /* attaching_conditions_to_tables */
|
|
},
|
|
{
|
|
"finalizing_table_conditions": [
|
|
{
|
|
"table": "`t1`",
|
|
"original_table_condition": "((`t1`.`i1` = 1) and (`t1`.`pk` < 3))",
|
|
"final_table_condition ": "((`t1`.`i1` = 1) and (`t1`.`pk` < 3))"
|
|
}
|
|
] /* finalizing_table_conditions */
|
|
},
|
|
{
|
|
"refine_plan": [
|
|
{
|
|
"table": "`t1`"
|
|
}
|
|
] /* refine_plan */
|
|
}
|
|
] /* steps */
|
|
} /* join_optimization */
|
|
},
|
|
{
|
|
"join_explain": {
|
|
"select#": 1,
|
|
"steps": [
|
|
] /* steps */
|
|
} /* join_explain */
|
|
}
|
|
] /* steps */
|
|
} 0 0
|
|
|
|
DROP TABLE t1;
|
|
CREATE TABLE t1 (a int, b int, PRIMARY KEY (a,b), KEY b (b)) ENGINE=InnoDB STATS_PERSISTENT=0;
|
|
INSERT INTO t1 VALUES (1,1),(1,2),(1,0),(1,3);
|
|
|
|
# Test trace for unreliable_ref_cost_and_range_uses_more_keyparts to
|
|
# ignore ref access on index with overly optimistic cost-estimate
|
|
EXPLAIN SELECT MAX(b), a FROM t1 WHERE b < 2 AND a = 1 GROUP BY a;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 NULL range PRIMARY,b b 4 NULL 2 25.00 Using where; Using index
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select max(`test`.`t1`.`b`) AS `MAX(b)`,`test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`a` = 1) and (`test`.`t1`.`b` < 2)) group by `test`.`t1`.`a`
|
|
|
|
SELECT * FROM information_schema.OPTIMIZER_TRACE;
|
|
QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
|
|
EXPLAIN SELECT MAX(b), a FROM t1 WHERE b < 2 AND a = 1 GROUP BY a {
|
|
"steps": [
|
|
{
|
|
"join_preparation": {
|
|
"select#": 1,
|
|
"steps": [
|
|
{
|
|
"expanded_query": "/* select#1 */ select max(`t1`.`b`) AS `MAX(b)`,`t1`.`a` AS `a` from `t1` where ((`t1`.`b` < 2) and (`t1`.`a` = 1)) group by `t1`.`a`"
|
|
}
|
|
] /* steps */
|
|
} /* join_preparation */
|
|
},
|
|
{
|
|
"join_optimization": {
|
|
"select#": 1,
|
|
"steps": [
|
|
{
|
|
"condition_processing": {
|
|
"condition": "WHERE",
|
|
"original_condition": "((`t1`.`b` < 2) and (`t1`.`a` = 1))",
|
|
"steps": [
|
|
{
|
|
"transformation": "equality_propagation",
|
|
"resulting_condition": "((`t1`.`b` < 2) and multiple equal(1, `t1`.`a`))"
|
|
},
|
|
{
|
|
"transformation": "constant_propagation",
|
|
"resulting_condition": "((`t1`.`b` < 2) and multiple equal(1, `t1`.`a`))"
|
|
},
|
|
{
|
|
"transformation": "trivial_condition_removal",
|
|
"resulting_condition": "((`t1`.`b` < 2) and multiple equal(1, `t1`.`a`))"
|
|
}
|
|
] /* steps */
|
|
} /* condition_processing */
|
|
},
|
|
{
|
|
"substitute_generated_columns": {
|
|
} /* substitute_generated_columns */
|
|
},
|
|
{
|
|
"table_dependencies": [
|
|
{
|
|
"table": "`t1`",
|
|
"row_may_be_null": false,
|
|
"map_bit": 0,
|
|
"depends_on_map_bits": [
|
|
] /* depends_on_map_bits */
|
|
}
|
|
] /* table_dependencies */
|
|
},
|
|
{
|
|
"ref_optimizer_key_uses": [
|
|
{
|
|
"table": "`t1`",
|
|
"field": "a",
|
|
"equals": "1",
|
|
"null_rejecting": false
|
|
}
|
|
] /* ref_optimizer_key_uses */
|
|
},
|
|
{
|
|
"rows_estimation": [
|
|
{
|
|
"table": "`t1`",
|
|
"range_analysis": {
|
|
"table_scan": {
|
|
"rows": 4,
|
|
"cost": 2.75
|
|
} /* table_scan */,
|
|
"potential_range_indexes": [
|
|
{
|
|
"index": "PRIMARY",
|
|
"usable": true,
|
|
"key_parts": [
|
|
"a",
|
|
"b"
|
|
] /* key_parts */
|
|
},
|
|
{
|
|
"index": "b",
|
|
"usable": true,
|
|
"key_parts": [
|
|
"b",
|
|
"a"
|
|
] /* key_parts */
|
|
}
|
|
] /* potential_range_indexes */,
|
|
"best_covering_index_scan": {
|
|
"index": "b",
|
|
"cost": 0.6511,
|
|
"chosen": true
|
|
} /* best_covering_index_scan */,
|
|
"setup_range_conditions": [
|
|
] /* setup_range_conditions */,
|
|
"group_index_range": {
|
|
"potential_group_range_indexes": [
|
|
{
|
|
"index": "PRIMARY",
|
|
"covering": true,
|
|
"index_dives_for_eq_ranges": true,
|
|
"ranges": [
|
|
"1 <= a <= 1 AND b < 2"
|
|
] /* ranges */,
|
|
"rows": 2,
|
|
"cost": 0.55
|
|
},
|
|
{
|
|
"index": "b",
|
|
"covering": true,
|
|
"usable": false,
|
|
"cause": "group_attribute_not_prefix_in_index"
|
|
}
|
|
] /* potential_group_range_indexes */
|
|
} /* group_index_range */,
|
|
"best_group_range_summary": {
|
|
"type": "index_group",
|
|
"index": "PRIMARY",
|
|
"group_attribute": "b",
|
|
"min_aggregate": false,
|
|
"max_aggregate": true,
|
|
"distinct_aggregate": false,
|
|
"rows": 2,
|
|
"cost": 0.55,
|
|
"key_parts_used_for_access": [
|
|
"a"
|
|
] /* key_parts_used_for_access */,
|
|
"ranges": [
|
|
"1 <= a <= 1 AND b < 2"
|
|
] /* ranges */,
|
|
"chosen": true
|
|
} /* best_group_range_summary */,
|
|
"skip_scan_range": {
|
|
"chosen": false,
|
|
"cause": "has_group_by"
|
|
} /* skip_scan_range */,
|
|
"analyzing_range_alternatives": {
|
|
"range_scan_alternatives": [
|
|
{
|
|
"index": "PRIMARY",
|
|
"ranges": [
|
|
"1 <= a <= 1 AND b < 2"
|
|
] /* ranges */,
|
|
"index_dives_for_eq_ranges": true,
|
|
"rowid_ordered": true,
|
|
"using_mrr": false,
|
|
"index_only": true,
|
|
"rows": 2,
|
|
"cost": 0.71,
|
|
"chosen": false,
|
|
"cause": "cost"
|
|
},
|
|
{
|
|
"index": "b",
|
|
"ranges": [
|
|
"b < 2"
|
|
] /* ranges */,
|
|
"index_dives_for_eq_ranges": true,
|
|
"rowid_ordered": false,
|
|
"using_mrr": false,
|
|
"index_only": true,
|
|
"rows": 2,
|
|
"cost": 0.4604,
|
|
"chosen": true
|
|
}
|
|
] /* range_scan_alternatives */,
|
|
"analyzing_roworder_intersect": {
|
|
"usable": false,
|
|
"cause": "too_few_roworder_scans"
|
|
} /* analyzing_roworder_intersect */
|
|
} /* analyzing_range_alternatives */,
|
|
"chosen_range_access_summary": {
|
|
"range_access_plan": {
|
|
"type": "range_scan",
|
|
"index": "b",
|
|
"rows": 2,
|
|
"ranges": [
|
|
"b < 2"
|
|
] /* ranges */
|
|
} /* range_access_plan */,
|
|
"rows_for_plan": 2,
|
|
"cost_for_plan": 0.4604,
|
|
"chosen": true
|
|
} /* chosen_range_access_summary */
|
|
} /* range_analysis */
|
|
}
|
|
] /* rows_estimation */
|
|
},
|
|
{
|
|
"considered_execution_plans": [
|
|
{
|
|
"plan_prefix": [
|
|
] /* plan_prefix */,
|
|
"table": "`t1`",
|
|
"best_access_path": {
|
|
"considered_access_paths": [
|
|
{
|
|
"access_type": "ref",
|
|
"index": "PRIMARY",
|
|
"chosen": false,
|
|
"cause": "range_uses_more_keyparts"
|
|
},
|
|
{
|
|
"rows_to_scan": 2,
|
|
"filtering_effect": [
|
|
] /* filtering_effect */,
|
|
"final_filtering_effect": 0.25,
|
|
"access_type": "range",
|
|
"range_details": {
|
|
"used_index": "b"
|
|
} /* range_details */,
|
|
"resulting_rows": 0.5,
|
|
"cost": 0.6604,
|
|
"chosen": true,
|
|
"use_tmp_table": true
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */,
|
|
"condition_filtering_pct": 100,
|
|
"rows_for_plan": 0.5,
|
|
"cost_for_plan": 0.6604,
|
|
"sort_cost": 0.5,
|
|
"new_cost_for_plan": 1.1604,
|
|
"chosen": true
|
|
}
|
|
] /* considered_execution_plans */
|
|
},
|
|
{
|
|
"attaching_conditions_to_tables": {
|
|
"original_condition": "((`t1`.`a` = 1) and (`t1`.`b` < 2))",
|
|
"attached_conditions_computation": [
|
|
] /* attached_conditions_computation */,
|
|
"attached_conditions_summary": [
|
|
{
|
|
"table": "`t1`",
|
|
"attached": "((`t1`.`a` = 1) and (`t1`.`b` < 2))"
|
|
}
|
|
] /* attached_conditions_summary */
|
|
} /* attaching_conditions_to_tables */
|
|
},
|
|
{
|
|
"optimizing_distinct_group_by_order_by": {
|
|
"simplifying_group_by": {
|
|
"original_clause": "`t1`.`a`",
|
|
"items": [
|
|
{
|
|
"item": "`t1`.`a`",
|
|
"equals_constant_in_where": true
|
|
}
|
|
] /* items */,
|
|
"resulting_clause_is_simple": true,
|
|
"resulting_clause": ""
|
|
} /* simplifying_group_by */
|
|
} /* optimizing_distinct_group_by_order_by */
|
|
},
|
|
{
|
|
"finalizing_table_conditions": [
|
|
{
|
|
"table": "`t1`",
|
|
"original_table_condition": "((`t1`.`a` = 1) and (`t1`.`b` < 2))",
|
|
"final_table_condition ": "((`t1`.`a` = 1) and (`t1`.`b` < 2))"
|
|
}
|
|
] /* finalizing_table_conditions */
|
|
},
|
|
{
|
|
"refine_plan": [
|
|
{
|
|
"table": "`t1`"
|
|
}
|
|
] /* refine_plan */
|
|
}
|
|
] /* steps */
|
|
} /* join_optimization */
|
|
},
|
|
{
|
|
"join_explain": {
|
|
"select#": 1,
|
|
"steps": [
|
|
] /* steps */
|
|
} /* join_explain */
|
|
}
|
|
] /* steps */
|
|
} 0 0
|
|
|
|
drop table t1;
|
|
#
|
|
# Tracing of when test_if_skip_sort_order() switches to another
|
|
# index and we abandon ICP,
|
|
# see "disabling_pushed_condition_on_old_index" in trace.
|
|
#
|
|
CREATE TABLE t1 (
|
|
c1 VARCHAR(2) NOT NULL,
|
|
i1 INTEGER NOT NULL,
|
|
c2 VARCHAR(2) NOT NULL,
|
|
KEY k1 (c1),
|
|
KEY k2 (c1, i1)
|
|
) CHARSET utf8mb4;
|
|
INSERT INTO t1 VALUES ('0',3,'0'),('0',2,'1');
|
|
EXPLAIN SELECT * FROM t1 WHERE c1 = '1' ORDER BY i1;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 NULL ref k1,k2 k2 10 const 1 100.00 NULL
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`c1` AS `c1`,`test`.`t1`.`i1` AS `i1`,`test`.`t1`.`c2` AS `c2` from `test`.`t1` where (`test`.`t1`.`c1` = '1') order by `test`.`t1`.`i1`
|
|
SELECT * FROM information_schema.OPTIMIZER_TRACE;
|
|
QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
|
|
EXPLAIN SELECT * FROM t1 WHERE c1 = '1' ORDER BY i1 {
|
|
"steps": [
|
|
{
|
|
"join_preparation": {
|
|
"select#": 1,
|
|
"steps": [
|
|
{
|
|
"expanded_query": "/* select#1 */ select `t1`.`c1` AS `c1`,`t1`.`i1` AS `i1`,`t1`.`c2` AS `c2` from `t1` where (`t1`.`c1` = '1') order by `t1`.`i1`"
|
|
}
|
|
] /* steps */
|
|
} /* join_preparation */
|
|
},
|
|
{
|
|
"join_optimization": {
|
|
"select#": 1,
|
|
"steps": [
|
|
{
|
|
"condition_processing": {
|
|
"condition": "WHERE",
|
|
"original_condition": "(`t1`.`c1` = '1')",
|
|
"steps": [
|
|
{
|
|
"transformation": "equality_propagation",
|
|
"resulting_condition": "multiple equal('1', `t1`.`c1`)"
|
|
},
|
|
{
|
|
"transformation": "constant_propagation",
|
|
"resulting_condition": "multiple equal('1', `t1`.`c1`)"
|
|
},
|
|
{
|
|
"transformation": "trivial_condition_removal",
|
|
"resulting_condition": "multiple equal('1', `t1`.`c1`)"
|
|
}
|
|
] /* steps */
|
|
} /* condition_processing */
|
|
},
|
|
{
|
|
"substitute_generated_columns": {
|
|
} /* substitute_generated_columns */
|
|
},
|
|
{
|
|
"table_dependencies": [
|
|
{
|
|
"table": "`t1`",
|
|
"row_may_be_null": false,
|
|
"map_bit": 0,
|
|
"depends_on_map_bits": [
|
|
] /* depends_on_map_bits */
|
|
}
|
|
] /* table_dependencies */
|
|
},
|
|
{
|
|
"ref_optimizer_key_uses": [
|
|
{
|
|
"table": "`t1`",
|
|
"field": "c1",
|
|
"equals": "'1'",
|
|
"null_rejecting": false
|
|
},
|
|
{
|
|
"table": "`t1`",
|
|
"field": "c1",
|
|
"equals": "'1'",
|
|
"null_rejecting": false
|
|
}
|
|
] /* ref_optimizer_key_uses */
|
|
},
|
|
{
|
|
"rows_estimation": [
|
|
{
|
|
"table": "`t1`",
|
|
"range_analysis": {
|
|
"table_scan": {
|
|
"rows": 2,
|
|
"cost": 2.8024
|
|
} /* table_scan */,
|
|
"potential_range_indexes": [
|
|
{
|
|
"index": "k1",
|
|
"usable": true,
|
|
"key_parts": [
|
|
"c1"
|
|
] /* key_parts */
|
|
},
|
|
{
|
|
"index": "k2",
|
|
"usable": true,
|
|
"key_parts": [
|
|
"c1",
|
|
"i1"
|
|
] /* key_parts */
|
|
}
|
|
] /* potential_range_indexes */,
|
|
"setup_range_conditions": [
|
|
] /* setup_range_conditions */,
|
|
"group_index_range": {
|
|
"chosen": false,
|
|
"cause": "not_group_by_or_distinct"
|
|
} /* group_index_range */,
|
|
"skip_scan_range": {
|
|
"potential_skip_scan_indexes": [
|
|
{
|
|
"index": "k1",
|
|
"usable": false,
|
|
"cause": "query_references_nonkey_column"
|
|
},
|
|
{
|
|
"index": "k2",
|
|
"usable": false,
|
|
"cause": "query_references_nonkey_column"
|
|
}
|
|
] /* potential_skip_scan_indexes */
|
|
} /* skip_scan_range */,
|
|
"analyzing_range_alternatives": {
|
|
"range_scan_alternatives": [
|
|
{
|
|
"index": "k1",
|
|
"ranges": [
|
|
"1 <= c1 <= 1"
|
|
] /* ranges */,
|
|
"index_dives_for_eq_ranges": true,
|
|
"rowid_ordered": true,
|
|
"using_mrr": false,
|
|
"index_only": false,
|
|
"rows": 1,
|
|
"cost": 0.61,
|
|
"chosen": true
|
|
},
|
|
{
|
|
"index": "k2",
|
|
"ranges": [
|
|
"1 <= c1 <= 1"
|
|
] /* ranges */,
|
|
"index_dives_for_eq_ranges": true,
|
|
"rowid_ordered": false,
|
|
"using_mrr": false,
|
|
"index_only": false,
|
|
"rows": 1,
|
|
"cost": 0.61,
|
|
"chosen": false,
|
|
"cause": "cost"
|
|
}
|
|
] /* range_scan_alternatives */,
|
|
"analyzing_roworder_intersect": {
|
|
"usable": false,
|
|
"cause": "too_few_roworder_scans"
|
|
} /* analyzing_roworder_intersect */
|
|
} /* analyzing_range_alternatives */,
|
|
"chosen_range_access_summary": {
|
|
"range_access_plan": {
|
|
"type": "range_scan",
|
|
"index": "k1",
|
|
"rows": 1,
|
|
"ranges": [
|
|
"1 <= c1 <= 1"
|
|
] /* ranges */
|
|
} /* range_access_plan */,
|
|
"rows_for_plan": 1,
|
|
"cost_for_plan": 0.61,
|
|
"chosen": true
|
|
} /* chosen_range_access_summary */
|
|
} /* range_analysis */
|
|
}
|
|
] /* rows_estimation */
|
|
},
|
|
{
|
|
"considered_execution_plans": [
|
|
{
|
|
"plan_prefix": [
|
|
] /* plan_prefix */,
|
|
"table": "`t1`",
|
|
"best_access_path": {
|
|
"considered_access_paths": [
|
|
{
|
|
"access_type": "ref",
|
|
"index": "k1",
|
|
"rows": 1,
|
|
"cost": 0.35,
|
|
"chosen": true
|
|
},
|
|
{
|
|
"access_type": "ref",
|
|
"index": "k2",
|
|
"rows": 1,
|
|
"cost": 0.35,
|
|
"chosen": false
|
|
},
|
|
{
|
|
"access_type": "range",
|
|
"range_details": {
|
|
"used_index": "k1"
|
|
} /* range_details */,
|
|
"chosen": false,
|
|
"cause": "heuristic_index_cheaper"
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */,
|
|
"condition_filtering_pct": 100,
|
|
"rows_for_plan": 1,
|
|
"cost_for_plan": 0.35,
|
|
"chosen": true
|
|
}
|
|
] /* considered_execution_plans */
|
|
},
|
|
{
|
|
"attaching_conditions_to_tables": {
|
|
"original_condition": "(`t1`.`c1` = '1')",
|
|
"attached_conditions_computation": [
|
|
] /* attached_conditions_computation */,
|
|
"attached_conditions_summary": [
|
|
{
|
|
"table": "`t1`",
|
|
"attached": "(`t1`.`c1` = '1')"
|
|
}
|
|
] /* attached_conditions_summary */
|
|
} /* attaching_conditions_to_tables */
|
|
},
|
|
{
|
|
"optimizing_distinct_group_by_order_by": {
|
|
"simplifying_order_by": {
|
|
"original_clause": "`t1`.`i1`",
|
|
"items": [
|
|
{
|
|
"item": "`t1`.`i1`"
|
|
}
|
|
] /* items */,
|
|
"resulting_clause_is_simple": true,
|
|
"resulting_clause": "`t1`.`i1`"
|
|
} /* simplifying_order_by */
|
|
} /* optimizing_distinct_group_by_order_by */
|
|
},
|
|
{
|
|
"reconsidering_access_paths_for_index_ordering": {
|
|
"clause": "ORDER BY",
|
|
"steps": [
|
|
{
|
|
"check_if_range_uses_more_keyparts_than_ref": {
|
|
"rerunning_range_optimizer_for_single_index": {
|
|
"range_analysis": {
|
|
"table_scan": {
|
|
"rows": 2,
|
|
"cost": 2e308
|
|
} /* table_scan */,
|
|
"potential_range_indexes": [
|
|
{
|
|
"index": "k1",
|
|
"usable": false,
|
|
"cause": "not_applicable"
|
|
},
|
|
{
|
|
"index": "k2",
|
|
"usable": true,
|
|
"key_parts": [
|
|
"c1",
|
|
"i1"
|
|
] /* key_parts */
|
|
}
|
|
] /* potential_range_indexes */,
|
|
"setup_range_conditions": [
|
|
] /* setup_range_conditions */,
|
|
"group_index_range": {
|
|
"chosen": false,
|
|
"cause": "not_group_by_or_distinct"
|
|
} /* group_index_range */,
|
|
"skip_scan_range": {
|
|
"potential_skip_scan_indexes": [
|
|
{
|
|
"index": "k2",
|
|
"usable": false,
|
|
"cause": "query_references_nonkey_column"
|
|
}
|
|
] /* potential_skip_scan_indexes */
|
|
} /* skip_scan_range */,
|
|
"analyzing_range_alternatives": {
|
|
"range_scan_alternatives": [
|
|
{
|
|
"index": "k2",
|
|
"ranges": [
|
|
"1 <= c1 <= 1"
|
|
] /* ranges */,
|
|
"index_dives_for_eq_ranges": true,
|
|
"rowid_ordered": false,
|
|
"using_mrr": false,
|
|
"index_only": false,
|
|
"rows": 1,
|
|
"cost": 0.61,
|
|
"chosen": true
|
|
}
|
|
] /* range_scan_alternatives */,
|
|
"analyzing_roworder_intersect": {
|
|
"usable": false,
|
|
"cause": "too_few_roworder_scans"
|
|
} /* analyzing_roworder_intersect */
|
|
} /* analyzing_range_alternatives */,
|
|
"chosen_range_access_summary": {
|
|
"range_access_plan": {
|
|
"type": "range_scan",
|
|
"index": "k2",
|
|
"rows": 1,
|
|
"ranges": [
|
|
"1 <= c1 <= 1"
|
|
] /* ranges */
|
|
} /* range_access_plan */,
|
|
"rows_for_plan": 1,
|
|
"cost_for_plan": 0.61,
|
|
"chosen": true
|
|
} /* chosen_range_access_summary */
|
|
} /* range_analysis */,
|
|
"access_type_unchanged": {
|
|
"ref_key_length": 10,
|
|
"range_key_length": 10
|
|
} /* access_type_unchanged */
|
|
} /* rerunning_range_optimizer_for_single_index */
|
|
} /* check_if_range_uses_more_keyparts_than_ref */
|
|
}
|
|
] /* steps */,
|
|
"index_order_summary": {
|
|
"table": "`t1`",
|
|
"index_provides_order": true,
|
|
"order_direction": "asc",
|
|
"index": "k2",
|
|
"plan_changed": true,
|
|
"access_type": "ref"
|
|
} /* index_order_summary */
|
|
} /* reconsidering_access_paths_for_index_ordering */
|
|
},
|
|
{
|
|
"finalizing_table_conditions": [
|
|
{
|
|
"table": "`t1`",
|
|
"original_table_condition": "(`t1`.`c1` = '1')",
|
|
"final_table_condition ": null
|
|
}
|
|
] /* finalizing_table_conditions */
|
|
},
|
|
{
|
|
"refine_plan": [
|
|
{
|
|
"table": "`t1`"
|
|
}
|
|
] /* refine_plan */
|
|
},
|
|
{
|
|
"considering_tmp_tables": [
|
|
] /* considering_tmp_tables */
|
|
}
|
|
] /* steps */
|
|
} /* join_optimization */
|
|
},
|
|
{
|
|
"join_explain": {
|
|
"select#": 1,
|
|
"steps": [
|
|
] /* steps */
|
|
} /* join_explain */
|
|
}
|
|
] /* steps */
|
|
} 0 0
|
|
DROP TABLE t1;
|