List:Commits« Previous MessageNext Message »
From:Jorgen Loland Date:March 15 2011 1:31pm
Subject:bzr commit into mysql-trunk branch (jorgen.loland:3279)
View as plain text  
#At file:///export/home/jl208045/mysql/wl4800/mysql-next-mr-opt-backporting-wl4800/ based on revid:guilhem.bichot@stripped

 3279 Jorgen Loland	2011-03-15
      Added test case for multiple ranges over a multi-keypart range
      
      Trace failed to print all but the final range in the 
      chosen_range_access_summary{} section of the optimizer trace. 
      Fixed by navigating to the first range interval before printing 
      ranges.

    modified:
      mysql-test/include/optimizer_trace_range.inc
      mysql-test/r/optimizer_trace_range_no_prot.result
      mysql-test/r/optimizer_trace_range_ps_prot.result
      sql/opt_range.cc
=== modified file 'mysql-test/include/optimizer_trace_range.inc'
--- a/mysql-test/include/optimizer_trace_range.inc	2011-01-18 13:00:16 +0000
+++ b/mysql-test/include/optimizer_trace_range.inc	2011-03-15 13:31:03 +0000
@@ -120,6 +120,13 @@ EXPLAIN SELECT * FROM t2 WHERE key1a = 5
 --echo
 SELECT * FROM information_schema.OPTIMIZER_TRACE;
 
+# Multiple ranges on key parts in same index
+--echo
+EXPLAIN SELECT * FROM t2 WHERE (key1a = 5 and key1b < 10 and key1b > 2) or
+                               (key1a = 4 and key1b < 7 and key1b > 3);    
+--echo
+SELECT * FROM information_schema.OPTIMIZER_TRACE;
+
 # more_expensive_than_table_scan
 --echo
 EXPLAIN SELECT * FROM t1 WHERE (key1 > 1 OR key2  > 2);

=== modified file 'mysql-test/r/optimizer_trace_range_no_prot.result'
--- a/mysql-test/r/optimizer_trace_range_no_prot.result	2011-03-01 10:55:27 +0000
+++ b/mysql-test/r/optimizer_trace_range_no_prot.result	2011-03-15 13:31:03 +0000
@@ -2111,6 +2111,207 @@ EXPLAIN SELECT * FROM t2 WHERE key1a = 5
   ] /* steps */
 }	0
 
