List:Commits« Previous MessageNext Message »
From:Jorgen Loland Date:September 17 2010 1:08pm
Subject:bzr commit into mysql-next-mr-bugfixing branch (jorgen.loland:3203)
View as plain text  
#At file:///export/home/jl208045/mysql/wl4800/mysql-next-mr-opt-backporting-wl4800-patchcleanup/ based on revid:guilhem@stripped

 3203 Jorgen Loland	2010-09-17
      WL4800 - Optimizer trace/debugger
      
      Intended for premature review only - will not be pushed.
      
      Changeset contains 
       * --trace-protocol for mtr
       * Tracing for the range optimizer

    modified:
      client/mysqltest.cc
      mysql-test/mysql-test-run.pl
      sql/opt_range.cc
      sql/opt_range.h
      sql/sql_select.cc
=== modified file 'client/mysqltest.cc'
--- a/client/mysqltest.cc	2010-08-16 07:30:40 +0000
+++ b/client/mysqltest.cc	2010-09-17 13:08:12 +0000
@@ -87,7 +87,7 @@ enum {
   OPT_PS_PROTOCOL=OPT_MAX_CLIENT_OPTION, OPT_SP_PROTOCOL,
   OPT_CURSOR_PROTOCOL, OPT_VIEW_PROTOCOL, OPT_MAX_CONNECT_RETRIES,
   OPT_MAX_CONNECTIONS, OPT_MARK_PROGRESS, OPT_LOG_DIR,
-  OPT_TAIL_LINES, OPT_RESULT_FORMAT_VERSION
+  OPT_TAIL_LINES, OPT_RESULT_FORMAT_VERSION, OPT_TRACE_PROTOCOL
 };
 
 static int record= 0, opt_sleep= -1;
@@ -107,6 +107,7 @@ static my_bool opt_mark_progress= 0;
 static my_bool ps_protocol= 0, ps_protocol_enabled= 0;
 static my_bool sp_protocol= 0, sp_protocol_enabled= 0;
 static my_bool view_protocol= 0, view_protocol_enabled= 0;
+static my_bool trace_protocol= 0, trace_protocol_enabled= 0;
 static my_bool cursor_protocol= 0, cursor_protocol_enabled= 0;
 static my_bool parsing_disabled= 0;
 static my_bool display_result_vertically= FALSE, display_result_lower= FALSE,
@@ -194,6 +195,7 @@ static ulong connection_retry_sleep= 100
 static my_regex_t ps_re;     /* the query can be run using PS protocol */
 static my_regex_t sp_re;     /* the query can be run as a SP */
 static my_regex_t view_re;   /* the query can be run as a view*/
+static my_regex_t trace_re;  /* the query can be traced with optimizer trace*/
 
 static void init_re(void);
 static int match_re(my_regex_t *, char *);
