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

 3288 Jorgen Loland	2011-04-06
      Fix optimizer tracing in range access plan analysis:
      
      Fixed misunderstanding: SEL_ARG::next_key_part does not
      point to a single SEL_ARG but to the root of a SEL_ARG tree
       * Old optimizer tracing code that effectively 
         printed all intervals of the first keypart and appended it
         with only the root of consecutive keyparts is replaced with
         recursive function that combines a range over the current
         keypart with all ranges in consecutive keyparts
       * Fixed documentation that describes how SEL_ARG red-black
         trees are structured.
       * Added test case that used to fail to print all ranges for
         a range access plan.
      
      Bonus: With new understanding of how SEL_ARG R-B trees are 
      structured, it was possible to:
       * Remove tracing from sel_arg_range_seq_next() (and all
         RANGE_SEQ_IF::next() implementations) and step_down_to().
         This was replaced with tracing of SEL_ARG trees in the 
         range optimizer.

    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/handler.cc
      sql/handler.h
      sql/opt_range.cc
      sql/opt_range.h
      sql/sql_join_cache.cc
=== modified file 'mysql-test/include/optimizer_trace_range.inc'
--- a/mysql-test/include/optimizer_trace_range.inc	2011-03-21 09:01:05 +0000
+++ b/mysql-test/include/optimizer_trace_range.inc	2011-04-06 09:19:23 +0000
@@ -129,6 +129,13 @@ EXPLAIN SELECT * FROM t2 WHERE (key1a = 
 --echo
 SELECT * FROM information_schema.OPTIMIZER_TRACE;
 
+# Multiple ranges on key parts in same index
+--echo
+EXPLAIN SELECT * FROM t2 WHERE (key1b < 10 and key1b > 7) and 
+                               (key1a = 4 or key1a = 5);
+--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-21 20:50:40 +0000
+++ b/mysql-test/r/optimizer_trace_range_no_prot.result	2011-04-06 09:19:23 +0000
@@ -2340,6 +2340,205 @@ EXPLAIN SELECT * FROM t2 WHERE (key1a = 
   ] /* steps */
 }	0
 
