#At file:///export/home/jl208045/mysql/wl4800/mysql-next-mr-opt-backporting-wl4800/ based on revid:guilhem.bichot@stripped
3279 Jorgen Loland 2011-03-15
Added test case for multiple ranges over a multi-keypart range
Trace failed to print all but the final range in the
chosen_range_access_summary{} section of the optimizer trace.
Fixed by navigating to the first range interval before printing
ranges.
modified:
mysql-test/include/optimizer_trace_range.inc
mysql-test/r/optimizer_trace_range_no_prot.result
mysql-test/r/optimizer_trace_range_ps_prot.result
sql/opt_range.cc
=== modified file 'mysql-test/include/optimizer_trace_range.inc'
--- a/mysql-test/include/optimizer_trace_range.inc 2011-01-18 13:00:16 +0000
+++ b/mysql-test/include/optimizer_trace_range.inc 2011-03-15 13:31:03 +0000
@@ -120,6 +120,13 @@ EXPLAIN SELECT * FROM t2 WHERE key1a = 5
--echo
SELECT * FROM information_schema.OPTIMIZER_TRACE;
+# Multiple ranges on key parts in same index
+--echo
+EXPLAIN SELECT * FROM t2 WHERE (key1a = 5 and key1b < 10 and key1b > 2) or
+ (key1a = 4 and key1b < 7 and key1b > 3);
+--echo
+SELECT * FROM information_schema.OPTIMIZER_TRACE;
+
# more_expensive_than_table_scan
--echo
EXPLAIN SELECT * FROM t1 WHERE (key1 > 1 OR key2 > 2);
=== modified file 'mysql-test/r/optimizer_trace_range_no_prot.result'
--- a/mysql-test/r/optimizer_trace_range_no_prot.result 2011-03-01 10:55:27 +0000
+++ b/mysql-test/r/optimizer_trace_range_no_prot.result 2011-03-15 13:31:03 +0000
@@ -2111,6 +2111,207 @@ EXPLAIN SELECT * FROM t2 WHERE key1a = 5
] /* steps */
} 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 type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 range PRIMARY,i1b PRIMARY 8 NULL 2 Using index condition
+
+SELECT * FROM information_schema.OPTIMIZER_TRACE;
+QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE
+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 `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)))"
+ }
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select#": 1,
+ "steps": [
+ {
+ "condition_processing": {
+ "condition": "WHERE",
+ "original_condition": "(((`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)))",
+ "steps": [
+ {
+ "transformation": "equality_propagation",
+ "resulting_condition": "(((`test`.`t2`.`key1b` < 10) and (`test`.`t2`.`key1b` > 2) and multiple equal(5, `test`.`t2`.`key1a`)) or ((`test`.`t2`.`key1b` < 7) and (`test`.`t2`.`key1b` > 3) and multiple equal(4, `test`.`t2`.`key1a`)))"
+ },
+ {
+ "transformation": "constant_propagation",
+ "resulting_condition": "(((`test`.`t2`.`key1b` < 10) and (`test`.`t2`.`key1b` > 2) and multiple equal(5, `test`.`t2`.`key1a`)) or ((`test`.`t2`.`key1b` < 7) and (`test`.`t2`.`key1b` > 3) and multiple equal(4, `test`.`t2`.`key1a`)))"
+ },
+ {
+ "transformation": "trivial_condition_removal",
+ "resulting_condition": "(((`test`.`t2`.`key1b` < 10) and (`test`.`t2`.`key1b` > 2) and multiple equal(5, `test`.`t2`.`key1a`)) or ((`test`.`t2`.`key1b` < 7) and (`test`.`t2`.`key1b` > 3) and multiple equal(4, `test`.`t2`.`key1a`)))"
+ }
+ ] /* steps */
+ } /* condition_processing */
+ },
+ {
+ "ref_optimizer_key_uses": [
+ ] /* ref_optimizer_key_uses */
+ },
+ {
+ "records_estimation": [
+ {
+ "database": "test",
+ "table": "t2",
+ "range_analysis": {
+ "table_scan": {
+ "records": 1024,
+ "cost": 215.15
+ } /* table_scan */,
+ "potential_range_indices": [
+ {
+ "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_indices */,
+ "setup_range_conditions": [
+ ] /* setup_range_conditions */,
+ "group_index_range": {
+ "chosen": false,
+ "cause": "not_group_by_or_distinct"
+ } /* group_index_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_only": false,
+ "records": 2,
+ "cost": 4.41,
+ "rowid_ordered": false,
+ "chosen": true
+ },
+ {
+ "index": "i1b",
+ "ranges": [
+ "2 < key1b <= 3 AND 5 <= key1a <= 5",
+ "3 < key1b < 7",
+ "7 <= key1b < 10 AND 5 <= key1a <= 5"
+ ] /* ranges */,
+ "index_only": false,
+ "records": 6,
+ "cost": 10.21,
+ "rowid_ordered": false,
+ "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",
+ "records": 2,
+ "ranges": [
+ "4 <= key1a <= 4 AND 3 < key1b < 7",
+ "5 <= key1a <= 5 AND 2 < key1b < 10"
+ ] /* ranges */
+ } /* range_access_plan */,
+ "records_for_plan": 2,
+ "cost_for_plan": 4.41,
+ "chosen": true
+ } /* chosen_range_access_summary */
+ } /* range_analysis */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "database": "test",
+ "table": "t2",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "range",
+ "records": 2,
+ "cost": 4.41,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 4.41,
+ "records_for_plan": 2,
+ "chosen": true
+ }
+ ] /* considered_execution_plans */
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": "(((`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)))",
+ "attached_conditions_computation": [
+ ] /* attached_conditions_computation */,
+ "attached_conditions_summary": [
+ {
+ "database": "test",
+ "table": "t2",
+ "attached": "(((`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)))"
+ }
+ ] /* attached_conditions_summary */
+ } /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ {
+ "database": "test",
+ "table": "t2",
+ "scan_type": "table"
+ }
+ ] /* refine_plan */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
+ }
+ ] /* steps */
+} 0
+
EXPLAIN SELECT * FROM t1 WHERE (key1 > 1 OR key2 > 2);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL i1,i2 NULL NULL NULL 1024 Using where
=== modified file 'mysql-test/r/optimizer_trace_range_ps_prot.result'
--- a/mysql-test/r/optimizer_trace_range_ps_prot.result 2011-03-01 10:55:27 +0000
+++ b/mysql-test/r/optimizer_trace_range_ps_prot.result 2011-03-15 13:31:03 +0000
@@ -2111,6 +2111,207 @@ EXPLAIN SELECT * FROM t2 WHERE key1a = 5
] /* steps */
} 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 type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 range PRIMARY,i1b PRIMARY 8 NULL 2 Using index condition
+
+SELECT * FROM information_schema.OPTIMIZER_TRACE;
+QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE
+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 `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)))"
+ }
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select#": 1,
+ "steps": [
+ {
+ "condition_processing": {
+ "condition": "WHERE",
+ "original_condition": "(((`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)))",
+ "steps": [
+ {
+ "transformation": "equality_propagation",
+ "resulting_condition": "(((`test`.`t2`.`key1b` < 10) and (`test`.`t2`.`key1b` > 2) and multiple equal(5, `test`.`t2`.`key1a`)) or ((`test`.`t2`.`key1b` < 7) and (`test`.`t2`.`key1b` > 3) and multiple equal(4, `test`.`t2`.`key1a`)))"
+ },
+ {
+ "transformation": "constant_propagation",
+ "resulting_condition": "(((`test`.`t2`.`key1b` < 10) and (`test`.`t2`.`key1b` > 2) and multiple equal(5, `test`.`t2`.`key1a`)) or ((`test`.`t2`.`key1b` < 7) and (`test`.`t2`.`key1b` > 3) and multiple equal(4, `test`.`t2`.`key1a`)))"
+ },
+ {
+ "transformation": "trivial_condition_removal",
+ "resulting_condition": "(((`test`.`t2`.`key1b` < 10) and (`test`.`t2`.`key1b` > 2) and multiple equal(5, `test`.`t2`.`key1a`)) or ((`test`.`t2`.`key1b` < 7) and (`test`.`t2`.`key1b` > 3) and multiple equal(4, `test`.`t2`.`key1a`)))"
+ }
+ ] /* steps */
+ } /* condition_processing */
+ },
+ {
+ "ref_optimizer_key_uses": [
+ ] /* ref_optimizer_key_uses */
+ },
+ {
+ "records_estimation": [
+ {
+ "database": "test",
+ "table": "t2",
+ "range_analysis": {
+ "table_scan": {
+ "records": 1024,
+ "cost": 215.15
+ } /* table_scan */,
+ "potential_range_indices": [
+ {
+ "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_indices */,
+ "setup_range_conditions": [
+ ] /* setup_range_conditions */,
+ "group_index_range": {
+ "chosen": false,
+ "cause": "not_group_by_or_distinct"
+ } /* group_index_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_only": false,
+ "records": 2,
+ "cost": 4.41,
+ "rowid_ordered": false,
+ "chosen": true
+ },
+ {
+ "index": "i1b",
+ "ranges": [
+ "2 < key1b <= 3 AND 5 <= key1a <= 5",
+ "3 < key1b < 7",
+ "7 <= key1b < 10 AND 5 <= key1a <= 5"
+ ] /* ranges */,
+ "index_only": false,
+ "records": 6,
+ "cost": 10.21,
+ "rowid_ordered": false,
+ "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",
+ "records": 2,
+ "ranges": [
+ "4 <= key1a <= 4 AND 3 < key1b < 7",
+ "5 <= key1a <= 5 AND 2 < key1b < 10"
+ ] /* ranges */
+ } /* range_access_plan */,
+ "records_for_plan": 2,
+ "cost_for_plan": 4.41,
+ "chosen": true
+ } /* chosen_range_access_summary */
+ } /* range_analysis */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": [
+ {
+ "database": "test",
+ "table": "t2",
+ "best_access_path": {
+ "considered_access_paths": [
+ {
+ "access_type": "range",
+ "records": 2,
+ "cost": 4.41,
+ "chosen": true
+ }
+ ] /* considered_access_paths */
+ } /* best_access_path */,
+ "cost_for_plan": 4.41,
+ "records_for_plan": 2,
+ "chosen": true
+ }
+ ] /* considered_execution_plans */
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": "(((`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)))",
+ "attached_conditions_computation": [
+ ] /* attached_conditions_computation */,
+ "attached_conditions_summary": [
+ {
+ "database": "test",
+ "table": "t2",
+ "attached": "(((`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)))"
+ }
+ ] /* attached_conditions_summary */
+ } /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ {
+ "database": "test",
+ "table": "t2",
+ "scan_type": "table"
+ }
+ ] /* refine_plan */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
+ }
+ ] /* steps */
+} 0
+
EXPLAIN SELECT * FROM t1 WHERE (key1 > 1 OR key2 > 2);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL i1,i2 NULL NULL NULL 1024 Using where
=== modified file 'sql/opt_range.cc'
--- a/sql/opt_range.cc 2011-02-16 15:06:06 +0000
+++ b/sql/opt_range.cc 2011-03-15 13:31:03 +0000
@@ -2018,13 +2018,17 @@ void TRP_RANGE::trace_basic_info(const P
add_utf8("index", cur_key.name).add("records", records);
Opt_trace_array trace_range(param->thd->opt_trace, "ranges");
- for (const SEL_ARG *current= key;
- current;
- current= current->next)
+
+ const SEL_ARG *current_range= key;
+ // make current_range point to the first interval
+ while (current_range->prev)
+ current_range= current_range->prev;
+
+ while (current_range)
{
String range_info;
range_info.set_charset(system_charset_info);
- for (const SEL_ARG *part= current;
+ for (const SEL_ARG *part= current_range;
part;
part= part->next_key_part)
{
@@ -2034,6 +2038,7 @@ void TRP_RANGE::trace_basic_info(const P
part->min_flag | part->max_flag);
}
trace_range.add_utf8(range_info.ptr(), range_info.length());
+ current_range= current_range->next;
}
#endif
}
Attachment: [text/bzr-bundle] bzr/jorgen.loland@oracle.com-20110315133103-ldnelxkial1qrfn4.bundle