@@ -6069,6 +6071,9 @@ static struct my_option my_long_options[
   {"view-protocol", OPT_VIEW_PROTOCOL, "Use views for select.",
    &view_protocol, &view_protocol, 0,
    GET_BOOL, NO_ARG, 0, 0, 0, 0, 0, 0},
+  {"trace-protocol", OPT_TRACE_PROTOCOL, "Optimizer trace all selects.",
+   &trace_protocol, &trace_protocol, 0,
+   GET_BOOL, NO_ARG, 0, 0, 0, 0, 0, 0},
   {"connect_timeout", OPT_CONNECT_TIMEOUT,
    "Number of seconds before connection timeout.",
    &opt_connect_timeout, &opt_connect_timeout, 0, GET_UINT, REQUIRED_ARG,
@@ -7547,6 +7552,35 @@ void run_query(struct st_connection *cn,
   DBUG_VOID_RETURN;
 }
 
+void run_trace(struct st_connection *cn, struct st_command *command, int flags)
+{
+  if (trace_protocol_enabled &&
+      !command->expected_errors.count &&
+      match_re(&trace_re, command->query))
+  {
+    st_command save_command= *command;
+    DYNAMIC_STRING query_str;
+    DYNAMIC_STRING ds_warning_messages;
+
+    init_dynamic_string(&ds_warning_messages, "", 0, 2048);
+    init_dynamic_string(&query_str, 
+                        "select trace from information_schema.optimizer_trace",
+                        256, 256);
+    command->query= query_str.str;
+    command->query_len= query_str.length;
+    command->end= strend(command->query);
+
+    run_query(cn, command, flags);
+
+    init_dynamic_string(&ds_warning_messages, "", 0, 2048);
+
+    dynstr_free(&query_str);
+    dynstr_free(&ds_warning_messages);
+
+    *command= save_command;
+  }
+}
+
 /****************************************************************************/
 /*
   Functions to detect different SQL statements
@@ -7606,9 +7640,21 @@ void init_re(void)
     "^("
     "[[:space:]]*SELECT[[:space:]])";
 
+  /*
+    Filter queries that can be optimizer-traced. 
+    Todo:
+      - Also matches "select trace from i_s.optimizer_trace", but it works
+      - Does not match insert/update/delete 
+  */
+  const char *trace_re_str =
+    "^("
+    "[[:space:]]*SELECT[[:space:]]|"
+    "[[:space:]]*EXPLAIN[[:space:]]SELECT[[:space:]])";
+
   init_re_comp(&ps_re, ps_re_str);
   init_re_comp(&sp_re, sp_re_str);
   init_re_comp(&view_re, view_re_str);
+  init_re_comp(&trace_re, trace_re_str);
 }
 
 
@@ -7945,6 +7991,7 @@ int main(int argc, char **argv)
   var_set_int("$PS_PROTOCOL", ps_protocol);
   var_set_int("$SP_PROTOCOL", sp_protocol);
   var_set_int("$VIEW_PROTOCOL", view_protocol);
+  var_set_int("$TRACE_PROTOCOL", trace_protocol);
   var_set_int("$CURSOR_PROTOCOL", cursor_protocol);
 
   DBUG_PRINT("info",("result_file: '%s'",
@@ -7967,6 +8014,7 @@ int main(int argc, char **argv)
   ps_protocol_enabled= ps_protocol;
   sp_protocol_enabled= sp_protocol;
   view_protocol_enabled= view_protocol;
+  trace_protocol_enabled= trace_protocol;
   cursor_protocol_enabled= cursor_protocol;
   /* Cursor protcol implies ps protocol */
   if (cursor_protocol_enabled)
@@ -8196,6 +8244,7 @@ int main(int argc, char **argv)
 	  save_file[0]= 0;
 	}
 	run_query(cur_con, command, flags);
+	run_trace(cur_con, command, flags);
 	command_executed++;
         command->last_argument= command->end;
 

=== modified file 'mysql-test/mysql-test-run.pl'
--- a/mysql-test/mysql-test-run.pl	2010-09-03 20:49:28 +0000
+++ b/mysql-test/mysql-test-run.pl	2010-09-17 13:08:12 +0000
@@ -171,6 +171,7 @@ my $opt_ps_protocol;
 my $opt_sp_protocol;
 my $opt_cursor_protocol;
 my $opt_view_protocol;
+my $opt_trace_protocol;
 
 our $opt_debug;
 our @opt_cases;                  # The test cases names in argv
@@ -843,6 +844,7 @@ sub command_line_setup {
              'ps-protocol'              => \$opt_ps_protocol,
              'sp-protocol'              => \$opt_sp_protocol,
              'view-protocol'            => \$opt_view_protocol,
+             'trace-protocol'           => \$opt_trace_protocol,
              'cursor-protocol'          => \$opt_cursor_protocol,
              'ssl|with-openssl'         => \$opt_ssl,
              'skip-ssl'                 => \$opt_skip_ssl,
@@ -1441,6 +1443,11 @@ sub command_line_setup {
       unless @valgrind_args;
   }
 
+  if ( $opt_trace_protocol )
+  {
+    push(@opt_extra_mysqld_opt, "--optimizer_trace=enabled=on,one_line=off");
+  }
+
   if ( $opt_valgrind )
   {
     # Set valgrind_options to default unless already defined
@@ -5029,6 +5036,11 @@ sub start_mysqltest ($) {
     mtr_add_arg($args, "--view-protocol");
   }
 
+  if ( $opt_trace_protocol )
+  {
+    mtr_add_arg($args, "--trace-protocol");
+  }
+
   if ( $opt_cursor_protocol )
   {
     mtr_add_arg($args, "--cursor-protocol");
@@ -5450,6 +5462,7 @@ Options to control what engine/variation
   cursor-protocol       Use the cursor protocol between client and server
                         (implies --ps-protocol)
   view-protocol         Create a view to execute all non updating queries
+  trace-protocol        Print optimizer trace
   sp-protocol           Create a stored procedure to execute all queries
   compress              Use the compressed protocol between client and server
   ssl                   Use ssl protocol between client and server

=== modified file 'sql/opt_range.cc'
--- a/sql/opt_range.cc	2010-09-05 16:08:07 +0000
+++ b/sql/opt_range.cc	2010-09-17 13:08:12 +0000
@@ -30,7 +30,8 @@
     and builds lists of intervals (in index/partitioning space), such that 
     all possible records that match the condition are contained within the 
     intervals.
-    The entry point for the range analysis module is get_mm_tree() function.
+    The entry point for the range analysis module is get_mm_tree() (mm=min_max) 
+    function.
     
     The lists are returned in form of complicated structure of interlinked
     SEL_TREE/SEL_IMERGE/SEL_ARG objects.
@@ -1953,6 +1954,9 @@ public:
   static void operator delete(void *ptr,size_t size) { TRASH(ptr, size); }
   static void operator delete(void *ptr, MEM_ROOT *mem_root) { /* Never called */ }
   virtual ~TABLE_READ_PLAN() {}               /* Remove gcc warning */
+  virtual const char *trp_name() {DBUG_ASSERT(false); return NULL; }
+  virtual void trace_basic_info(PARAM *param, Opt_trace_object *oto) 
+    {DBUG_ASSERT(false);}
 
 };
 
@@ -1994,8 +1998,36 @@ public:
     }
     DBUG_RETURN(quick);
   }
+  const char *trp_name(){return "range";}
+  void trace_basic_info(PARAM *param, Opt_trace_object *oto) {
+    oto->add(trp_name(), "TRP type").
+      add(param->table->s->key_info[key_idx].name,"index").
+      add(records,"records");
+  }
 };
 
+typedef struct st_ror_scan_info
+{
+  uint      idx;      /* # of used key in param->keys */
+  uint      keynr;    /* # of used key in table */
+  ha_rows   records;  /* estimate of # records this scan will return */
+
+  /* Set of intervals over key fields that will be used for row retrieval. */
+  SEL_ARG   *sel_arg;
+
+  /* Fields used in the query and covered by this ROR scan. */
+  MY_BITMAP covered_fields;
+  uint      used_fields_covered; /* # of set bits in covered_fields */
+  int       key_rec_length; /* length of key record (including rowid) */
+
+  /*
+    Cost of reading all index records with values in sel_arg intervals set
+    (assuming there is no need to access full table records)
+  */
+  double    index_read_cost;
+  uint      first_uncovered_field; /* first unused bit in covered_fields */
+  uint      key_components; /* # of parts in the key */
+} ROR_SCAN_INFO;
 
 /* Plan for QUICK_ROR_INTERSECT_SELECT scan. */
 
@@ -2013,6 +2045,24 @@ public:
   struct st_ror_scan_info *cpk_scan;  /* Clustered PK scan, if there is one */
   bool is_covering; /* TRUE if no row retrieval phase is necessary */
   double index_scan_costs; /* SUM(cost(index_scan)) */
+  const char *trp_name(){return "ror_intersect";}
+  void trace_basic_info(PARAM *param, Opt_trace_object *oto) {
+    oto->add(trp_name(), "TRP type");
+    Opt_trace_array ota(param->thd->opt_trace,"ror_intersect of");
+
+    char buff[1024];
+    String tmp(buff,sizeof(buff),&my_charset_bin);
+    tmp.length(0);
+    uint records= 0;
+    for (st_ror_scan_info **current= first_scan; 
+         current != last_scan; 
+         current++)
+    {
+      Opt_trace_object (param->thd->opt_trace).
+        add(param->table->s->key_info[(*current)->idx].name, "index").
+        add((*current)->records, "records");
+    }
+  }
 };
 
 
@@ -2031,6 +2081,18 @@ public:
                              MEM_ROOT *parent_alloc);
   TABLE_READ_PLAN **first_ror; /* array of ptrs to plans for merged scans */
   TABLE_READ_PLAN **last_ror;  /* end of the above array */
+  const char *trp_name(){return "ror_union";}
+  void trace_basic_info(PARAM *param, Opt_trace_object *oto) {
+    oto->add(trp_name(), "TRP type");
+    Opt_trace_array ota(param->thd->opt_trace,"ror union of");
+    for (TABLE_READ_PLAN **current= first_ror; 
+         current != last_ror;
+         current++)
+    {
+      Opt_trace_object details(param->thd->opt_trace);
+      (*current)->trace_basic_info(param,&details);
+    }
+  }
 };
 
 
@@ -2049,6 +2111,18 @@ public:
                              MEM_ROOT *parent_alloc);
   TRP_RANGE **range_scans; /* array of ptrs to plans of merged scans */
   TRP_RANGE **range_scans_end; /* end of the array */
+  const char *trp_name(){return "index_merge";}
+  void trace_basic_info(PARAM *param, Opt_trace_object *oto) {
+    oto->add(trp_name(), "TRP type");
+    Opt_trace_array ota(param->thd->opt_trace,"index_merge of");
+    for (TRP_RANGE **current= range_scans; 
+         current != range_scans_end;
+         current++)
+    {
+      Opt_trace_object details(param->thd->opt_trace);
+      (*current)->trace_basic_info(param,&details);
+    }
+  }
 };
 
 
@@ -2076,6 +2150,10 @@ public:
   /* Number of records selected by the ranges in index_tree. */
   ha_rows quick_prefix_records;
 public:
