List:Commits« Previous MessageNext Message »
From:Jorgen Loland Date:May 18 2011 7:22am
Subject:bzr commit into mysql-trunk branch (jorgen.loland:3307)
View as plain text  
#At file:///export/home/jl208045/mysql/wl4800/mysql-next-mr-opt-backporting-wl4800-12551047/ based on revid:guilhem.bichot@stripped

 3307 Jorgen Loland	2011-05-18
      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 entering
      this function when doing RQG testing, so now tracing could be 
      fixed and a few test cases added to optimizer_trace_range*

    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-04-06 09:19:23 +0000
+++ b/mysql-test/include/optimizer_trace_range.inc	2011-05-18 07:22:29 +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
+# 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
+# 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 choses 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 07:22:29 +0000
@@ -4920,3 +4920,517 @@ 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');
+
+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 */,
+                      "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);
+
+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 07:22:29 +0000
@@ -4912,3 +4912,517 @@ 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');
+
+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 */,
+                      "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);
+
+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 07:22:29 +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 (&param->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 (&param->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;
@@ -5237,6 +5232,10 @@ TRP_ROR_INTERSECT *get_best_covering_ror
   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(&param->thd->opt_trace, "included_indices");
+
   do
   {
     /*
@@ -5264,17 +5263,37 @@ TRP_ROR_INTERSECT *get_best_covering_ror
     /* I=I-first(I) */
     total_cost += (*ror_scan_mark)->index_read_cost;
     records += (*ror_scan_mark)->records;
+
+    trace_idx.add_utf8(param->table->key_info[(*ror_scan_mark)->keynr].name);
+
     DBUG_PRINT("info", ("Adding scan on %s",
                         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(&param->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("chosen", false).
+      add_alnum("cause", "not_completely_covering");
+    DBUG_RETURN(NULL);
+  }
+
+  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
@@ -5292,7 +5311,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))
@@ -5313,6 +5335,11 @@ TRP_ROR_INTERSECT *get_best_covering_ror
   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);
 }
 


Attachment: [text/bzr-bundle] bzr/jorgen.loland@oracle.com-20110518072229-od9z0djfndtec94p.bundle
Thread
bzr commit into mysql-trunk branch (jorgen.loland:3307) Jorgen Loland19 May