From: Jorgen Loland Date: May 18 2011 10:37am Subject: bzr push into mysql-trunk branch (jorgen.loland:3306 to 3307) Bug#12551047 List-Archive: http://lists.mysql.com/commits/137641 X-Bug: 12551047 Message-Id: <20110518103724.E25A6B54@atum21.norway.sun.com> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 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).