+  const char *trp_name(){return "group_min_max";}
+  void trace_basic_info(PARAM *param, Opt_trace_object *oto) {
+    oto->add(trp_name(), "TRP type").add("more here","todo");
+  }
   TRP_GROUP_MIN_MAX(bool have_min_arg, bool have_max_arg, 
                     bool have_agg_distinct_arg,
                     KEY_PART_INFO *min_max_arg_part_arg,
@@ -2244,12 +2322,10 @@ int SQL_SELECT::test_quick_select(THD *t
   else if (read_time <= 2.0 && !force_quick_range)
     DBUG_RETURN(0);				/* No need for quick select */
 
-#ifndef NO_OPT_TRACE_FOR_RANGE_OPT
   Opt_trace_object oto(thd->opt_trace, "range_analysis");
   Opt_trace_object(thd->opt_trace, "table_scan").
     add(head->file->stats.records, "records").
     add(read_time, "cost");
-#endif
 
   keys_to_use.intersect(head->keys_in_use_for_query);
   if (!keys_to_use.is_clear_all())
@@ -2300,9 +2376,7 @@ int SQL_SELECT::test_quick_select(THD *t
     thd->mem_root= &alloc;
 
     {
-#ifndef NO_OPT_TRACE_FOR_RANGE_OPT
-      Opt_trace_object oto1(thd->opt_trace, "indices");
-#endif
+      Opt_trace_array ota(thd->opt_trace, "possible min-max indices");
       /*
         Make an array with description of all key parts of all table keys.
         This is used in get_mm_parts function.
@@ -2310,17 +2384,23 @@ int SQL_SELECT::test_quick_select(THD *t
       key_info= head->key_info;
       for (idx=0 ; idx < head->s->keys ; idx++, key_info++)
       {
-#ifndef NO_OPT_TRACE_FOR_RANGE_OPT
-        oto1.add(key_info->name, "index");
-#endif
+        Opt_trace_object oto2(thd->opt_trace);
+        oto2.add(key_info->name, "index");
         KEY_PART_INFO *key_part_info;
         if (!keys_to_use.is_set(idx))
+        {
+          oto2.add("false","usable").add("not applicable","cause");
           continue;
+        }
         if (key_info->flags & HA_FULLTEXT)
+        {
+          oto2.add("false","usable").add("fulltext","cause");
           continue;    // ToDo: ft-keys in non-ft ranges, if possible   SerG
+        }
 
         param.key[param.keys]=key_parts;
         key_part_info= key_info->key_part;
+        Opt_trace_array ota1(thd->opt_trace, "key parts");
         for (uint part=0 ; part < key_info->key_parts ;
              part++, key_parts++, key_part_info++)
         {
@@ -2334,6 +2414,7 @@ int SQL_SELECT::test_quick_select(THD *t
             (key_info->flags & HA_SPATIAL) ? Field::itMBR : Field::itRAW;
           /* Only HA_PART_KEY_SEG is used */
           key_parts->flag=         (uint8) key_part_info->key_part_flag;
+          ota1.add(key_parts->field->field_name);
         }
         param.real_keynr[param.keys++]=idx;
       }
@@ -2357,12 +2438,10 @@ int SQL_SELECT::test_quick_select(THD *t
         chosen= true;
       }
 
-#ifndef NO_OPT_TRACE_FOR_RANGE_OPT
-      Opt_trace_object(thd->opt_trace, "best_covering_scan").
+      Opt_trace_object(thd->opt_trace, "best full index scan").
         add(head->key_info[key_for_use].name, "index").
         add(key_read_time, "cost").
         add(chosen, "chosen");
-#endif
     }
 
     TABLE_READ_PLAN *best_trp= NULL;
@@ -2375,6 +2454,7 @@ int SQL_SELECT::test_quick_select(THD *t
       {
         if (tree->type == SEL_TREE::IMPOSSIBLE)
         {
+          oto.add(false,"usable").add("SEL_TREE::IMPOSSIBLE","cause");
           records=0L;                      /* Return -1 from this function. */
           read_time= (double) HA_POS_ERROR;
           goto free_mem;
@@ -2384,7 +2464,11 @@ int SQL_SELECT::test_quick_select(THD *t
           can construct a group-min-max quick select
         */
         if (tree->type != SEL_TREE::KEY && tree->type != SEL_TREE::KEY_SMALLER)
+        {
+          oto.add(false,"range possible").
+            add("non-KEY comparison and not '<' ","cause");
           tree= NULL;
+        }
       }
     }
 
@@ -2395,8 +2479,11 @@ int SQL_SELECT::test_quick_select(THD *t
     group_trp= get_best_group_min_max(&param, tree, best_read_time);
     if (group_trp)
     {
+      Opt_trace_object o(thd->opt_trace, "group min max select");
+      o.add("high priority", "more here");
       param.table->quick_condition_rows= min(group_trp->records,
                                              head->file->stats.records);
+      o.add(group_trp->read_cost,"cost");
       if (group_trp->read_cost < best_read_time)
       {
         best_trp= group_trp;
@@ -2412,14 +2499,19 @@ int SQL_SELECT::test_quick_select(THD *t
       */
       if (tree->merges.is_empty())
       {
+        Opt_trace_object o(thd->opt_trace, "analyzing min-max range");
         TRP_RANGE         *range_trp;
         TRP_ROR_INTERSECT *rori_trp;
         bool can_build_covering= FALSE;
+        int type= 0;
+        int index= 0;
 
         /* Get best 'range' plan and prepare data for making other plans */
         if ((range_trp= get_key_scans_params(&param, tree, FALSE, TRUE,
                                              best_read_time)))
         {
+          type= 1;
+          index= range_trp->key_idx;
           best_trp= range_trp;
           best_read_time= best_trp->read_cost;
         }
@@ -2439,6 +2531,8 @@ int SQL_SELECT::test_quick_select(THD *t
           if ((rori_trp= get_best_ror_intersect(&param, tree, best_read_time,
                                                 &can_build_covering)))
           {
+            o.add(true,"todo more on ror intersect");
+            type= 2;
             best_trp= rori_trp;
             best_read_time= best_trp->read_cost;
             /*
@@ -2448,9 +2542,19 @@ int SQL_SELECT::test_quick_select(THD *t
             if (!rori_trp->is_covering && can_build_covering &&
                 (rori_trp= get_best_covering_ror_intersect(&param, tree,
                                                            best_read_time)))
+            {
+              type= 3;
               best_trp= rori_trp;
+            }
           }
         }
+        
+        o.add((longlong)type, "chosen plan").add(true,"need something better");
+
+        if (type == 0)
+          o.add(true, "more to do here");
+        if (type == 1)
+          o.add (param.table->key_info[index].name, "best index for range");
       }
       else
       {
@@ -2463,18 +2567,26 @@ int SQL_SELECT::test_quick_select(THD *t
           DBUG_PRINT("info",("No range reads possible,"
                              " trying to construct index_merge"));
           List_iterator_fast<SEL_IMERGE> it(tree->merges);
-          while ((imerge= it++))
           {
-            new_conj_trp= get_best_disjunct_quick(&param, imerge, best_read_time);
-            if (new_conj_trp)
-              set_if_smaller(param.table->quick_condition_rows, 
-                             new_conj_trp->records);
-            if (!best_conj_trp || (new_conj_trp && new_conj_trp->read_cost <
-                                   best_conj_trp->read_cost))
-              best_conj_trp= new_conj_trp;
+            Opt_trace_array ota(thd->opt_trace, "analyzing index-merge");
+            while ((imerge= it++))
+            {
+              new_conj_trp= get_best_disjunct_quick(&param, imerge, best_read_time);
+              if (new_conj_trp)
+                set_if_smaller(param.table->quick_condition_rows, 
+                               new_conj_trp->records);
+              if (!best_conj_trp || (new_conj_trp && new_conj_trp->read_cost <
+                                     best_conj_trp->read_cost))
+              {
+                
+                best_conj_trp= new_conj_trp;
+              }
+            }
+            if (best_conj_trp)
+            {
+              best_trp= best_conj_trp;
+            }
           }
-          if (best_conj_trp)
-            best_trp= best_conj_trp;
         }
       }
     }
@@ -2490,19 +2602,23 @@ int SQL_SELECT::test_quick_select(THD *t
         delete quick;
         quick= NULL;
       }
+      Opt_trace_object oto2(thd->opt_trace, "quick select");
+
+      best_trp->trace_basic_info(&param,&oto2);
+      
+      oto2.add(quick->records,"total records");
+      oto2.add(quick->read_time,"total cost");
+      oto2.add("maybe","trace quick->mrr_flags?");
     }
+    
+    oto.add(param.table->quick_condition_rows, "quick_condition_rows (SP)");
 
-#ifndef NO_OPT_TRACE_FOR_RANGE_OPT
-    oto.add(param.table->quick_condition_rows, "quick_condition_rows");
-#endif
-  free_mem:
+free_mem:
     free_root(&alloc,MYF(0));			// Return memory & allocator
     thd->mem_root= param.old_root;
     thd->no_errors=0;
   }
-#ifndef NO_OPT_TRACE_FOR_RANGE_OPT
-  oto.add(records, "records");
-#endif
+  oto.add(records, "records (SP)"); 
   OPT_TRACE2(thd->opt_trace, print_quick(quick, &needed_reg));
 
   /*
@@ -3835,48 +3951,54 @@ TABLE_READ_PLAN *get_best_disjunct_quick
   DBUG_ENTER("get_best_disjunct_quick");
   DBUG_PRINT("info", ("Full table scan cost: %g", read_time));
 
+  Opt_trace_object o(param->thd->opt_trace);
   if (!(range_scans= (TRP_RANGE**)alloc_root(param->mem_root,
                                              sizeof(TRP_RANGE*)*
                                              n_child_scans)))
     DBUG_RETURN(NULL);
-  /*
-    Collect best 'range' scan for each of disjuncts, and, while doing so,
-    analyze possibility of ROR scans. Also calculate some values needed by
-    other parts of the code.
-  */
-  for (ptree= imerge->trees, cur_child= range_scans;
-       ptree != imerge->trees_next;
-       ptree++, cur_child++)
-  {
-    DBUG_EXECUTE("info", print_sel_tree(param, *ptree, &(*ptree)->keys_map,
-                                        "tree in SEL_IMERGE"););
-    if (!(*cur_child= get_key_scans_params(param, *ptree, TRUE, FALSE, read_time)))
-    {
-      /*
-        One of index scans in this index_merge is more expensive than entire
-        table read for another available option. The entire index_merge (and
-        any possible ROR-union) will be more expensive then, too. We continue
-        here only to update SQL_SELECT members.
-      */
-      imerge_too_expensive= TRUE;
-    }
-    if (imerge_too_expensive)
-      continue;
+  {
+    Opt_trace_array a(param->thd->opt_trace,"indices to merge");
+    /*
+      Collect best 'range' scan for each of disjuncts, and, while doing so,
+      analyze possibility of ROR scans. Also calculate some values needed by
+      other parts of the code.
+    */
+    for (ptree= imerge->trees, cur_child= range_scans;
+         ptree != imerge->trees_next;
+         ptree++, cur_child++)
+    {
+      DBUG_EXECUTE("info", print_sel_tree(param, *ptree, &(*ptree)->keys_map,
+                                          "tree in SEL_IMERGE"););
+      Opt_trace_object o2(param->thd->opt_trace);
+      if (!(*cur_child= get_key_scans_params(param, *ptree, TRUE, FALSE, read_time)))
+      {
+        /*
+          One of index scans in this index_merge is more expensive than entire
+          table read for another available option. The entire index_merge (and
+          any possible ROR-union) will be more expensive then, too. We continue
+          here only to update SQL_SELECT members.
+        */
+        o2.add("yay","a hit").add(true,"expensive").add(false,"chosen");
+        imerge_too_expensive= TRUE;
+      }
+      if (imerge_too_expensive)
+        continue;
 
-    imerge_cost += (*cur_child)->read_cost;
-    all_scans_ror_able &= ((*ptree)->n_ror_scans > 0);
-    all_scans_rors &= (*cur_child)->is_ror;
-    if (pk_is_clustered &&
-        param->real_keynr[(*cur_child)->key_idx] ==
-        param->table->s->primary_key)
-    {
-      cpk_scan= cur_child;
-      cpk_scan_records= (*cur_child)->records;
+      imerge_cost += (*cur_child)->read_cost;
+      all_scans_ror_able &= ((*ptree)->n_ror_scans > 0);
+      all_scans_rors &= (*cur_child)->is_ror;
+      if (pk_is_clustered &&
+          param->real_keynr[(*cur_child)->key_idx] ==
+          param->table->s->primary_key)
+      {
+        cpk_scan= cur_child;
+        cpk_scan_records= (*cur_child)->records;
+      }
+      else
+        non_cpk_scan_records += (*cur_child)->records;
     }
-    else
-      non_cpk_scan_records += (*cur_child)->records;
   }
-
+  o.add(imerge_cost,"scan cost");
   DBUG_PRINT("info", ("index_merge scans cost %g", imerge_cost));
   if (imerge_too_expensive || (imerge_cost > read_time) ||
       ((non_cpk_scan_records+cpk_scan_records >= param->table->file->stats.records) &&
@@ -3888,6 +4010,7 @@ TABLE_READ_PLAN *get_best_disjunct_quick
     */
     DBUG_PRINT("info", ("Sum of index_merge scans is more expensive than "
                         "full table scan, bailing out"));
+    o.add(false,"chosen").add("more expensive than table scan","cause");
     DBUG_RETURN(NULL);
   }
 
@@ -3900,6 +4023,8 @@ TABLE_READ_PLAN *get_best_disjunct_quick
       param->thd->optimizer_switch_flag(OPTIMIZER_SWITCH_INDEX_MERGE_UNION))
   {
     roru_read_plans= (TABLE_READ_PLAN**)range_scans;
+    o.add(true,"Use ROR-union").
+      add("ROR always cheaper than non-ROR","cause");
     goto skip_to_ror_scan;
   }
 
@@ -3910,6 +4035,7 @@ TABLE_READ_PLAN *get_best_disjunct_quick
       is done in QUICK_RANGE_SELECT::row_in_ranges)
      */
     imerge_cost += non_cpk_scan_records / TIME_FOR_COMPARE_ROWID;
+    o.add(imerge_cost,"clustered scan new cost");
   }
 
   /* Calculate cost(rowid_to_row_scan) */
@@ -3921,11 +4047,13 @@ TABLE_READ_PLAN *get_best_disjunct_quick
                         &sweep_cost);
     imerge_cost += sweep_cost.total_cost();
   }
+  o.add(imerge_cost,"cost with sweep");
   DBUG_PRINT("info",("index_merge cost with rowid-to-row scan: %g",
                      imerge_cost));
   if (imerge_cost > read_time || 
       !param->thd->optimizer_switch_flag(OPTIMIZER_SWITCH_INDEX_MERGE_SORT_UNION))
   {
+    o.add(true,"going for ror index merge").add("yay","more here");
     goto build_ror_index_merge;
   }
 
@@ -3946,6 +4074,7 @@ TABLE_READ_PLAN *get_best_disjunct_quick
     Unique::get_use_cost(param->imerge_cost_buff, (uint)non_cpk_scan_records,
                          param->table->file->ref_length,
                          param->thd->variables.sortbuff_size);
+  o.add(imerge_cost,"total cost");
   DBUG_PRINT("info",("index_merge total cost: %g (wanted: less then %g)",
                      imerge_cost, read_time));
   if (imerge_cost < read_time)
@@ -3981,44 +4110,50 @@ skip_to_ror_scan:
   cur_roru_plan= roru_read_plans;
 
   /* Find 'best' ROR scan for each of trees in disjunction */
-  for (ptree= imerge->trees, cur_child= range_scans;
-       ptree != imerge->trees_next;
-       ptree++, cur_child++, cur_roru_plan++)
   {
-    /*
-      Assume the best ROR scan is the one that has cheapest full-row-retrieval
-      scan cost.
-      Also accumulate index_only scan costs as we'll need them to calculate
-      overall index_intersection cost.
-    */
-    double cost;
-    if ((*cur_child)->is_ror)
+    Opt_trace_array ota_x(param->thd->opt_trace, "analyzing ROR scans");
+    ota_x.add("this is where to continue with ror");
+    for (ptree= imerge->trees, cur_child= range_scans;
+         ptree != imerge->trees_next;
+         ptree++, cur_child++, cur_roru_plan++)
     {
-      /* Ok, we have index_only cost, now get full rows scan cost */
-      cost= param->table->file->
-              read_time(param->real_keynr[(*cur_child)->key_idx], 1,
-                        (*cur_child)->records) +
-              rows2double((*cur_child)->records) / TIME_FOR_COMPARE;
-    }
-    else
-      cost= read_time;
+      Opt_trace_object inner_trace(param->thd->opt_trace);
+      (*cur_child)->trace_basic_info(param, &inner_trace);
+      /*
+        Assume the best ROR scan is the one that has cheapest full-row-retrieval
+        scan cost.
+        Also accumulate index_only scan costs as we'll need them to calculate
+        overall index_intersection cost.
+      */
+      double cost;
+      if ((*cur_child)->is_ror)
+      {
+        /* Ok, we have index_only cost, now get full rows scan cost */
+        cost= param->table->file->
+          read_time(param->real_keynr[(*cur_child)->key_idx], 1,
+                    (*cur_child)->records) +
+          rows2double((*cur_child)->records) / TIME_FOR_COMPARE;
+      }
+      else
+        cost= read_time;
 
-    TABLE_READ_PLAN *prev_plan= *cur_child;
-    if (!(*cur_roru_plan= get_best_ror_intersect(param, *ptree, cost,
-                                                 &dummy)))
-    {
-      if (prev_plan->is_ror)
-        *cur_roru_plan= prev_plan;
+      TABLE_READ_PLAN *prev_plan= *cur_child;
+      if (!(*cur_roru_plan= get_best_ror_intersect(param, *ptree, cost,
+                                                   &dummy)))
+      {
+        if (prev_plan->is_ror)
+          *cur_roru_plan= prev_plan;
+        else
+          DBUG_RETURN(imerge_trp);
+        roru_index_costs += (*cur_roru_plan)->read_cost;
+      }
       else
-        DBUG_RETURN(imerge_trp);
-      roru_index_costs += (*cur_roru_plan)->read_cost;
+        roru_index_costs +=
+          ((TRP_ROR_INTERSECT*)(*cur_roru_plan))->index_scan_costs;
+      roru_total_records += (*cur_roru_plan)->records;
+      roru_intersect_part *= (*cur_roru_plan)->records /
+        param->table->file->stats.records;
     }
-    else
-      roru_index_costs +=
-        ((TRP_ROR_INTERSECT*)(*cur_roru_plan))->index_scan_costs;
-    roru_total_records += (*cur_roru_plan)->records;
-    roru_intersect_part *= (*cur_roru_plan)->records /
-                           param->table->file->stats.records;
   }
 
   /*
@@ -4050,6 +4185,8 @@ skip_to_ror_scan:
                      sweep_cost.total_cost();
   }
 
+  o.add(roru_total_cost, "ROR-union cost").
+    add((ulonglong)n_child_scans, "members");
   DBUG_PRINT("info", ("ROR-union: cost %g, %d members", roru_total_cost,
                       n_child_scans));
   TRP_ROR_UNION* roru;
@@ -4068,30 +4205,6 @@ skip_to_ror_scan:
 }
 
 
-typedef struct st_ror_scan_info
-{
-  uint      idx;      /* # of used key in param->keys */
-  uint      keynr;    /* # of used key in table */
-  ha_rows   records;  /* estimate of # records this scan will return */
-
-  /* Set of intervals over key fields that will be used for row retrieval. */
-  SEL_ARG   *sel_arg;
-
-  /* Fields used in the query and covered by this ROR scan. */
-  MY_BITMAP covered_fields;
-  uint      used_fields_covered; /* # of set bits in covered_fields */
-  int       key_rec_length; /* length of key record (including rowid) */
-
-  /*
-    Cost of reading all index records with values in sel_arg intervals set
-    (assuming there is no need to access full table records)
-  */
-  double    index_read_cost;
-  uint      first_uncovered_field; /* first unused bit in covered_fields */
-  uint      key_components; /* # of parts in the key */
-} ROR_SCAN_INFO;
-
-
 /*
   Create ROR_SCAN_INFO* structure with a single ROR scan on index idx using
   sel_arg set of intervals.
@@ -4612,9 +4725,19 @@ TRP_ROR_INTERSECT *get_best_ror_intersec
   double min_cost= DBL_MAX;
   DBUG_ENTER("get_best_ror_intersect");
 
+  Opt_trace_object oto1(param->thd->opt_trace, "analyzing ror intersect");
+
   if ((tree->n_ror_scans < 2) || !param->table->file->stats.records ||
       !param->thd->optimizer_switch_flag(OPTIMIZER_SWITCH_INDEX_MERGE_INTERSECT))
+  {
+    if (tree->n_ror_scans >= 2)
+      oto1.add(false,"usable").add("not sure","cause");
+    else
+      oto1.add(false,"usable").add("too few ror scans","cause");
     DBUG_RETURN(NULL);
+  }
+
+  oto1.add(true,"need more on ror here");
 
   /*
     Step1: Collect ROR-able SEL_ARGs and create ROR_SCAN_INFO for each of 
@@ -4737,6 +4860,7 @@ TRP_ROR_INTERSECT *get_best_ror_intersec
   {
     if (!(trp= new (param->mem_root) TRP_ROR_INTERSECT))
       DBUG_RETURN(trp);
+    Opt_trace_object(param->thd->opt_trace, "set first_scan 1");
     if (!(trp->first_scan=
            (ROR_SCAN_INFO**)alloc_root(param->mem_root,
                                        sizeof(ROR_SCAN_INFO*)*best_num)))
@@ -4897,6 +5021,7 @@ TRP_ROR_INTERSECT *get_best_covering_ror
   if (!(trp= new (param->mem_root) TRP_ROR_INTERSECT))
     DBUG_RETURN(trp);
   uint best_num= (ror_scan_mark - tree->ror_scans);
+  Opt_trace_object(param->thd->opt_trace, "set first_scan 2");
   if (!(trp->first_scan= (ROR_SCAN_INFO**)alloc_root(param->mem_root,
                                                      sizeof(ROR_SCAN_INFO*)*
                                                      best_num)))
@@ -4962,9 +5087,8 @@ static TRP_RANGE *get_key_scans_params(P
   */
   DBUG_EXECUTE("info", print_sel_tree(param, tree, &tree->keys_map,
                                       "tree scans"););
-#ifndef NO_OPT_TRACE_FOR_RANGE_OPT
-  Opt_trace_object oto1(param->thd->opt_trace, "index_scans_parameters");
-#endif
+  Opt_trace_array oto1(param->thd->opt_trace, "analyzing range scan");
+  oto1.add("need >1 key test");
 
   tree->ror_scans_map.clear_all();
   tree->n_ror_scans= 0;
@@ -4984,42 +5108,34 @@ static TRP_RANGE *get_key_scans_params(P
       bool read_index_only= index_read_must_be_used ? TRUE :
                             (bool) param->table->covering_keys.is_set(keynr);
 
-#ifndef NO_OPT_TRACE_FOR_RANGE_OPT
-      Opt_trace_object oto2(param->thd->opt_trace, "range_scan");
+      Opt_trace_object oto2(param->thd->opt_trace);
       oto2.add(param->table->key_info[keynr].name, "index");
-#endif
       found_records= check_quick_select(param, idx, read_index_only, *key,
                                         update_tbl_stats, &mrr_flags,
                                         &buf_size, &cost);
-#ifndef NO_OPT_TRACE_FOR_RANGE_OPT
       oto2.add(read_index_only, "index_only").add(found_records, "records");
-#endif
 
       if ((found_records != HA_POS_ERROR) && param->is_ror_scan)
       {
-#ifndef NO_OPT_TRACE_FOR_RANGE_OPT
         oto2.add(true, "rowid_ordered");
-#endif
         tree->n_ror_scans++;
         tree->ror_scans_map.set_bit(idx);
       }
-#ifndef NO_OPT_TRACE_FOR_RANGE_OPT
       else
         oto2.add(false, "rowid_ordered");
-#endif
 
       if (found_records != HA_POS_ERROR &&
           read_time > (found_read_time= cost.total_cost()))
       {
-#ifndef NO_OPT_TRACE_FOR_RANGE_OPT
         oto2.add(found_read_time, "cost").add(true, "chosen");
-#endif
         read_time=    found_read_time;
         best_records= found_records;
         key_to_read=  key;
         best_mrr_flags= mrr_flags;
         best_buf_size=  buf_size;
       }
+      else
+        oto2.add(false, "chosen");
     }
   }
 
@@ -5035,14 +5151,15 @@ static TRP_RANGE *get_key_scans_params(P
       read_plan->is_ror= tree->ror_scans_map.is_set(idx);
       read_plan->read_cost= read_time;
       read_plan->mrr_buf_size= best_buf_size;
-#ifndef NO_OPT_TRACE_FOR_RANGE_OPT
-      oto1.add(param->table->key_info[param->real_keynr[idx]].name,
-                "index_for_best_range_access");
-#endif
       //DBUG_PRINT("info",
       //           ("Returning range plan for key %s, cost %g, records %lu",
       //            param->table->key_info[param->real_keynr[idx]].name,
       //            read_plan->read_cost, (ulong) read_plan->records));
+      Opt_trace_object oto2(param->thd->opt_trace);
+      oto2.add(param->table->key_info[param->real_keynr[idx]].name,
+               "index_for_best_range_access").
+        add("need test for no key to read","todo").
+        add("remove this","todo2");
     }
   }
   else
@@ -5098,10 +5215,12 @@ QUICK_SELECT_I *TRP_ROR_INTERSECT::make_
                                              "creating ROR-intersect",
                                              first_scan, last_scan););
     alloc= parent_alloc? parent_alloc: &quick_intrsect->alloc;
-    for (; first_scan != last_scan;++first_scan)
+    for (st_ror_scan_info **current= first_scan; 
+         current != last_scan; 
+         current++)
     {
-      if (!(quick= get_quick_select(param, (*first_scan)->idx,
-                                    (*first_scan)->sel_arg,
+      if (!(quick= get_quick_select(param, (*current)->idx,
+                                    (*current)->sel_arg,
                                     HA_MRR_USE_DEFAULT_IMPL | HA_MRR_SORTED,
                                     0, alloc)) ||
           quick_intrsect->push_quick_back(quick))
@@ -9683,18 +9802,32 @@ get_best_group_min_max(PARAM *param, SEL
 
   DBUG_ENTER("get_best_group_min_max");
 
+  Opt_trace_object oto(thd->opt_trace, "group min max select");
+
   /* Perform few 'cheap' tests whether this access method is applicable. */
   if (!join)
+  {
+    oto.add("false","chosen").add("no join","cause");
     DBUG_RETURN(NULL);        /* This is not a select statement. */
+  }
   if ((join->tables != 1) ||  /* The query must reference one table. */
       (join->select_lex->olap == ROLLUP_TYPE)) /* Check (B3) for ROLLUP */
+  {
+    oto.add("false","chosen").add("rollup or >1 table","cause");
     DBUG_RETURN(NULL);
+  }
   if (table->s->keys == 0)        /* There are no indexes to use. */
+  {
+    oto.add("false","chosen").add("no indexes to use","cause");
     DBUG_RETURN(NULL);
+  }
 
   /* Check (SA1,SA4) and store the only MIN/MAX argument - the C attribute.*/
   if (join->make_sum_func_list(join->all_fields, join->fields_list, 1))
+  {
+    oto.add("false","chosen").add("don't know yet","cause");
     DBUG_RETURN(NULL);
+  }
 
   List_iterator<Item> select_items_it(join->fields_list);
   is_agg_distinct = is_indexed_agg_distinct(join, &agg_distinct_flds);
@@ -9702,13 +9835,18 @@ get_best_group_min_max(PARAM *param, SEL
   if ((!join->group_list) && /* Neither GROUP BY nor a DISTINCT query. */
       (!join->select_distinct) &&
       !is_agg_distinct)
+  {
+    oto.add("false","chosen").add("query not GROUP BY or DISTINCT","cause");
     DBUG_RETURN(NULL);
+  }
+
   /* Analyze the query in more detail. */
 
   if (join->sum_funcs[0])
   {
     Item_sum *min_max_item;
     Item_sum **func_ptr= join->sum_funcs;
+
     while ((min_max_item= *(func_ptr++)))
     {
       if (min_max_item->sum_func() == Item_sum::MIN_FUNC)
@@ -9720,7 +9858,10 @@ get_best_group_min_max(PARAM *param, SEL
                min_max_item->sum_func() == Item_sum::AVG_DISTINCT_FUNC)
         continue;
       else
+      {
+        oto.add("false","chosen").add("not MIN/MAX or COUNT/SUM/AVG DISTINCT","cause");
         DBUG_RETURN(NULL);
+      }
 
       /* The argument of MIN/MAX. */
       Item *expr= min_max_item->get_arg(0)->real_item();
@@ -9738,6 +9879,7 @@ get_best_group_min_max(PARAM *param, SEL
   /* Check (SA5). */
   if (join->select_distinct)
   {
+    oto.add("true","select distinct").add("true","todo");
     while ((item= select_items_it++))
     {
       if (item->real_item()->type() != Item::FIELD_ITEM)
@@ -9749,7 +9891,10 @@ get_best_group_min_max(PARAM *param, SEL
   for (tmp_group= join->group_list; tmp_group; tmp_group= tmp_group->next)
   {
     if ((*tmp_group->item)->real_item()->type() != Item::FIELD_ITEM)
+    {
+      oto.add("false","chosen").add("group field is expression","cause");
       DBUG_RETURN(NULL);
+    }
   }
 
   /*
@@ -9771,9 +9916,12 @@ get_best_group_min_max(PARAM *param, SEL
   ha_rows cur_quick_prefix_records= 0;
   uint cur_param_idx=MAX_KEY;
 
+  Opt_trace_array ota(thd->opt_trace, "looking for covering keys");
   for (uint cur_index= 0 ; cur_index_info != cur_index_info_end ;
        cur_index_info++, cur_index++)
   {
+    Opt_trace_object o(thd->opt_trace);
+    o.add(table->key_info[cur_index].name, "index");
     KEY_PART_INFO *cur_part;
     KEY_PART_INFO *end_part; /* Last part for loops. */
     /* Last index part. */
@@ -9793,7 +9941,10 @@ get_best_group_min_max(PARAM *param, SEL
     
     /* Check (B1) - if current index is covering. */
     if (!table->covering_keys.is_set(cur_index))
+    {
+      o.add("false","usable").add("not covering key", "cause");
       goto next_index;
+    }
 
     /*
       If the current storage manager is such that it appends the primary key to
@@ -9817,9 +9968,13 @@ get_best_group_min_max(PARAM *param, SEL
         */
         if (bitmap_is_set(table->read_set, cur_field->field_index) &&
             !cur_field->part_of_key_not_clustered.is_set(cur_index))
+        {
+          o.add("false","usable").add("not covering", "cause");
           goto next_index;                  // Field was not part of key
+        }
       }
     }
+    o.add("true", "covering");
 
     /*
       Check (GA1) for GROUP BY queries.
@@ -9848,9 +10003,13 @@ get_best_group_min_max(PARAM *param, SEL
           used_key_parts_map.set_bit(max_key_part);
         }
         else
+        {
+          o.add("false","usable").add("don't know", "cause");
           goto next_index;
+        }
       }
     }
+
     /*
       Check (GA2) if this is a DISTINCT query.
       If GA2, then Store a new ORDER object in group_fields_array at the
@@ -9914,10 +10073,14 @@ get_best_group_min_max(PARAM *param, SEL
     {
       key_part_nr= get_field_keypart(cur_index_info, min_max_arg_item->field);
       if (key_part_nr <= cur_group_key_parts)
+      {
+        o.add("false","usable").add("todo SA2", "cause");
         goto next_index;
+      }
       min_max_arg_part= cur_index_info->key_part + key_part_nr - 1;
     }
 
+    o.add("need more here","CONTINUE HERE");
     /*
       Check (NGA1, NGA2) and extract a sequence of constants to be used as part
       of all search keys.

=== modified file 'sql/opt_range.h'
--- a/sql/opt_range.h	2010-08-05 13:51:44 +0000
+++ b/sql/opt_range.h	2010-09-17 13:08:12 +0000
@@ -33,6 +33,7 @@
 */
 #include "sql_class.h"                          // set_var.h: THD
 #include "set_var.h"                            /* Item */
+#include "opt_trace.h"
 
 class JOIN;
 class Item_sum;
@@ -302,6 +303,8 @@ public:
   /* Get type of this quick select - one of the QS_TYPE_* values */
   virtual int get_type() = 0;
 
+  virtual const char* get_type_name() { DBUG_ASSERT(false); }
+
   /*
     Initialize this quick select as a merged scan inside a ROR-union or a ROR-
     intersection scan. The caller must not additionally call init() if this
@@ -470,6 +473,7 @@ public:
   void save_last_pos()
   { file->position(record); }
   int get_type() { return QS_TYPE_RANGE; }
+  const char* get_type_name(){ return "range"; }
   void add_keys_and_lengths(String *key_names, String *used_lengths);
   void add_info_string(String *str);
 #if !defined(DBUG_OFF) || defined(OPTIMIZER_TRACE)
@@ -567,6 +571,7 @@ public:
   bool reverse_sorted() { return false; }
   bool unique_key_range() { return false; }
   int get_type() { return QS_TYPE_INDEX_MERGE; }
+  const char* get_type_name(){ return "index_merge"; }
   void add_keys_and_lengths(String *key_names, String *used_lengths);
   void add_info_string(String *str);
   bool is_keys_used(const MY_BITMAP *fields);
@@ -627,6 +632,7 @@ public:
   bool reverse_sorted() { return false; }
   bool unique_key_range() { return false; }
   int get_type() { return QS_TYPE_ROR_INTERSECT; }
+  const char* get_type_name(){ return "ror_intersect"; }
   void add_keys_and_lengths(String *key_names, String *used_lengths);
   void add_info_string(String *str);
   bool is_keys_used(const MY_BITMAP *fields);
@@ -682,6 +688,7 @@ public:
   bool reverse_sorted() { return false; }
   bool unique_key_range() { return false; }
   int get_type() { return QS_TYPE_ROR_UNION; }
+  const char* get_type_name(){ return "ror_union"; }
   void add_keys_and_lengths(String *key_names, String *used_lengths);
   void add_info_string(String *str);
   bool is_keys_used(const MY_BITMAP *fields);
@@ -808,6 +815,7 @@ public:
   bool reverse_sorted() { return false; }
   bool unique_key_range() { return false; }
   int get_type() { return QS_TYPE_GROUP_MIN_MAX; }
+  const char* get_type_name(){ return "group_min_max"; }
   void add_keys_and_lengths(String *key_names, String *used_lengths);
 #if !defined(DBUG_OFF) || defined(OPTIMIZER_TRACE)
   void dbug_dump(int indent, bool verbose);
@@ -829,6 +837,7 @@ public:
   int get_next();
   bool reverse_sorted() { return 1; }
   int get_type() { return QS_TYPE_RANGE_DESC; }
+  const char* get_type_name(){ return "range_desc"; }
   QUICK_SELECT_I *make_reverse(uint used_key_parts_arg)
   {
     return this; // is already reverse sorted
@@ -863,6 +872,7 @@ class SQL_SELECT :public Sql_alloc {
   bool check_quick(THD *thd, bool force_quick_range, ha_rows limit)
   {
     key_map tmp(key_map::ALL_BITS);
+    Opt_trace_object oto(thd->opt_trace);
     return test_quick_select(thd, tmp, 0, limit, force_quick_range, FALSE) < 0;
   }
   inline bool skip_record(THD *thd, bool *skip_record)
@@ -886,6 +896,7 @@ public:
   int reset() { return 0; }
   int get_next() { return file->ft_read(record); }
   int get_type() { return QS_TYPE_FULLTEXT; }
+  const char* get_type_name(){ return "fulltext"; }
 };
 
 FT_SELECT *get_ft_select(THD *thd, TABLE *table, uint key);

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2010-09-05 16:08:07 +0000
+++ b/sql/sql_select.cc	2010-09-17 13:08:12 +0000
@@ -259,6 +259,10 @@ void select_describe(JOIN *join, bool ne
 			    bool distinct, const char *message=NullS);
 static Item *remove_additional_cond(Item* conds);
 static void add_group_and_distinct_keys(JOIN *join, JOIN_TAB *join_tab);
+static void trace_indices_added_group_distinct(THD *thd, 
+                                               JOIN_TAB *join_tab,
+                                               const key_map new_keys, 
+                                               char* cause);
 static bool replace_subcondition(JOIN *join, Item **tree, 
                                  Item *old_cond, Item *new_cond,
                                  bool do_fix_fields);
@@ -4932,14 +4936,27 @@ make_join_statistics(JOIN *join, TABLE_L
 
       for (s= stat ; s < stat_end ; s++)
       {
+        Opt_trace_object oto1(join->thd->opt_trace);
+        oto1.add(s->table->alias, "table");
         if (s->type == JT_SYSTEM || s->type == JT_CONST)
         {
+          oto1.add("1","found_records").
+            add("1","records").
+            add("1","cost").
+            add("1.0","worst_seeks");
+
+          if (s->ref.key_parts)
+            oto1.add((s->table->key_info+ s->ref.key)->name,"index");
+
+          if (s->type == JT_SYSTEM)
+            oto1.add("SYSTEM table","cause");
+          else
+            oto1.add("CONST table","cause");
+
           /* Only one matching row */
           s->found_records= s->records= s->read_time=1; s->worst_seeks= 1.0;
           continue;
         }
-        Opt_trace_object oto1(join->thd->opt_trace);
-        oto1.add(s->table->alias, "table");
         /* Approximate found rows and time to read them */
         s->found_records= s->records= s->table->file->stats.records;
         s->read_time= (ha_rows) s->table->file->scan_time();
@@ -4978,10 +4995,6 @@ make_join_statistics(JOIN *join, TABLE_L
                               1, &error);
           if (!select)
             goto error;
-#ifdef NO_OPT_TRACE_FOR_RANGE_OPT
-          Opt_trace_object(join->thd->opt_trace, "more_range_optimizer_trace").
-            add("yes!", "TODO?");
-#endif
           records= get_quick_record_count(join->thd, select, s->table,
                                           &s->const_keys, join->row_limit);
           s->quick= select->quick;
@@ -5002,9 +5015,18 @@ make_join_statistics(JOIN *join, TABLE_L
             {
               /* Generate empty row */
               s->info= "Impossible ON condition";
+              oto1.add("returning empty NULL row","info").
+                add("Impossible ON condition","cause").
+                add("YAY","found test hit1");
               found_const_table_map|= s->table->map;
               s->type= JT_CONST;
               mark_as_null_row(s->table);		// All fields are NULL
+            } 
+            else 
+            {
+              oto1.add("0","records").
+                add("Impossible WHERE condition","cause").
+                add("YAY","found test hit2");
             }
           }
           if (records != HA_POS_ERROR)
@@ -6414,11 +6436,14 @@ add_group_and_distinct_keys(JOIN *join, 
   Item_field *cur_item;
   key_map possible_keys;
 
+  char* cause;
+
   if (join->group_list)
   { /* Collect all query fields referenced in the GROUP clause. */
     for (cur_group= join->group_list; cur_group; cur_group= cur_group->next)
       (*cur_group->item)->walk(&Item::collect_item_field_processor, 0,
                                (uchar*) &indexed_fields);
+    cause= (char*)"group by\0";
   }
   else if (join->select_distinct)
   { /* Collect all query fields referenced in the SELECT clause. */
@@ -6428,10 +6453,12 @@ add_group_and_distinct_keys(JOIN *join, 
     while ((item= select_items_it++))
       item->walk(&Item::collect_item_field_processor, 0,
                  (uchar*) &indexed_fields);
+    cause= (char*)"distinct\0";
   }
   else if (is_indexed_agg_distinct(join, &indexed_fields))
   {
     join->sort_and_group= 1;
+    cause= (char*) "indexed distinct aggregate";
   }
   else
     return;
@@ -6448,7 +6475,30 @@ add_group_and_distinct_keys(JOIN *join, 
   }
 
   if (!possible_keys.is_clear_all())
+  {
+    trace_indices_added_group_distinct(join->thd, join_tab,
+                                       possible_keys, cause);
     join_tab->const_keys.merge(possible_keys);
+  }
+}
+
+static void trace_indices_added_group_distinct(THD *thd, 
+                                               JOIN_TAB *join_tab,
+                                               const key_map new_keys, 
+                                               char* cause)
+{
+  KEY *key_info= join_tab->table->key_info;
+  key_map existing_keys= join_tab->const_keys;
+  uint nokeys= join_tab->table->s->keys;
+  
+  Opt_trace_object oto1(thd->opt_trace, "const_keys added");
+  {
+    Opt_trace_array ota1(thd->opt_trace,"keys");
+    for (uint j=0 ; j < nokeys ; j++)
+      if (new_keys.is_set(j) && !existing_keys.is_set(j))
+        ota1.add(key_info[j].name);
+  }
+  oto1.add(cause,"cause");
 }
 
 
@@ -7247,7 +7297,7 @@ best_access_path(JOIN      *join,
   }
 
   Opt_trace_object oto1(thd->opt_trace);
-  oto1.add("table scan", "access_type");
+  oto1.add("scan", "access_type");
   /*
     Don't test table scan if it can't be better.
     Prefer key lookup if we would use the same key for scanning.
@@ -7331,6 +7381,7 @@ best_access_path(JOIN      *join,
 
     if (s->quick)
     {
+      oto1.add(s->quick->get_type_name(),"using quick");
       /*
         For each record we:
         - read record range through 'quick'
@@ -7367,6 +7418,7 @@ best_access_path(JOIN      *join,
       }
       else
       {
+        oto1.add("yay","add something about join buffer");
         /*
           We read the table as many times as join buffer becomes full.
           It would be more exact to round the result of the division with
@@ -7429,7 +7481,10 @@ skip_table_scan:
       idx == join->const_tables &&
       s->table == join->sort_by_table &&
       join->unit->select_limit_cnt >= records)
+  {
+    oto1.add(true, "use temp table");
     join->sort_by_table= (TABLE*) 1;  // Must use temporary table
+  }
 
   DBUG_VOID_RETURN;
 }
@@ -9740,6 +9795,8 @@ static bool make_join_select(JOIN *join,
 	    if (sel->cond && !sel->cond->fixed)
 	      sel->cond->quick_fix_field();
 
+	    Opt_trace_object oto(join->thd->opt_trace); //temporary fix
+	    oto.add(true,"need some description here");
 	    if (sel->test_quick_select(thd, tab->keys,
 				       used_tables & ~ current_map,
 				       (join->select_options &
@@ -9753,15 +9810,19 @@ static bool make_join_select(JOIN *join,
 		we have to check isn't it only "impossible ON" instead
 	      */
               sel->cond=orig_cond;
-              if (!*tab->on_expr_ref ||
-                  sel->test_quick_select(thd, tab->keys,
+              if (!*tab->on_expr_ref)
+                DBUG_RETURN(1);                 // Impossible WHERE
+              else 
+              {
+                if (sel->test_quick_select(thd, tab->keys,
                                          used_tables & ~ current_map,
                                          (join->select_options &
                                           OPTION_FOUND_ROWS ?
                                           HA_POS_ERROR :
                                           join->unit->select_limit_cnt),0,
-                                          FALSE) < 0)
-		DBUG_RETURN(1);			// Impossible WHERE
+                                           FALSE) < 0)
+                  DBUG_RETURN(1);			// Impossible WHERE
+              }
             }
             else
 	      sel->cond=orig_cond;
@@ -18253,6 +18314,8 @@ test_if_quick_select(JOIN_TAB *tab)
 {
   delete tab->select->quick;
   tab->select->quick=0;
+  Opt_trace_object oto(tab->join->thd->opt_trace); //temporary fix
+  oto.add(true,"need some description here2");
   return tab->select->test_quick_select(tab->join->thd, tab->keys,
 					(table_map) 0, HA_POS_ERROR, 0,
                                         FALSE);


Attachment: [text/bzr-bundle] bzr/jorgen.loland@oracle.com-20100917130812-1betv09fdpv1lan0.bundle
Thread
bzr commit into mysql-next-mr-bugfixing branch (jorgen.loland:3203) Jorgen Loland17 Sep
  • Re: bzr commit into mysql-next-mr-bugfixing branch (jorgen.loland:3203)Guilhem Bichot20 Sep