+EXPLAIN SELECT * FROM t2 WHERE (key1a = 5 and key1b < 10 and key1b > 2) or
+(key1a = 4 and key1b < 7 and key1b > 3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	range	PRIMARY,i1b	PRIMARY	8	NULL	2	Using index condition
+
+SELECT * FROM information_schema.OPTIMIZER_TRACE;
+QUERY	TRACE	MISSING_BYTES_BEYOND_MAX_MEM_SIZE
+EXPLAIN SELECT * FROM t2 WHERE (key1a = 5 and key1b < 10 and key1b > 2) or
+(key1a = 4 and key1b < 7 and key1b > 3)	{
+  "steps": [
+    {
+      "join_preparation": {
+        "select#": 1,
+        "steps": [
+          {
+            "expanded_query": "/* select#1 */ select `test`.`t2`.`key1a` AS `key1a`,`test`.`t2`.`key1b` AS `key1b`,`test`.`t2`.`key2` AS `key2`,`test`.`t2`.`key2_1` AS `key2_1`,`test`.`t2`.`key2_2` AS `key2_2`,`test`.`t2`.`key3` AS `key3` from `test`.`t2` where (((`test`.`t2`.`key1a` = 5) and (`test`.`t2`.`key1b` < 10) and (`test`.`t2`.`key1b` > 2)) or ((`test`.`t2`.`key1a` = 4) and (`test`.`t2`.`key1b` < 7) and (`test`.`t2`.`key1b` > 3)))"
+          }
+        ] /* steps */
+      } /* join_preparation */
+    },
+    {
+      "join_optimization": {
+        "select#": 1,
+        "steps": [
+          {
+            "condition_processing": {
+              "condition": "WHERE",
+              "original_condition": "(((`test`.`t2`.`key1a` = 5) and (`test`.`t2`.`key1b` < 10) and (`test`.`t2`.`key1b` > 2)) or ((`test`.`t2`.`key1a` = 4) and (`test`.`t2`.`key1b` < 7) and (`test`.`t2`.`key1b` > 3)))",
+              "steps": [
+                {
+                  "transformation": "equality_propagation",
+                  "resulting_condition": "(((`test`.`t2`.`key1b` < 10) and (`test`.`t2`.`key1b` > 2) and multiple equal(5, `test`.`t2`.`key1a`)) or ((`test`.`t2`.`key1b` < 7) and (`test`.`t2`.`key1b` > 3) and multiple equal(4, `test`.`t2`.`key1a`)))"
+                },
+                {
+                  "transformation": "constant_propagation",
+                  "resulting_condition": "(((`test`.`t2`.`key1b` < 10) and (`test`.`t2`.`key1b` > 2) and multiple equal(5, `test`.`t2`.`key1a`)) or ((`test`.`t2`.`key1b` < 7) and (`test`.`t2`.`key1b` > 3) and multiple equal(4, `test`.`t2`.`key1a`)))"
+                },
+                {
+                  "transformation": "trivial_condition_removal",
+                  "resulting_condition": "(((`test`.`t2`.`key1b` < 10) and (`test`.`t2`.`key1b` > 2) and multiple equal(5, `test`.`t2`.`key1a`)) or ((`test`.`t2`.`key1b` < 7) and (`test`.`t2`.`key1b` > 3) and multiple equal(4, `test`.`t2`.`key1a`)))"
+                }
+              ] /* steps */
+            } /* condition_processing */
+          },
+          {
+            "ref_optimizer_key_uses": [
+            ] /* ref_optimizer_key_uses */
+          },
+          {
+            "records_estimation": [
+              {
+                "database": "test",
+                "table": "t2",
+                "range_analysis": {
+                  "table_scan": {
+                    "records": 1024,
+                    "cost": 215.15
+                  } /* table_scan */,
+                  "potential_range_indices": [
+                    {
+                      "index": "PRIMARY",
+                      "usable": true,
+                      "key_parts": [
+                        "key1a",
+                        "key1b"
+                      ] /* key_parts */
+                    },
+                    {
+                      "index": "i1b",
+                      "usable": true,
+                      "key_parts": [
+                        "key1b",
+                        "key1a"
+                      ] /* key_parts */
+                    },
+                    {
+                      "index": "i2_1",
+                      "usable": false,
+                      "cause": "not_applicable"
+                    },
+                    {
+                      "index": "i2_2",
+                      "usable": false,
+                      "cause": "not_applicable"
+                    }
+                  ] /* potential_range_indices */,
+                  "setup_range_conditions": [
+                  ] /* setup_range_conditions */,
+                  "group_index_range": {
+                    "chosen": false,
+                    "cause": "not_group_by_or_distinct"
+                  } /* group_index_range */,
+                  "analyzing_range_alternatives": {
+                    "range_scan_alternatives": [
+                      {
+                        "index": "PRIMARY",
+                        "ranges": [
+                          "4 <= key1a <= 4 AND 3 < key1b < 7",
+                          "5 <= key1a <= 5 AND 2 < key1b < 10"
+                        ] /* ranges */,
+                        "index_only": false,
+                        "records": 2,
+                        "cost": 4.41,
+                        "rowid_ordered": false,
+                        "chosen": true
+                      },
+                      {
+                        "index": "i1b",
+                        "ranges": [
+                          "2 < key1b <= 3 AND 5 <= key1a <= 5",
+                          "3 < key1b < 7",
+                          "7 <= key1b < 10 AND 5 <= key1a <= 5"
+                        ] /* ranges */,
+                        "index_only": false,
+                        "records": 6,
+                        "cost": 10.21,
+                        "rowid_ordered": false,
+                        "chosen": false,
+                        "cause": "cost"
+                      }
+                    ] /* range_scan_alternatives */,
+                    "analyzing_roworder_intersect": {
+                      "usable": false,
+                      "cause": "too_few_roworder_scans"
+                    } /* analyzing_roworder_intersect */
+                  } /* analyzing_range_alternatives */,
+                  "chosen_range_access_summary": {
+                    "range_access_plan": {
+                      "type": "range_scan",
+                      "index": "PRIMARY",
+                      "records": 2,
+                      "ranges": [
+                        "4 <= key1a <= 4 AND 3 < key1b < 7",
+                        "5 <= key1a <= 5 AND 2 < key1b < 10"
+                      ] /* ranges */
+                    } /* range_access_plan */,
+                    "records_for_plan": 2,
+                    "cost_for_plan": 4.41,
+                    "chosen": true
+                  } /* chosen_range_access_summary */
+                } /* range_analysis */
+              }
+            ] /* records_estimation */
+          },
+          {
+            "considered_execution_plans": [
+              {
+                "database": "test",
+                "table": "t2",
+                "best_access_path": {
+                  "considered_access_paths": [
+                    {
+                      "access_type": "range",
+                      "records": 2,
+                      "cost": 4.41,
+                      "chosen": true
+                    }
+                  ] /* considered_access_paths */
+                } /* best_access_path */,
+                "cost_for_plan": 4.41,
+                "records_for_plan": 2,
+                "chosen": true
+              }
+            ] /* considered_execution_plans */
+          },
+          {
+            "attaching_conditions_to_tables": {
+              "original_condition": "(((`test`.`t2`.`key1a` = 5) and (`test`.`t2`.`key1b` < 10) and (`test`.`t2`.`key1b` > 2)) or ((`test`.`t2`.`key1a` = 4) and (`test`.`t2`.`key1b` < 7) and (`test`.`t2`.`key1b` > 3)))",
+              "attached_conditions_computation": [
+              ] /* attached_conditions_computation */,
+              "attached_conditions_summary": [
+                {
+                  "database": "test",
+                  "table": "t2",
+                  "attached": "(((`test`.`t2`.`key1a` = 5) and (`test`.`t2`.`key1b` < 10) and (`test`.`t2`.`key1b` > 2)) or ((`test`.`t2`.`key1a` = 4) and (`test`.`t2`.`key1b` < 7) and (`test`.`t2`.`key1b` > 3)))"
+                }
+              ] /* attached_conditions_summary */
+            } /* attaching_conditions_to_tables */
+          },
+          {
+            "refine_plan": [
+              {
+                "database": "test",
+                "table": "t2",
+                "scan_type": "table"
+              }
+            ] /* refine_plan */
+          }
+        ] /* steps */
+      } /* join_optimization */
+    },
+    {
+      "join_execution": {
+        "select#": 1,
+        "steps": [
+        ] /* steps */
+      } /* join_execution */
+    }
+  ] /* steps */
+}	0
+
 EXPLAIN SELECT * FROM t1 WHERE (key1 > 1 OR key2  > 2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	i1,i2	NULL	NULL	NULL	1024	Using where

=== modified file 'mysql-test/r/optimizer_trace_range_ps_prot.result'
--- a/mysql-test/r/optimizer_trace_range_ps_prot.result	2011-03-01 10:55:27 +0000
+++ b/mysql-test/r/optimizer_trace_range_ps_prot.result	2011-03-15 13:31:03 +0000
@@ -2111,6 +2111,207 @@ EXPLAIN SELECT * FROM t2 WHERE key1a = 5
   ] /* steps */
 }	0
 