+EXPLAIN SELECT * FROM t2 WHERE (key1b < 10 and key1b > 7) and 
+(key1a = 4 or key1a = 5);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	range	PRIMARY,i1b	i1b	4	NULL	2	Using index condition
+
+SELECT * FROM information_schema.OPTIMIZER_TRACE;
+QUERY	TRACE	MISSING_BYTES_BEYOND_MAX_MEM_SIZE
+EXPLAIN SELECT * FROM t2 WHERE (key1b < 10 and key1b > 7) and 
+(key1a = 4 or key1a = 5)	{
+  "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`.`key1b` < 10) and (`test`.`t2`.`key1b` > 7) and ((`test`.`t2`.`key1a` = 4) or (`test`.`t2`.`key1a` = 5)))"
+          }
+        ] /* steps */
+      } /* join_preparation */
+    },
+    {
+      "join_optimization": {
+        "select#": 1,
+        "steps": [
+          {
+            "condition_processing": {
+              "condition": "WHERE",
+              "original_condition": "((`test`.`t2`.`key1b` < 10) and (`test`.`t2`.`key1b` > 7) and ((`test`.`t2`.`key1a` = 4) or (`test`.`t2`.`key1a` = 5)))",
+              "steps": [
+                {
+                  "transformation": "equality_propagation",
+                  "resulting_condition": "((`test`.`t2`.`key1b` < 10) and (`test`.`t2`.`key1b` > 7) and (multiple equal(4, `test`.`t2`.`key1a`) or multiple equal(5, `test`.`t2`.`key1a`)))"
+                },
+                {
+                  "transformation": "constant_propagation",
+                  "resulting_condition": "((`test`.`t2`.`key1b` < 10) and (`test`.`t2`.`key1b` > 7) and (multiple equal(4, `test`.`t2`.`key1a`) or multiple equal(5, `test`.`t2`.`key1a`)))"
+                },
+                {
+                  "transformation": "trivial_condition_removal",
+                  "resulting_condition": "((`test`.`t2`.`key1b` < 10) and (`test`.`t2`.`key1b` > 7) and (multiple equal(4, `test`.`t2`.`key1a`) or multiple equal(5, `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 7 < key1b < 10",
+                          "5 <= key1a <= 5 AND 7 < key1b < 10"
+                        ] /* ranges */,
+                        "index_only": false,
+                        "records": 2,
+                        "cost": 4.41,
+                        "rowid_ordered": false,
+                        "chosen": true
+                      },
+                      {
+                        "index": "i1b",
+                        "ranges": [
+                          "7 < key1b < 10 AND 4 <= key1a <= 4",
+                          "7 < key1b < 10 AND 5 <= key1a <= 5"
+                        ] /* ranges */,
+                        "index_only": false,
+                        "records": 2,
+                        "cost": 3.41,
+                        "rowid_ordered": false,
+                        "chosen": true
+                      }
+                    ] /* 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": "i1b",
+                      "records": 2,
+                      "ranges": [
+                        "7 < key1b < 10 AND 4 <= key1a <= 4",
+                        "7 < key1b < 10 AND 5 <= key1a <= 5"
+                      ] /* ranges */
+                    } /* range_access_plan */,
+                    "records_for_plan": 2,
+                    "cost_for_plan": 3.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": 3.41,
+                      "chosen": true
+                    }
+                  ] /* considered_access_paths */
+                } /* best_access_path */,
+                "cost_for_plan": 3.81,
+                "records_for_plan": 2,
+                "chosen": true
+              }
+            ] /* considered_execution_plans */
+          },
+          {
+            "attaching_conditions_to_tables": {
+              "original_condition": "((`test`.`t2`.`key1b` < 10) and (`test`.`t2`.`key1b` > 7) and ((`test`.`t2`.`key1a` = 4) or (`test`.`t2`.`key1a` = 5)))",
+              "attached_conditions_computation": [
+              ] /* attached_conditions_computation */,
+              "attached_conditions_summary": [
+                {
+                  "database": "test",
+                  "table": "t2",
+                  "attached": "((`test`.`t2`.`key1b` < 10) and (`test`.`t2`.`key1b` > 7) and ((`test`.`t2`.`key1a` = 4) or (`test`.`t2`.`key1a` = 5)))"
+                }
+              ] /* 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-21 20:50:40 +0000
+++ b/mysql-test/r/optimizer_trace_range_ps_prot.result	2011-04-06 09:19:23 +0000
@@ -2340,6 +2340,205 @@ EXPLAIN SELECT * FROM t2 WHERE (key1a = 
   ] /* steps */
 }	0
 
+EXPLAIN SELECT * FROM t2 WHERE (key1b < 10 and key1b > 7) and 
+(key1a = 4 or key1a = 5);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	range	PRIMARY,i1b	i1b	4	NULL	2	Using index condition
+
+SELECT * FROM information_schema.OPTIMIZER_TRACE;
+QUERY	TRACE	MISSING_BYTES_BEYOND_MAX_MEM_SIZE
+EXPLAIN SELECT * FROM t2 WHERE (key1b < 10 and key1b > 7) and 
+(key1a = 4 or key1a = 5)	{
+  "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`.`key1b` < 10) and (`test`.`t2`.`key1b` > 7) and ((`test`.`t2`.`key1a` = 4) or (`test`.`t2`.`key1a` = 5)))"
+          }
+        ] /* steps */
+      } /* join_preparation */
+    },
+    {
+      "join_optimization": {
+        "select#": 1,
+        "steps": [
+          {
+            "condition_processing": {
+              "condition": "WHERE",
+              "original_condition": "((`test`.`t2`.`key1b` < 10) and (`test`.`t2`.`key1b` > 7) and ((`test`.`t2`.`key1a` = 4) or (`test`.`t2`.`key1a` = 5)))",
+              "steps": [
+                {
+                  "transformation": "equality_propagation",
+                  "resulting_condition": "((`test`.`t2`.`key1b` < 10) and (`test`.`t2`.`key1b` > 7) and (multiple equal(4, `test`.`t2`.`key1a`) or multiple equal(5, `test`.`t2`.`key1a`)))"
+                },
+                {
+                  "transformation": "constant_propagation",
+                  "resulting_condition": "((`test`.`t2`.`key1b` < 10) and (`test`.`t2`.`key1b` > 7) and (multiple equal(4, `test`.`t2`.`key1a`) or multiple equal(5, `test`.`t2`.`key1a`)))"
+                },
+                {
+                  "transformation": "trivial_condition_removal",
+                  "resulting_condition": "((`test`.`t2`.`key1b` < 10) and (`test`.`t2`.`key1b` > 7) and (multiple equal(4, `test`.`t2`.`key1a`) or multiple equal(5, `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 7 < key1b < 10",
+                          "5 <= key1a <= 5 AND 7 < key1b < 10"
+                        ] /* ranges */,
+                        "index_only": false,
+                        "records": 2,
+                        "cost": 4.41,
+                        "rowid_ordered": false,
+                        "chosen": true
+                      },
+                      {
+                        "index": "i1b",
+                        "ranges": [
+                          "7 < key1b < 10 AND 4 <= key1a <= 4",
+                          "7 < key1b < 10 AND 5 <= key1a <= 5"
+                        ] /* ranges */,
+                        "index_only": false,
+                        "records": 2,
+                        "cost": 3.41,
+                        "rowid_ordered": false,
+                        "chosen": true
+                      }
+                    ] /* 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": "i1b",
+                      "records": 2,
+                      "ranges": [
+                        "7 < key1b < 10 AND 4 <= key1a <= 4",
+                        "7 < key1b < 10 AND 5 <= key1a <= 5"
+                      ] /* ranges */
+                    } /* range_access_plan */,
+                    "records_for_plan": 2,
+                    "cost_for_plan": 3.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": 3.41,
+                      "chosen": true
+                    }
+                  ] /* considered_access_paths */
+                } /* best_access_path */,
+                "cost_for_plan": 3.81,
+                "records_for_plan": 2,
+                "chosen": true
+              }
+            ] /* considered_execution_plans */
+          },
+          {
+            "attaching_conditions_to_tables": {
+              "original_condition": "((`test`.`t2`.`key1b` < 10) and (`test`.`t2`.`key1b` > 7) and ((`test`.`t2`.`key1a` = 4) or (`test`.`t2`.`key1a` = 5)))",
+              "attached_conditions_computation": [
+              ] /* attached_conditions_computation */,
+              "attached_conditions_summary": [
+                {
+                  "database": "test",
+                  "table": "t2",
+                  "attached": "((`test`.`t2`.`key1b` < 10) and (`test`.`t2`.`key1b` > 7) and ((`test`.`t2`.`key1a` = 4) or (`test`.`t2`.`key1a` = 5)))"
+                }
+              ] /* 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/handler.cc'
--- a/sql/handler.cc	2011-03-21 17:55:41 +0000
+++ b/sql/handler.cc	2011-04-06 09:19:23 +0000
@@ -4514,8 +4514,7 @@ handler::multi_range_read_info_const(uin
   *bufsz= 0;
 
   seq_it= seq->init(seq_init_param, n_ranges, *flags);
-  Opt_trace_array ota(thd->opt_trace, "ranges");
-  while (!seq->next(seq_it, &range, &ota))
+  while (!seq->next(seq_it, &range))
   {
     if (unlikely(thd->killed != 0))
       return HA_POS_ERROR;
@@ -4715,7 +4714,7 @@ int handler::multi_range_read_next(char 
 
 start:
     /* Try the next range(s) until one matches a record. */
-    while (!(range_res= mrr_funcs.next(mrr_iter, &mrr_cur_range, NULL)))
+    while (!(range_res= mrr_funcs.next(mrr_iter, &mrr_cur_range)))
     {
 scan_it_again:
       result= read_range_first(mrr_cur_range.start_key.keypart_map ?

=== modified file 'sql/handler.h'
--- a/sql/handler.h	2011-03-22 10:08:27 +0000
+++ b/sql/handler.h	2011-04-06 09:19:23 +0000
@@ -1153,15 +1153,12 @@ typedef struct st_range_seq_if
       next()
         seq          The value returned by RANGE_SEQ_IF::init()
         range        OUT Information about the next range
-        trace_array  Optimizer trace-related information is appended to this if
-                     tracing is enabled. If NULL, no optimizer tracing is done
     
     RETURN
       0 - Ok, the range structure filled with info about the next range
       1 - No more ranges
   */
-  uint (*next) (range_seq_t seq, KEY_MULTI_RANGE *range, 
-                Opt_trace_array *trace_array);
+  uint (*next) (range_seq_t seq, KEY_MULTI_RANGE *range);
 
   /*
     Check whether range_info orders to skip the next record

=== modified file 'sql/opt_range.cc'
--- a/sql/opt_range.cc	2011-04-05 07:34:13 +0000
+++ b/sql/opt_range.cc	2011-04-06 09:19:23 +0000
@@ -235,15 +235,28 @@ class RANGE_OPT_PARAM;
 
   In red-black tree form:
 
-                     +------------------------------+
-                     | kp1=2 AND (kp3=11 OR kp3=14) |
-                     +------------------------------+
-                          /                    \
-                         /                      \
-         +------------------------+    +--------------------+
-         | kp1 < 1 AND kp2=5 AND  |    | kp1=3 AND          |
-         | (kp3=10 OR kp3=12)     |    | (kp3=11 OR kp3=14) |
-         +------------------------+    +--------------------+
+                     +-------+                 +--------+
+                     | kp1=2 |.................| kp3=14 |
+                     +-------+                 +--------+
+                      /     \                     /
+             +---------+    +-------+     +--------+
+             | kp1 < 1 |    | kp1=3 |     | kp3=11 |
+             +---------+    +-------+     +--------+
+                 .               .
+            ......               .......
+            .                          .
+        +-------+                  +--------+
+        | kp2=5 |                  | kp3=14 |
+        +-------+                  +--------+
+            .                        /
+            .                   +--------+
+       (root of R-B tree        | kp3=11 |
+        for "kp3={10|12}")      +--------+
+
+
+  Where / and \ denote left and right pointers and ... denotes
+  next_key_part pointers to the root of the R-B tree of intervals for
+  consecutive key parts.
 
   3. SEL_ARG GRAPH USE
 
@@ -372,6 +385,10 @@ public:
   SEL_ARG *left,*right;   /* R-B tree children */
   SEL_ARG *next,*prev;    /* Links for bi-directional interval list */
   SEL_ARG *parent;        /* R-B tree parent */
+  /* 
+    R-B tree root of intervals covering keyparts consecutive to this
+    SEL_ARG. See documentation of SEL_ARG GRAPH semantics for details.
+  */
   SEL_ARG *next_key_part; 
   enum leaf_color { BLACK,RED } color;
   enum Type { IMPOSSIBLE, MAYBE, MAYBE_KEY, KEY_RANGE } type;
@@ -843,6 +860,10 @@ static void print_quick(QUICK_SELECT_I *
 #endif
 
 #ifdef OPTIMIZER_TRACE
+static void trace_range_all_keyparts(Opt_trace_array &trace_range,
+                                     const String *range_so_far,
+                                     SEL_ARG *keypart_root,
+                                     const KEY_PART_INFO *key_parts);
 static void append_range(String *out,
                          const KEY_PART_INFO *key_parts,
                          const uchar *min_key, const uchar *max_key,
@@ -2063,27 +2084,13 @@ void TRP_RANGE::trace_basic_info(const P
 
   Opt_trace_array trace_range(param->thd->opt_trace, "ranges");
 
-  const SEL_ARG *current_range= key;
-  // navigate from root to first interval in the interval tree 
-  while (current_range->left && current_range->left != &null_element)
-    current_range= current_range->left;
+  // TRP_RANGE should not be created if there are no range intervals
+  DBUG_ASSERT(key);
+
+  String range_info;
+  range_info.set_charset(system_charset_info);
+  trace_range_all_keyparts(trace_range, &range_info, key, key_part);
 
-  while (current_range)
-  {
-    String range_info;
-    range_info.set_charset(system_charset_info);
-    for (const SEL_ARG *part= current_range;
-         part;
-         part= part->next_key_part)
-    {
-      const KEY_PART_INFO *cur_key_part= key_part + part->part;
-      append_range(&range_info, cur_key_part,
-                   part->min_value, part->max_value,
-                   part->min_flag | part->max_flag);
-    }
-    trace_range.add_utf8(range_info.ptr(), range_info.length());
-    current_range= current_range->next;
-  }
 #endif
 }
 
@@ -2345,28 +2352,12 @@ void TRP_GROUP_MIN_MAX::trace_basic_info
   }
   Opt_trace_array trace_range(param->thd->opt_trace, "ranges");
 
-
-  const SEL_ARG *current_range= index_tree;
-  // navigate from root to first interval in the interval tree 
-  while (current_range && // can have group quick without ranges
-         current_range->left && current_range->left != &null_element)
-    current_range= current_range->left;
-
-  while (current_range)
+  // can have group quick without ranges
+  if (index_tree)
   {
     String range_info;
     range_info.set_charset(system_charset_info);
-    for (const SEL_ARG *part= current_range;
-         part;
-         part= part->next_key_part)
-    {
-      const KEY_PART_INFO *cur_key_part= key_part + part->part;
-      append_range(&range_info, cur_key_part,
-                   part->min_value, part->max_value,
-                   part->min_flag | part->max_flag);
-    }
-    trace_range.add_utf8(range_info.ptr(), range_info.length());
-    current_range= current_range->next;
+    trace_range_all_keyparts(trace_range, &range_info, index_tree, key_part);
   }
 #endif
 }
@@ -5391,6 +5382,23 @@ static TRP_RANGE *get_key_scans_params(P
       found_records= check_quick_select(param, idx, read_index_only, *key,
                                         update_tbl_stats, &mrr_flags,
                                         &buf_size, &cost);
+      
+#ifdef OPTIMIZER_TRACE
+      // check_quick_select() says don't use range if it returns HA_POS_ERROR
+      if (found_records != HA_POS_ERROR && 
+          param->thd->opt_trace && param->thd->opt_trace->is_started()) 
+      { 
+        Opt_trace_array trace_range(param->thd->opt_trace, "ranges");
+        
+        const KEY &cur_key= param->table->key_info[keynr];
+        const KEY_PART_INFO *key_part= cur_key.key_part;
+
+        String range_info;
+        range_info.set_charset(system_charset_info);
+        trace_range_all_keyparts(trace_range, &range_info, *key, key_part);
+      }
+#endif
+
       trace_idx.add("index_only", read_index_only).
         add("records", found_records).
         add("cost", cost.total_cost());
@@ -8038,27 +8046,9 @@ range_seq_t sel_arg_range_seq_init(void 
                       condition to this String if not NULL. Used by optimizer
                       trace
 */
-static void step_down_to(String *trace_string, 
-                         SEL_ARG_RANGE_SEQ *arg, SEL_ARG *key_tree)
+static void step_down_to(SEL_ARG_RANGE_SEQ *arg, SEL_ARG *key_tree)
 {
 
-#ifdef OPTIMIZER_TRACE
-  if (trace_string != NULL)
-  {
-    /*
-       Stepping down will append the range for the current keypart (in
-       key_tree) to seq. Trace range here since this is where it is
-       human readable.
-    */
-    const KEY_PART_INFO *key_part=
-      arg->param->table->key_info[arg->real_keyno].key_part + key_tree->part;
-
-    append_range(trace_string, key_part,
-                 key_tree->min_value, key_tree->max_value,
-                 key_tree->min_flag | key_tree->max_flag);
-  }
-#endif
-
   RANGE_SEQ_ENTRY *cur= &arg->stack[arg->i+1];
   RANGE_SEQ_ENTRY *prev= &arg->stack[arg->i];
   
@@ -8115,24 +8105,10 @@ static void step_down_to(String *trace_s
 */
 
 //psergey-merge-todo: support check_quick_keys:max_keypart
-uint sel_arg_range_seq_next(range_seq_t rseq, KEY_MULTI_RANGE *range, 
-                            Opt_trace_array *trace_array)
+uint sel_arg_range_seq_next(range_seq_t rseq, KEY_MULTI_RANGE *range)
 {
   SEL_ARG *key_tree;
   SEL_ARG_RANGE_SEQ *seq= (SEL_ARG_RANGE_SEQ*)rseq;
-  String key_range_trace;
-  //points to key_range_trace if doing tracing, NULL otherwise
-  String *active_trace_ptr= NULL; 
-
-#ifdef OPTIMIZER_TRACE
-  if (unlikely(trace_array != NULL) &&
-      unlikely(seq->param->thd->opt_trace != NULL) &&
-      seq->param->thd->opt_trace->is_started())
-  {
-    key_range_trace.set_charset(system_charset_info);
-    active_trace_ptr= &key_range_trace;
-  }
-#endif
 
   if (seq->at_start)
   {
@@ -8150,7 +8126,7 @@ uint sel_arg_range_seq_next(range_seq_t 
     DBUG_ASSERT(key_tree->next != &null_element);
     //step down; (update the tuple, we'll step right and stay there)
     seq->i--;
-    step_down_to(active_trace_ptr, seq, key_tree->next);
+    step_down_to(seq, key_tree->next);
     key_tree= key_tree->next;
     seq->param->is_ror_scan= FALSE;
     goto walk_right_n_up;
@@ -8172,7 +8148,7 @@ uint sel_arg_range_seq_next(range_seq_t 
       DBUG_ASSERT(key_tree->next != &null_element);
       // Step down; update the tuple
       seq->i--;
-      step_down_to(active_trace_ptr, seq, key_tree->next);
+      step_down_to(seq, key_tree->next);
       key_tree= key_tree->next;
       seq->param->is_ror_scan= FALSE;
       break;
@@ -8211,20 +8187,6 @@ walk_right_n_up:
                                           &cur->max_key,
                                           &cur->max_key_flag,
                                           MAX_KEY);
-
-#ifdef OPTIMIZER_TRACE
-        if (unlikely(active_trace_ptr != NULL))
-        {
-          // Trace the range we just stored
-          KEY_PART_INFO *kpi=
-            seq->param->table->key_info[seq->real_keyno].key_part +
-            store_key_part->part;
-
-          append_range(active_trace_ptr, kpi,
-                       store_key_part->min_value, store_key_part->max_value,
-                       store_key_part->min_flag | store_key_part->max_flag);
-        }
-#endif
         break;
       }
     }
@@ -8242,7 +8204,7 @@ walk_up_n_right:
       /* Step up */
       key_tree= key_tree->prev;
     }
-    step_down_to(active_trace_ptr, seq, key_tree);
+    step_down_to(seq, key_tree);
   }
 
   /* Ok got a tuple */
@@ -8304,9 +8266,6 @@ walk_up_n_right:
   seq->param->range_count++;
   seq->param->max_key_part=max(seq->param->max_key_part,key_tree->part);
 
-  if (unlikely(active_trace_ptr != NULL) && active_trace_ptr->length())
-    trace_array->add_utf8(active_trace_ptr->ptr(), active_trace_ptr->length());
-
   return 0;
 }
 
@@ -9329,8 +9288,7 @@ range_seq_t quick_range_seq_init(void *i
     1  No more ranges in the sequence
 */
 
-uint quick_range_seq_next(range_seq_t rseq, KEY_MULTI_RANGE *range, 
-                          Opt_trace_array *unused)
+uint quick_range_seq_next(range_seq_t rseq, KEY_MULTI_RANGE *range)
 {
   QUICK_RANGE_SEQ_CTX *ctx= (QUICK_RANGE_SEQ_CTX*)rseq;
 
@@ -10586,6 +10544,21 @@ get_best_group_min_max(PARAM *param, SEL
                                                    cur_index_tree, TRUE,
                                                    &mrr_flags, &mrr_bufsize,
                                                    &dummy_cost);
+
+#ifdef OPTIMIZER_TRACE
+      if (cur_index_tree &&
+          param->thd->opt_trace && param->thd->opt_trace->is_started())
+      {
+        Opt_trace_array trace_range(param->thd->opt_trace, "ranges");
+
+        const KEY_PART_INFO *key_part= cur_index_info->key_part;
+
+        String range_info;
+        range_info.set_charset(system_charset_info);
+        trace_range_all_keyparts(trace_range, &range_info,
+                                 cur_index_tree, key_part);
+      }
+#endif
     }
     cost_group_min_max(table, cur_index_info, cur_used_key_parts,
                        cur_group_key_parts, tree, cur_index_tree,
@@ -12433,6 +12406,60 @@ static void append_range(String *out,
   }
 }
 
+/**
+  Traverse an R-B tree of range conditions and append all ranges for this
+  keypart and consecutive keyparts to the optimizer trace. See
+  description of R-B trees/SEL_ARG for details on how ranges are
+  linked.
+
+  @param[in,out] trace_range   Optimizer trace array ranges are appended to
+  @param[in]     range_so_far  String containing ranges for keyparts prior
+                               to this keypart.
+  @param[in]     keypart_root  The root of the R-B tree containing intervals
+                               for this keypart.
+  @param[in]     key_parts     Index components description, used when adding
+                               information to the optimizer trace
+*/
+static void trace_range_all_keyparts(Opt_trace_array &trace_range,
+                                     const String *range_so_far,
+                                     SEL_ARG *keypart_root,
+                                     const KEY_PART_INFO *key_parts)
+{
+  DBUG_ASSERT(keypart_root && keypart_root != &null_element);
+
+  // Navigate to first interval in red-black tree
+  const KEY_PART_INFO *cur_key_part= key_parts + keypart_root->part;
+  SEL_ARG *keypart_range= keypart_root->first();
+
+  while (keypart_range)
+  {
+    String range_cur_keypart= String(*range_so_far);
+
+    // Append the current range to the range String
+    append_range(&range_cur_keypart, cur_key_part,
+                 keypart_range->min_value, keypart_range->max_value,
+                 keypart_range->min_flag | keypart_range->max_flag);
+
+    if (keypart_range->next_key_part)
+    {
+      // Not done - there are ranges in consecutive keyparts as well
+      trace_range_all_keyparts(trace_range, &range_cur_keypart,
+                               keypart_range->next_key_part, key_parts);
+    }
+    else
+    {
+      /*
+        This is the last keypart with a range. Print full range
+        info to the optimizer trace
+      */
+      trace_range.add_utf8(range_cur_keypart.ptr(),
+                           range_cur_keypart.length());
+    }
+    keypart_range= keypart_range->next;
+  }
+
+}
+
 #endif //OPTIMIZER_TRACE
 
 /*****************************************************************************

=== modified file 'sql/opt_range.h'
--- a/sql/opt_range.h	2011-03-17 12:03:30 +0000
+++ b/sql/opt_range.h	2011-04-06 09:19:23 +0000
@@ -394,8 +394,7 @@ typedef struct st_quick_range_seq_ctx
 } QUICK_RANGE_SEQ_CTX;
 
 range_seq_t quick_range_seq_init(void *init_param, uint n_ranges, uint flags);
-uint quick_range_seq_next(range_seq_t rseq, KEY_MULTI_RANGE *range, 
-                          Opt_trace_array *unused);
+uint quick_range_seq_next(range_seq_t rseq, KEY_MULTI_RANGE *range);
 
 
 /*

=== modified file 'sql/sql_join_cache.cc'
--- a/sql/sql_join_cache.cc	2011-03-17 12:03:30 +0000
+++ b/sql/sql_join_cache.cc	2011-04-06 09:19:23 +0000
@@ -2169,8 +2169,7 @@ range_seq_t bka_range_seq_init(void *ini
 */    
 
 static 
-uint bka_range_seq_next(range_seq_t rseq, KEY_MULTI_RANGE *range, 
-                        Opt_trace_array *unused)
+uint bka_range_seq_next(range_seq_t rseq, KEY_MULTI_RANGE *range)
 {
   DBUG_ENTER("bka_range_seq_next");
   JOIN_CACHE_BKA *cache= (JOIN_CACHE_BKA *) rseq;
@@ -2994,8 +2993,7 @@ range_seq_t bka_unique_range_seq_init(vo
 */    
 
 static 
-uint bka_unique_range_seq_next(range_seq_t rseq, KEY_MULTI_RANGE *range,
-                               Opt_trace_array *unused)
+uint bka_unique_range_seq_next(range_seq_t rseq, KEY_MULTI_RANGE *range)
 {
   DBUG_ENTER("bka_unique_range_seq_next");
   JOIN_CACHE_BKA_UNIQUE *cache= (JOIN_CACHE_BKA_UNIQUE *) rseq;


Attachment: [text/bzr-bundle] bzr/jorgen.loland@oracle.com-20110406091923-3v4t3fnkc7xwqal6.bundle
Thread
bzr commit into mysql-trunk branch (jorgen.loland:3288) Jorgen Loland6 Apr