3307 Jorgen Loland 2011-05-18
Bug#12551047: ASSERTION NO_ASSERT_ON_SYNTAX_ERROR FAILED IN
OPT_TRACE_STMT::SYNTAX_ERROR
Optimizer tracing: get_best_covering_ror_intersect() was not
tested by any existing mtr test case so we had no test case
to use with optimizer tracing either. John found a case when
doing RQG testing, so now tracing could be fixed and a few
test cases added to optimizer_trace_range*.test
@ sql/opt_range.cc
Fix optimizer tracing in previously untested function get_best_covering_ror_intersect()
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
3306 Guilhem Bichot 2011-05-13
disable offending code until Jorgen has time to fix it.
modified:
sql/opt_range.cc
=== modified file 'mysql-test/include/optimizer_trace_range.inc'
--- a/mysql-test/include/optimizer_trace_range.inc 2011-04-06 09:19:23 +0000
+++ b/mysql-test/include/optimizer_trace_range.inc 2011-05-18 10:37:05 +0000
@@ -253,3 +253,36 @@ SELECT * FROM information_schema.OPTIMIZ
--echo
DROP TABLE t1;
+
+# Analyze whether to use covering roworder intersect
+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;
+
+INSERT INTO t1 VALUES (1, 1, 9,'a'), (2, 2, 8,'b'), (3, 3, 7,'c'),
+ (4, 4, 6,'d'), (5, 5, 5,'e');
+
+--echo
+--echo # Covering ROR intersect not chosen: only one scan used
+EXPLAIN SELECT v FROM t1 WHERE i1 = 1 AND v = 'a' AND pk < 3;
+--echo
+SELECT * FROM information_schema.OPTIMIZER_TRACE;
+
+DROP INDEX i1_idx ON t1;
+CREATE INDEX i1_i2_idx ON t1 (i2,i1);
+
+--echo
+--echo # Covering ROR intersect not chosen: cost
+EXPLAIN SELECT v FROM t1 WHERE i1 = 1 AND i2 = 1 AND v = 'a' AND pk < 3;
+--echo
+SELECT * FROM information_schema.OPTIMIZER_TRACE;
+
+# Todo: make a test case that chooses ROR intersect
+
+--echo
+DROP TABLE t1;
=== modified file 'mysql-test/r/optimizer_trace_range_no_prot.result'
--- a/mysql-test/r/optimizer_trace_range_no_prot.result 2011-05-06 12:58:06 +0000
+++ b/mysql-test/r/optimizer_trace_range_no_prot.result 2011-05-18 10:37:05 +0000
@@ -4920,3 +4920,523 @@ EXPLAIN SELECT DISTINCT i1 FROM t1 WHERE
} 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;
+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: only one scan used
+EXPLAIN SELECT v FROM t1 WHERE i1 = 1 AND v = 'a' AND pk < 3;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ref PRIMARY,i1_idx,v_idx i1_idx 5 const 1 Using index condition; Using where
+
+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 `test`.`t1`.`v` AS `v` from `test`.`t1` where ((`test`.`t1`.`i1` = 1) and (`test`.`t1`.`v` = 'a') and (`test`.`t1`.`pk` < 3))"
+ }
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select#": 1,
+ "steps": [
+ {
+ "condition_processing": {
+ "condition": "WHERE",
+ "original_condition": "((`test`.`t1`.`i1` = 1) and (`test`.`t1`.`v` = 'a') and (`test`.`t1`.`pk` < 3))",
+ "steps": [
+ {
+ "transformation": "equality_propagation",
+ "resulting_condition": "((`test`.`t1`.`pk` < 3) and multiple equal(1, `test`.`t1`.`i1`) and multiple equal('a', `test`.`t1`.`v`))"
+ },
+ {
+ "transformation": "constant_propagation",
+ "resulting_condition": "((`test`.`t1`.`pk` < 3) and multiple equal(1, `test`.`t1`.`i1`) and multiple equal('a', `test`.`t1`.`v`))"
+ },
+ {
+ "transformation": "trivial_condition_removal",
+ "resulting_condition": "((`test`.`t1`.`pk` < 3) and multiple equal(1, `test`.`t1`.`i1`) and multiple equal('a', `test`.`t1`.`v`))"
+ }
+ ] /* steps */
+ } /* condition_processing */
+ },
+ {
+ "ref_optimizer_key_uses": [
+ {
+ "database": "test",
+ "table": "t1",
+ "field": "i1",
+ "equals": "1",
+ "null_rejecting": false
+ },
+ {
+ "database": "test",
+ "table": "t1",
+ "field": "v",
+ "equals": "'a'",
+ "null_rejecting": false
+ },
+ {
+ "database": "test",
+ "table": "t1",
+ "field": "i1",
+ "equals": "1",
+ "null_rejecting": false
+ }
+ ] /* ref_optimizer_key_uses */
+ },
+ {
+ "records_estimation": [
+ {
+ "database": "test",
+ "table": "t1",
+ "range_analysis": {
+ "table_scan": {
+ "records": 5,
+ "cost": 4.1
+ } /* table_scan */,
+ "potential_range_indices": [
+ {
+ "index": "PRIMARY",
+ "usable": true,
+ "key_parts": [
+ "pk"
+ ] /* key_parts */
+ },
+ {
+ "index": "i1_idx",
+ "usable": true,
+ "key_parts": [
+ "i1"
+ ] /* key_parts */
+ },
+ {
+ "index": "v_idx",
+ "usable": true,
+ "key_parts": [
+ "v",
+ "i1"
+ ] /* key_parts */
+ }
+ ] /* potential_range_indices */,
+ "best_covering_index_scan": {
+ "index": "v_idx",
+ "cost": 2.0063,
+ "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 */,
+ "analyzing_range_alternatives": {
+ "range_scan_alternatives": [
+ {
+ "index": "PRIMARY",
+ "ranges": [
+ "pk < 3"
+ ] /* ranges */,
+ "index_only": false,
+ "records": 2,
+ "cost": 2.41,
+ "rowid_ordered": true,
+ "chosen": false,
+ "cause": "cost"
+ },
+ {
+ "index": "i1_idx",
+ "ranges": [
+ "1 <= i1 <= 1"
+ ] /* ranges */,
+ "index_only": false,
+ "records": 1,
+ "cost": 2.21,
+ "rowid_ordered": true,
+ "chosen": false,
+ "cause": "cost"
+ },
+ {
+ "index": "v_idx",
+ "ranges": [
+ "a <= v <= a AND 1 <= i1 <= 1"
+ ] /* ranges */,
+ "index_only": true,
+ "records": 1,
+ "cost": 2.21,
+ "rowid_ordered": true,
+ "chosen": false,
+ "cause": "cost"
+ }
+ ] /* range_scan_alternatives */,
+ "analyzing_roworder_intersect": {
+ "intersecting_indices": [
+ {
+ "index": "i1_idx",
+ "usable": true,
+ "matching_records_now": 1,
+ "cumulated_cost": 2,
+ "isect_covering_with_this_index": false
+ },
+ {
+ "index": "v_idx",
+ "usable": true,
+ "matching_records_now": 0.2,
+ "cumulated_cost": 2,
+ "isect_covering_with_this_index": true
+ }
+ ] /* intersecting_indices */,
+ "clustered_pk": {
+ "clustered_pk_scan_added_to_intersect": true,
+ "cumulated_cost": 1.1
+ } /* clustered_pk */,
+ "records": 1,
+ "cost": 1.1,
+ "covering": false,
+ "chosen": true
+ } /* analyzing_roworder_intersect */,
+ "make_covering_roworder_intersect": {
+ "included_indices": [
+ "v_idx"
+ ] /* included_indices */,
+ "covering": true,
+ "not_included_indices": [
+ "i1_idx"
+ ] /* not_included_indices */,
+ "chosen": false,
+ "cause": "only_one_index"
+ } /* make_covering_roworder_intersect */
+ } /* analyzing_range_alternatives */,
+ "chosen_range_access_summary": {
+ "range_access_plan": {
+ "type": "index_roworder_intersect",
+ "records": 1,
+ "cost": 1.1,
+ "covering": false,
+ "clustered_pk_scan": true,
+ "intersect_of": [
+ {
+ "type": "range_scan",
+ "index": "i1_idx",
+ "records": 1,
+ "ranges": [
+ "1 <= i1 <= 1"
+ ] /* ranges */
+ }
+ ] /* intersect_of */
+ } /* range_access_plan */,
+ "records_for_plan": 1,
+ "cost_for_plan": 1.1,
+ "chosen": true
+ } /* chosen_range_access_summary */
+ } /* range_analysis */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": "..."
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": "((`test`.`t1`.`v` = 'a') and (`test`.`t1`.`i1` = 1) and (`test`.`t1`.`pk` < 3))",
+ "attached_conditions_computation": [
+ ] /* attached_conditions_computation */,
+ "attached_conditions_summary": [
+ {
+ "database": "test",
+ "table": "t1",
+ "attached": "((`test`.`t1`.`v` = 'a') and (`test`.`t1`.`pk` < 3))"
+ }
+ ] /* attached_conditions_summary */
+ } /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ {
+ "database": "test",
+ "table": "t1"
+ }
+ ] /* refine_plan */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
+ }
+ ] /* steps */
+} 0 0
+DROP INDEX i1_idx ON t1;
+CREATE INDEX i1_i2_idx ON t1 (i2,i1);
+
+# Covering ROR intersect not chosen: cost
+EXPLAIN SELECT v FROM t1 WHERE i1 = 1 AND i2 = 1 AND v = 'a' AND pk < 3;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ref PRIMARY,v_idx,i1_i2_idx v_idx 9 const,const 1 Using index condition; Using where
+
+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 i2 = 1 AND v = 'a' AND pk < 3 {
+ "steps": [
+ {
+ "join_preparation": {
+ "select#": 1,
+ "steps": [
+ {
+ "expanded_query": "/* select#1 */ select `test`.`t1`.`v` AS `v` from `test`.`t1` where ((`test`.`t1`.`i1` = 1) and (`test`.`t1`.`i2` = 1) and (`test`.`t1`.`v` = 'a') and (`test`.`t1`.`pk` < 3))"
+ }
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select#": 1,
+ "steps": [
+ {
+ "condition_processing": {
+ "condition": "WHERE",
+ "original_condition": "((`test`.`t1`.`i1` = 1) and (`test`.`t1`.`i2` = 1) and (`test`.`t1`.`v` = 'a') and (`test`.`t1`.`pk` < 3))",
+ "steps": [
+ {
+ "transformation": "equality_propagation",
+ "resulting_condition": "((`test`.`t1`.`pk` < 3) and multiple equal(1, `test`.`t1`.`i1`) and multiple equal(1, `test`.`t1`.`i2`) and multiple equal('a', `test`.`t1`.`v`))"
+ },
+ {
+ "transformation": "constant_propagation",
+ "resulting_condition": "((`test`.`t1`.`pk` < 3) and multiple equal(1, `test`.`t1`.`i1`) and multiple equal(1, `test`.`t1`.`i2`) and multiple equal('a', `test`.`t1`.`v`))"
+ },
+ {
+ "transformation": "trivial_condition_removal",
+ "resulting_condition": "((`test`.`t1`.`pk` < 3) and multiple equal(1, `test`.`t1`.`i1`) and multiple equal(1, `test`.`t1`.`i2`) and multiple equal('a', `test`.`t1`.`v`))"
+ }
+ ] /* steps */
+ } /* condition_processing */
+ },
+ {
+ "ref_optimizer_key_uses": [
+ {
+ "database": "test",
+ "table": "t1",
+ "field": "v",
+ "equals": "'a'",
+ "null_rejecting": false
+ },
+ {
+ "database": "test",
+ "table": "t1",
+ "field": "i1",
+ "equals": "1",
+ "null_rejecting": false
+ },
+ {
+ "database": "test",
+ "table": "t1",
+ "field": "i2",
+ "equals": "1",
+ "null_rejecting": false
+ },
+ {
+ "database": "test",
+ "table": "t1",
+ "field": "i1",
+ "equals": "1",
+ "null_rejecting": false
+ }
+ ] /* ref_optimizer_key_uses */
+ },
+ {
+ "records_estimation": [
+ {
+ "database": "test",
+ "table": "t1",
+ "range_analysis": {
+ "table_scan": {
+ "records": 5,
+ "cost": 4.1
+ } /* table_scan */,
+ "potential_range_indices": [
+ {
+ "index": "PRIMARY",
+ "usable": true,
+ "key_parts": [
+ "pk"
+ ] /* key_parts */
+ },
+ {
+ "index": "v_idx",
+ "usable": true,
+ "key_parts": [
+ "v",
+ "i1"
+ ] /* key_parts */
+ },
+ {
+ "index": "i1_i2_idx",
+ "usable": true,
+ "key_parts": [
+ "i2",
+ "i1"
+ ] /* key_parts */
+ }
+ ] /* 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": [
+ "pk < 3"
+ ] /* ranges */,
+ "index_only": false,
+ "records": 2,
+ "cost": 2.41,
+ "rowid_ordered": true,
+ "chosen": true
+ },
+ {
+ "index": "v_idx",
+ "ranges": [
+ "a <= v <= a AND 1 <= i1 <= 1"
+ ] /* ranges */,
+ "index_only": false,
+ "records": 1,
+ "cost": 2.21,
+ "rowid_ordered": true,
+ "chosen": true
+ },
+ {
+ "index": "i1_i2_idx",
+ "ranges": [
+ "1 <= i2 <= 1 AND 1 <= i1 <= 1"
+ ] /* ranges */,
+ "index_only": false,
+ "records": 1,
+ "cost": 2.21,
+ "rowid_ordered": true,
+ "chosen": false,
+ "cause": "cost"
+ }
+ ] /* range_scan_alternatives */,
+ "analyzing_roworder_intersect": {
+ "intersecting_indices": [
+ {
+ "index": "v_idx",
+ "usable": true,
+ "matching_records_now": 1,
+ "cumulated_cost": 2,
+ "isect_covering_with_this_index": false
+ },
+ {
+ "index": "i1_i2_idx",
+ "usable": true,
+ "matching_records_now": 0.2,
+ "cumulated_cost": 2,
+ "isect_covering_with_this_index": true
+ }
+ ] /* intersecting_indices */,
+ "clustered_pk": {
+ "clustered_pk_scan_added_to_intersect": true,
+ "cumulated_cost": 1.1
+ } /* clustered_pk */,
+ "records": 1,
+ "cost": 1.1,
+ "covering": false,
+ "chosen": true
+ } /* analyzing_roworder_intersect */,
+ "make_covering_roworder_intersect": {
+ "included_indices": [
+ "v_idx",
+ "i1_i2_idx"
+ ] /* included_indices */,
+ "chosen": false,
+ "cause": "cost"
+ } /* make_covering_roworder_intersect */
+ } /* analyzing_range_alternatives */,
+ "chosen_range_access_summary": {
+ "range_access_plan": {
+ "type": "index_roworder_intersect",
+ "records": 1,
+ "cost": 1.1,
+ "covering": false,
+ "clustered_pk_scan": true,
+ "intersect_of": [
+ {
+ "type": "range_scan",
+ "index": "v_idx",
+ "records": 1,
+ "ranges": [
+ "a <= v <= a AND 1 <= i1 <= 1"
+ ] /* ranges */
+ }
+ ] /* intersect_of */
+ } /* range_access_plan */,
+ "records_for_plan": 1,
+ "cost_for_plan": 1.1,
+ "chosen": true
+ } /* chosen_range_access_summary */
+ } /* range_analysis */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": "..."
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": "((`test`.`t1`.`v` = 'a') and (`test`.`t1`.`i2` = 1) and (`test`.`t1`.`i1` = 1) and (`test`.`t1`.`pk` < 3))",
+ "attached_conditions_computation": [
+ ] /* attached_conditions_computation */,
+ "attached_conditions_summary": [
+ {
+ "database": "test",
+ "table": "t1",
+ "attached": "((`test`.`t1`.`v` = 'a') and (`test`.`t1`.`i2` = 1) and (`test`.`t1`.`pk` < 3))"
+ }
+ ] /* attached_conditions_summary */
+ } /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ {
+ "database": "test",
+ "table": "t1"
+ }
+ ] /* refine_plan */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
+ }
+ ] /* steps */
+} 0 0
+
+DROP TABLE t1;
=== modified file 'mysql-test/r/optimizer_trace_range_ps_prot.result'
--- a/mysql-test/r/optimizer_trace_range_ps_prot.result 2011-05-06 12:58:06 +0000
+++ b/mysql-test/r/optimizer_trace_range_ps_prot.result 2011-05-18 10:37:05 +0000
@@ -4912,3 +4912,523 @@ EXPLAIN SELECT DISTINCT i1 FROM t1 WHERE
} 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;
+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: only one scan used
+EXPLAIN SELECT v FROM t1 WHERE i1 = 1 AND v = 'a' AND pk < 3;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ref PRIMARY,i1_idx,v_idx i1_idx 5 const 1 Using index condition; Using where
+
+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 `test`.`t1`.`v` AS `v` from `test`.`t1` where ((`test`.`t1`.`i1` = 1) and (`test`.`t1`.`v` = 'a') and (`test`.`t1`.`pk` < 3))"
+ }
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select#": 1,
+ "steps": [
+ {
+ "condition_processing": {
+ "condition": "WHERE",
+ "original_condition": "((`test`.`t1`.`i1` = 1) and (`test`.`t1`.`v` = 'a') and (`test`.`t1`.`pk` < 3))",
+ "steps": [
+ {
+ "transformation": "equality_propagation",
+ "resulting_condition": "((`test`.`t1`.`pk` < 3) and multiple equal(1, `test`.`t1`.`i1`) and multiple equal('a', `test`.`t1`.`v`))"
+ },
+ {
+ "transformation": "constant_propagation",
+ "resulting_condition": "((`test`.`t1`.`pk` < 3) and multiple equal(1, `test`.`t1`.`i1`) and multiple equal('a', `test`.`t1`.`v`))"
+ },
+ {
+ "transformation": "trivial_condition_removal",
+ "resulting_condition": "((`test`.`t1`.`pk` < 3) and multiple equal(1, `test`.`t1`.`i1`) and multiple equal('a', `test`.`t1`.`v`))"
+ }
+ ] /* steps */
+ } /* condition_processing */
+ },
+ {
+ "ref_optimizer_key_uses": [
+ {
+ "database": "test",
+ "table": "t1",
+ "field": "i1",
+ "equals": "1",
+ "null_rejecting": false
+ },
+ {
+ "database": "test",
+ "table": "t1",
+ "field": "v",
+ "equals": "'a'",
+ "null_rejecting": false
+ },
+ {
+ "database": "test",
+ "table": "t1",
+ "field": "i1",
+ "equals": "1",
+ "null_rejecting": false
+ }
+ ] /* ref_optimizer_key_uses */
+ },
+ {
+ "records_estimation": [
+ {
+ "database": "test",
+ "table": "t1",
+ "range_analysis": {
+ "table_scan": {
+ "records": 5,
+ "cost": 4.1
+ } /* table_scan */,
+ "potential_range_indices": [
+ {
+ "index": "PRIMARY",
+ "usable": true,
+ "key_parts": [
+ "pk"
+ ] /* key_parts */
+ },
+ {
+ "index": "i1_idx",
+ "usable": true,
+ "key_parts": [
+ "i1"
+ ] /* key_parts */
+ },
+ {
+ "index": "v_idx",
+ "usable": true,
+ "key_parts": [
+ "v",
+ "i1"
+ ] /* key_parts */
+ }
+ ] /* potential_range_indices */,
+ "best_covering_index_scan": {
+ "index": "v_idx",
+ "cost": 2.0063,
+ "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 */,
+ "analyzing_range_alternatives": {
+ "range_scan_alternatives": [
+ {
+ "index": "PRIMARY",
+ "ranges": [
+ "pk < 3"
+ ] /* ranges */,
+ "index_only": false,
+ "records": 2,
+ "cost": 2.41,
+ "rowid_ordered": true,
+ "chosen": false,
+ "cause": "cost"
+ },
+ {
+ "index": "i1_idx",
+ "ranges": [
+ "1 <= i1 <= 1"
+ ] /* ranges */,
+ "index_only": false,
+ "records": 1,
+ "cost": 2.21,
+ "rowid_ordered": true,
+ "chosen": false,
+ "cause": "cost"
+ },
+ {
+ "index": "v_idx",
+ "ranges": [
+ "a <= v <= a AND 1 <= i1 <= 1"
+ ] /* ranges */,
+ "index_only": true,
+ "records": 1,
+ "cost": 2.21,
+ "rowid_ordered": true,
+ "chosen": false,
+ "cause": "cost"
+ }
+ ] /* range_scan_alternatives */,
+ "analyzing_roworder_intersect": {
+ "intersecting_indices": [
+ {
+ "index": "i1_idx",
+ "usable": true,
+ "matching_records_now": 1,
+ "cumulated_cost": 2,
+ "isect_covering_with_this_index": false
+ },
+ {
+ "index": "v_idx",
+ "usable": true,
+ "matching_records_now": 0.2,
+ "cumulated_cost": 2,
+ "isect_covering_with_this_index": true
+ }
+ ] /* intersecting_indices */,
+ "clustered_pk": {
+ "clustered_pk_scan_added_to_intersect": true,
+ "cumulated_cost": 1.1
+ } /* clustered_pk */,
+ "records": 1,
+ "cost": 1.1,
+ "covering": false,
+ "chosen": true
+ } /* analyzing_roworder_intersect */,
+ "make_covering_roworder_intersect": {
+ "included_indices": [
+ "v_idx"
+ ] /* included_indices */,
+ "covering": true,
+ "not_included_indices": [
+ "i1_idx"
+ ] /* not_included_indices */,
+ "chosen": false,
+ "cause": "only_one_index"
+ } /* make_covering_roworder_intersect */
+ } /* analyzing_range_alternatives */,
+ "chosen_range_access_summary": {
+ "range_access_plan": {
+ "type": "index_roworder_intersect",
+ "records": 1,
+ "cost": 1.1,
+ "covering": false,
+ "clustered_pk_scan": true,
+ "intersect_of": [
+ {
+ "type": "range_scan",
+ "index": "i1_idx",
+ "records": 1,
+ "ranges": [
+ "1 <= i1 <= 1"
+ ] /* ranges */
+ }
+ ] /* intersect_of */
+ } /* range_access_plan */,
+ "records_for_plan": 1,
+ "cost_for_plan": 1.1,
+ "chosen": true
+ } /* chosen_range_access_summary */
+ } /* range_analysis */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": "..."
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": "((`test`.`t1`.`v` = 'a') and (`test`.`t1`.`i1` = 1) and (`test`.`t1`.`pk` < 3))",
+ "attached_conditions_computation": [
+ ] /* attached_conditions_computation */,
+ "attached_conditions_summary": [
+ {
+ "database": "test",
+ "table": "t1",
+ "attached": "((`test`.`t1`.`v` = 'a') and (`test`.`t1`.`pk` < 3))"
+ }
+ ] /* attached_conditions_summary */
+ } /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ {
+ "database": "test",
+ "table": "t1"
+ }
+ ] /* refine_plan */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
+ }
+ ] /* steps */
+} 0 0
+DROP INDEX i1_idx ON t1;
+CREATE INDEX i1_i2_idx ON t1 (i2,i1);
+
+# Covering ROR intersect not chosen: cost
+EXPLAIN SELECT v FROM t1 WHERE i1 = 1 AND i2 = 1 AND v = 'a' AND pk < 3;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ref PRIMARY,v_idx,i1_i2_idx v_idx 9 const,const 1 Using index condition; Using where
+
+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 i2 = 1 AND v = 'a' AND pk < 3 {
+ "steps": [
+ {
+ "join_preparation": {
+ "select#": 1,
+ "steps": [
+ {
+ "expanded_query": "/* select#1 */ select `test`.`t1`.`v` AS `v` from `test`.`t1` where ((`test`.`t1`.`i1` = 1) and (`test`.`t1`.`i2` = 1) and (`test`.`t1`.`v` = 'a') and (`test`.`t1`.`pk` < 3))"
+ }
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select#": 1,
+ "steps": [
+ {
+ "condition_processing": {
+ "condition": "WHERE",
+ "original_condition": "((`test`.`t1`.`i1` = 1) and (`test`.`t1`.`i2` = 1) and (`test`.`t1`.`v` = 'a') and (`test`.`t1`.`pk` < 3))",
+ "steps": [
+ {
+ "transformation": "equality_propagation",
+ "resulting_condition": "((`test`.`t1`.`pk` < 3) and multiple equal(1, `test`.`t1`.`i1`) and multiple equal(1, `test`.`t1`.`i2`) and multiple equal('a', `test`.`t1`.`v`))"
+ },
+ {
+ "transformation": "constant_propagation",
+ "resulting_condition": "((`test`.`t1`.`pk` < 3) and multiple equal(1, `test`.`t1`.`i1`) and multiple equal(1, `test`.`t1`.`i2`) and multiple equal('a', `test`.`t1`.`v`))"
+ },
+ {
+ "transformation": "trivial_condition_removal",
+ "resulting_condition": "((`test`.`t1`.`pk` < 3) and multiple equal(1, `test`.`t1`.`i1`) and multiple equal(1, `test`.`t1`.`i2`) and multiple equal('a', `test`.`t1`.`v`))"
+ }
+ ] /* steps */
+ } /* condition_processing */
+ },
+ {
+ "ref_optimizer_key_uses": [
+ {
+ "database": "test",
+ "table": "t1",
+ "field": "v",
+ "equals": "'a'",
+ "null_rejecting": false
+ },
+ {
+ "database": "test",
+ "table": "t1",
+ "field": "i1",
+ "equals": "1",
+ "null_rejecting": false
+ },
+ {
+ "database": "test",
+ "table": "t1",
+ "field": "i2",
+ "equals": "1",
+ "null_rejecting": false
+ },
+ {
+ "database": "test",
+ "table": "t1",
+ "field": "i1",
+ "equals": "1",
+ "null_rejecting": false
+ }
+ ] /* ref_optimizer_key_uses */
+ },
+ {
+ "records_estimation": [
+ {
+ "database": "test",
+ "table": "t1",
+ "range_analysis": {
+ "table_scan": {
+ "records": 5,
+ "cost": 4.1
+ } /* table_scan */,
+ "potential_range_indices": [
+ {
+ "index": "PRIMARY",
+ "usable": true,
+ "key_parts": [
+ "pk"
+ ] /* key_parts */
+ },
+ {
+ "index": "v_idx",
+ "usable": true,
+ "key_parts": [
+ "v",
+ "i1"
+ ] /* key_parts */
+ },
+ {
+ "index": "i1_i2_idx",
+ "usable": true,
+ "key_parts": [
+ "i2",
+ "i1"
+ ] /* key_parts */
+ }
+ ] /* 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": [
+ "pk < 3"
+ ] /* ranges */,
+ "index_only": false,
+ "records": 2,
+ "cost": 2.41,
+ "rowid_ordered": true,
+ "chosen": true
+ },
+ {
+ "index": "v_idx",
+ "ranges": [
+ "a <= v <= a AND 1 <= i1 <= 1"
+ ] /* ranges */,
+ "index_only": false,
+ "records": 1,
+ "cost": 2.21,
+ "rowid_ordered": true,
+ "chosen": true
+ },
+ {
+ "index": "i1_i2_idx",
+ "ranges": [
+ "1 <= i2 <= 1 AND 1 <= i1 <= 1"
+ ] /* ranges */,
+ "index_only": false,
+ "records": 1,
+ "cost": 2.21,
+ "rowid_ordered": true,
+ "chosen": false,
+ "cause": "cost"
+ }
+ ] /* range_scan_alternatives */,
+ "analyzing_roworder_intersect": {
+ "intersecting_indices": [
+ {
+ "index": "v_idx",
+ "usable": true,
+ "matching_records_now": 1,
+ "cumulated_cost": 2,
+ "isect_covering_with_this_index": false
+ },
+ {
+ "index": "i1_i2_idx",
+ "usable": true,
+ "matching_records_now": 0.2,
+ "cumulated_cost": 2,
+ "isect_covering_with_this_index": true
+ }
+ ] /* intersecting_indices */,
+ "clustered_pk": {
+ "clustered_pk_scan_added_to_intersect": true,
+ "cumulated_cost": 1.1
+ } /* clustered_pk */,
+ "records": 1,
+ "cost": 1.1,
+ "covering": false,
+ "chosen": true
+ } /* analyzing_roworder_intersect */,
+ "make_covering_roworder_intersect": {
+ "included_indices": [
+ "v_idx",
+ "i1_i2_idx"
+ ] /* included_indices */,
+ "chosen": false,
+ "cause": "cost"
+ } /* make_covering_roworder_intersect */
+ } /* analyzing_range_alternatives */,
+ "chosen_range_access_summary": {
+ "range_access_plan": {
+ "type": "index_roworder_intersect",
+ "records": 1,
+ "cost": 1.1,
+ "covering": false,
+ "clustered_pk_scan": true,
+ "intersect_of": [
+ {
+ "type": "range_scan",
+ "index": "v_idx",
+ "records": 1,
+ "ranges": [
+ "a <= v <= a AND 1 <= i1 <= 1"
+ ] /* ranges */
+ }
+ ] /* intersect_of */
+ } /* range_access_plan */,
+ "records_for_plan": 1,
+ "cost_for_plan": 1.1,
+ "chosen": true
+ } /* chosen_range_access_summary */
+ } /* range_analysis */
+ }
+ ] /* records_estimation */
+ },
+ {
+ "considered_execution_plans": "..."
+ },
+ {
+ "attaching_conditions_to_tables": {
+ "original_condition": "((`test`.`t1`.`v` = 'a') and (`test`.`t1`.`i2` = 1) and (`test`.`t1`.`i1` = 1) and (`test`.`t1`.`pk` < 3))",
+ "attached_conditions_computation": [
+ ] /* attached_conditions_computation */,
+ "attached_conditions_summary": [
+ {
+ "database": "test",
+ "table": "t1",
+ "attached": "((`test`.`t1`.`v` = 'a') and (`test`.`t1`.`i2` = 1) and (`test`.`t1`.`pk` < 3))"
+ }
+ ] /* attached_conditions_summary */
+ } /* attaching_conditions_to_tables */
+ },
+ {
+ "refine_plan": [
+ {
+ "database": "test",
+ "table": "t1"
+ }
+ ] /* refine_plan */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
+ }
+ ] /* steps */
+} 0 0
+
+DROP TABLE t1;
=== modified file 'sql/opt_range.cc'
--- a/sql/opt_range.cc 2011-05-13 14:17:54 +0000
+++ b/sql/opt_range.cc 2011-05-18 10:37:05 +0000
@@ -5196,17 +5196,12 @@ TRP_ROR_INTERSECT *get_best_covering_ror
ROR_SCAN_INFO **ror_scans_end= tree->ror_scans_end;
DBUG_ENTER("get_best_covering_ror_intersect");
-#if 0 // Jorgen will fix with BUG#12551047
- // None of our tests enter this function
- Opt_trace_object (¶m->thd->opt_trace).
- add("get_best_covering_roworder_intersect", true).
- add("untested_code", true).
- add("need_tracing",true);
-#endif
-
if (!param->thd->optimizer_switch_flag(OPTIMIZER_SWITCH_INDEX_MERGE_INTERSECT))
DBUG_RETURN(NULL);
+ Opt_trace_object trace_covering (¶m->thd->opt_trace,
+ "make_covering_roworder_intersect");
+
for (ROR_SCAN_INFO **scan= tree->ror_scans; scan != ror_scans_end; ++scan)
(*scan)->key_components=
param->table->key_info[(*scan)->keynr].key_parts;
@@ -5234,9 +5229,10 @@ TRP_ROR_INTERSECT *get_best_covering_ror
bool all_covered;
DBUG_PRINT("info", ("Building covering ROR-intersection"));
- DBUG_EXECUTE("info", print_ror_scans_arr(param->table,
- "building covering ROR-I",
- ror_scan_mark, ror_scans_end););
+
+ // Note: trace_idx.end() is called to close this object after this loop.
+ Opt_trace_array trace_idx(¶m->thd->opt_trace, "included_indices");
+
do
{
/*
@@ -5257,33 +5253,55 @@ TRP_ROR_INTERSECT *get_best_covering_ror
my_qsort(ror_scan_mark, ror_scans_end-ror_scan_mark, sizeof(ROR_SCAN_INFO*),
(qsort_cmp)cmp_ror_scan_info_covering);
- DBUG_EXECUTE("info", print_ror_scans_arr(param->table,
- "remaining scans",
- ror_scan_mark, ror_scans_end););
-
/* I=I-first(I) */
total_cost += (*ror_scan_mark)->index_read_cost;
records += (*ror_scan_mark)->records;
- DBUG_PRINT("info", ("Adding scan on %s",
- param->table->key_info[(*ror_scan_mark)->keynr].name));
+
+ trace_idx.add_utf8(param->table->key_info[(*ror_scan_mark)->keynr].name);
+
if (total_cost > read_time)
+ {
+ trace_idx.end();
+ trace_covering.add("chosen", false).add_alnum("cause", "cost");
DBUG_RETURN(NULL);
+ }
/* F=F-covered by first(I) */
bitmap_union(covered_fields, &(*ror_scan_mark)->covered_fields);
all_covered= bitmap_is_subset(¶m->needed_fields, covered_fields);
} while ((++ror_scan_mark < ror_scans_end) && !all_covered);
+
+ trace_idx.end();
- if (!all_covered || (ror_scan_mark - tree->ror_scans) == 1)
+ if (!all_covered)
+ {
+ trace_covering.add("covering", false).add("chosen", false);
DBUG_RETURN(NULL);
+ }
+ else
+ {
+ trace_covering.add("covering", true);
+ if (ror_scan_mark < ror_scans_end)
+ {
+ Opt_trace_array ota(¶m->thd->opt_trace, "not_included_indices");
+
+ ROR_SCAN_INFO **remaining_idx= ror_scan_mark;
+ for (;remaining_idx < ror_scans_end; remaining_idx++)
+ ota.add_utf8(param->table->key_info[(*remaining_idx)->keynr].name);
+ }
+ }
+
+ if ((ror_scan_mark - tree->ror_scans) == 1)
+ {
+ trace_covering.add("chosen", false).
+ add_alnum("cause", "only_one_index");
+ DBUG_RETURN(NULL);
+ }
/*
Ok, [tree->ror_scans .. ror_scan) holds covering index_intersection with
cost total_cost.
*/
DBUG_PRINT("info", ("Covering ROR-intersect scans cost: %g", total_cost));
- DBUG_EXECUTE("info", print_ror_scans_arr(param->table,
- "creating covering ROR-intersect",
- tree->ror_scans, ror_scan_mark););
/* Add priority queue use cost. */
total_cost += rows2double(records) *
@@ -5292,7 +5310,10 @@ TRP_ROR_INTERSECT *get_best_covering_ror
DBUG_PRINT("info", ("Covering ROR-intersect full cost: %g", total_cost));
if (total_cost > read_time)
+ {
+ trace_covering.add("chosen", false).add_alnum("cause", "cost");
DBUG_RETURN(NULL);
+ }
TRP_ROR_INTERSECT *trp;
if (!(trp= new (param->mem_root) TRP_ROR_INTERSECT))
@@ -5310,9 +5331,10 @@ TRP_ROR_INTERSECT *get_best_covering_ror
trp->cpk_scan= NULL;
set_if_smaller(param->table->quick_condition_rows, records);
- DBUG_PRINT("info",
- ("Returning covering ROR-intersect plan: cost %g, records %lu",
- trp->read_cost, (ulong) trp->records));
+ trace_covering.add("records", trp->records).
+ add("cost", trp->read_cost).
+ add("chosen", true);
+
DBUG_RETURN(trp);
}
No bundle (reason: useless for push emails).
| Thread |
|---|
| • bzr push into mysql-trunk branch (jorgen.loland:3306 to 3307) Bug#12551047 | Jorgen Loland | 19 May |