+EXPLAIN SELECT * FROM t2 WHERE (key1a = 5 and key1b < 10 and key1b > 2) or
+(key1a = 4 and key1b < 7 and key1b > 3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	range	PRIMARY,i1b	PRIMARY	8	NULL	2	Using index condition
+
+SELECT * FROM information_schema.OPTIMIZER_TRACE;
+QUERY	TRACE	MISSING_BYTES_BEYOND_MAX_MEM_SIZE
+EXPLAIN SELECT * FROM t2 WHERE (key1a = 5 and key1b < 10 and key1b > 2) or
+(key1a = 4 and key1b < 7 and key1b > 3)	{
+  "steps": [
+    {
+      "join_preparation": {
+        "select#": 1,
+        "steps": [
+          {
+            "expanded_query": "/* select#1 */ select `test`.`t2`.`key1a` AS `key1a`,`test`.`t2`.`key1b` AS `key1b`,`test`.`t2`.`key2` AS `key2`,`test`.`t2`.`key2_1` AS `key2_1`,`test`.`t2`.`key2_2` AS `key2_2`,`test`.`t2`.`key3` AS `key3` from `test`.`t2` where (((`test`.`t2`.`key1a` = 5) and (`test`.`t2`.`key1b` < 10) and (`test`.`t2`.`key1b` > 2)) or ((`test`.`t2`.`key1a` = 4) and (`test`.`t2`.`key1b` < 7) and (`test`.`t2`.`key1b` > 3)))"
+          }
+        ] /* steps */
+      } /* join_preparation */
+    },
+    {
+      "join_optimization": {
+        "select#": 1,
+        "steps": [
+          {
+            "condition_processing": {
+              "condition": "WHERE",
+              "original_condition": "(((`test`.`t2`.`key1a` = 5) and (`test`.`t2`.`key1b` < 10) and (`test`.`t2`.`key1b` > 2)) or ((`test`.`t2`.`key1a` = 4) and (`test`.`t2`.`key1b` < 7) and (`test`.`t2`.`key1b` > 3)))",
+              "steps": [
+                {
+                  "transformation": "equality_propagation",
+                  "resulting_condition": "(((`test`.`t2`.`key1b` < 10) and (`test`.`t2`.`key1b` > 2) and multiple equal(5, `test`.`t2`.`key1a`)) or ((`test`.`t2`.`key1b` < 7) and (`test`.`t2`.`key1b` > 3) and multiple equal(4, `test`.`t2`.`key1a`)))"
+                },
+                {
+                  "transformation": "constant_propagation",
+                  "resulting_condition": "(((`test`.`t2`.`key1b` < 10) and (`test`.`t2`.`key1b` > 2) and multiple equal(5, `test`.`t2`.`key1a`)) or ((`test`.`t2`.`key1b` < 7) and (`test`.`t2`.`key1b` > 3) and multiple equal(4, `test`.`t2`.`key1a`)))"
+                },
+                {
+                  "transformation": "trivial_condition_removal",
+                  "resulting_condition": "(((`test`.`t2`.`key1b` < 10) and (`test`.`t2`.`key1b` > 2) and multiple equal(5, `test`.`t2`.`key1a`)) or ((`test`.`t2`.`key1b` < 7) and (`test`.`t2`.`key1b` > 3) and multiple equal(4, `test`.`t2`.`key1a`)))"
+                }
+              ] /* steps */
+            } /* condition_processing */
+          },
+          {
+            "ref_optimizer_key_uses": [
+            ] /* ref_optimizer_key_uses */
+          },
+          {
+            "records_estimation": [
+              {
+                "database": "test",
+                "table": "t2",
+                "range_analysis": {
+                  "table_scan": {
+                    "records": 1024,
+                    "cost": 215.15
+                  } /* table_scan */,
+                  "potential_range_indices": [
+                    {
+                      "index": "PRIMARY",
+                      "usable": true,
+                      "key_parts": [
+                        "key1a",
+                        "key1b"
+                      ] /* key_parts */
+                    },
+                    {
+                      "index": "i1b",
+                      "usable": true,
+                      "key_parts": [
+                        "key1b",
+                        "key1a"
+                      ] /* key_parts */
+                    },
+                    {
+                      "index": "i2_1",
+                      "usable": false,
+                      "cause": "not_applicable"
+                    },
+                    {
+                      "index": "i2_2",
+                      "usable": false,
+                      "cause": "not_applicable"
+                    }
+                  ] /* potential_range_indices */,
+                  "setup_range_conditions": [
+                  ] /* setup_range_conditions */,
+                  "group_index_range": {
+                    "chosen": false,
+                    "cause": "not_group_by_or_distinct"
+                  } /* group_index_range */,
+                  "analyzing_range_alternatives": {
+                    "range_scan_alternatives": [
+                      {
+                        "index": "PRIMARY",
+                        "ranges": [
+                          "4 <= key1a <= 4 AND 3 < key1b < 7",
+                          "5 <= key1a <= 5 AND 2 < key1b < 10"
+                        ] /* ranges */,
+                        "index_only": false,
+                        "records": 2,
+                        "cost": 4.41,
+                        "rowid_ordered": false,
+                        "chosen": true
+                      },
+                      {
+                        "index": "i1b",
+                        "ranges": [
+                          "2 < key1b <= 3 AND 5 <= key1a <= 5",
+                          "3 < key1b < 7",
+                          "7 <= key1b < 10 AND 5 <= key1a <= 5"
+                        ] /* ranges */,
+                        "index_only": false,
+                        "records": 6,
+                        "cost": 10.21,
+                        "rowid_ordered": false,
+                        "chosen": false,
+                        "cause": "cost"
+                      }
+                    ] /* range_scan_alternatives */,
+                    "analyzing_roworder_intersect": {
+                      "usable": false,
+                      "cause": "too_few_roworder_scans"
+                    } /* analyzing_roworder_intersect */
+                  } /* analyzing_range_alternatives */,
+                  "chosen_range_access_summary": {
+                    "range_access_plan": {
+                      "type": "range_scan",
+                      "index": "PRIMARY",
+                      "records": 2,
+                      "ranges": [
+                        "4 <= key1a <= 4 AND 3 < key1b < 7",
+                        "5 <= key1a <= 5 AND 2 < key1b < 10"
+                      ] /* ranges */
+                    } /* range_access_plan */,
+                    "records_for_plan": 2,
+                    "cost_for_plan": 4.41,
+                    "chosen": true
+                  } /* chosen_range_access_summary */
+                } /* range_analysis */
+              }
+            ] /* records_estimation */
+          },
+          {
+            "considered_execution_plans": [
+              {
+                "database": "test",
+                "table": "t2",
+                "best_access_path": {
+                  "considered_access_paths": [
+                    {
+                      "access_type": "range",
+                      "records": 2,
+                      "cost": 4.41,
+                      "chosen": true
+                    }
+                  ] /* considered_access_paths */
+                } /* best_access_path */,
+                "cost_for_plan": 4.41,
+                "records_for_plan": 2,
+                "chosen": true
+              }
+            ] /* considered_execution_plans */
+          },
+          {
+            "attaching_conditions_to_tables": {
+              "original_condition": "(((`test`.`t2`.`key1a` = 5) and (`test`.`t2`.`key1b` < 10) and (`test`.`t2`.`key1b` > 2)) or ((`test`.`t2`.`key1a` = 4) and (`test`.`t2`.`key1b` < 7) and (`test`.`t2`.`key1b` > 3)))",
+              "attached_conditions_computation": [
+              ] /* attached_conditions_computation */,
+              "attached_conditions_summary": [
+                {
+                  "database": "test",
+                  "table": "t2",
+                  "attached": "(((`test`.`t2`.`key1a` = 5) and (`test`.`t2`.`key1b` < 10) and (`test`.`t2`.`key1b` > 2)) or ((`test`.`t2`.`key1a` = 4) and (`test`.`t2`.`key1b` < 7) and (`test`.`t2`.`key1b` > 3)))"
+                }
+              ] /* attached_conditions_summary */
+            } /* attaching_conditions_to_tables */
+          },
+          {
+            "refine_plan": [
+              {
+                "database": "test",
+                "table": "t2",
+                "scan_type": "table"
+              }
+            ] /* refine_plan */
+          }
+        ] /* steps */
+      } /* join_optimization */
+    },
+    {
+      "join_execution": {
+        "select#": 1,
+        "steps": [
+        ] /* steps */
+      } /* join_execution */
+    }
+  ] /* steps */
+}	0
+
 EXPLAIN SELECT * FROM t1 WHERE (key1 > 1 OR key2  > 2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	i1,i2	NULL	NULL	NULL	1024	Using where

=== modified file 'sql/opt_range.cc'
--- a/sql/opt_range.cc	2011-02-16 15:06:06 +0000
+++ b/sql/opt_range.cc	2011-03-15 13:31:03 +0000
@@ -2018,13 +2018,17 @@ void TRP_RANGE::trace_basic_info(const P
     add_utf8("index", cur_key.name).add("records", records);
 
   Opt_trace_array trace_range(param->thd->opt_trace, "ranges");
-  for (const SEL_ARG *current= key;
-       current;
-       current= current->next)
+
+  const SEL_ARG *current_range= key;
+  // make current_range point to the first interval
+  while (current_range->prev)
+    current_range= current_range->prev;
+
+  while (current_range)
   {
     String range_info;
     range_info.set_charset(system_charset_info);
-    for (const SEL_ARG *part= current;
+    for (const SEL_ARG *part= current_range;
          part;
          part= part->next_key_part)
     {
@@ -2034,6 +2038,7 @@ void TRP_RANGE::trace_basic_info(const P
                    part->min_flag | part->max_flag);
     }
     trace_range.add_utf8(range_info.ptr(), range_info.length());
+    current_range= current_range->next;
   }
 #endif
 }


Attachment: [text/bzr-bundle] bzr/jorgen.loland@oracle.com-20110315133103-ldnelxkial1qrfn4.bundle
Thread
bzr commit into mysql-trunk branch (jorgen.loland:3279) Jorgen Loland15 Mar
  • Re: bzr commit into mysql-trunk branch (jorgen.loland:3279)Guilhem Bichot15 Mar
    • Re: bzr commit into mysql-trunk branch (jorgen.loland:3279)Jorgen Loland16 Mar
      • Re: bzr commit into mysql-trunk branch (jorgen.loland:3279)Jorgen Loland16 Mar
      • Re: bzr commit into mysql-trunk branch (jorgen.loland:3279)Guilhem Bichot18 Mar