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

 3217 Jorgen Loland	2010-09-29
      WL#5594 - Add optimizer traces to the range optimizer
      
      Add optimizer trace points to the range optimizer.

    modified:
      sql/opt_range.cc
      sql/opt_range.h
      sql/sql_select.cc
=== modified file 'sql/opt_range.cc'
--- a/sql/opt_range.cc	2010-09-27 09:26:39 +0000
+++ b/sql/opt_range.cc	2010-09-29 14:15:38 +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.
@@ -1955,7 +1956,10 @@ 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 *get_name() {DBUG_ASSERT(false); return NULL; }
+  virtual void trace_basic_info(const PARAM *param, Opt_trace_object *trace) 
+    {DBUG_ASSERT(false);}
+  
 };
 
 class TRP_ROR_INTERSECT;
@@ -1996,8 +2000,36 @@ public:
     }
     DBUG_RETURN(quick);
   }
+  const char *get_name(){return "range";}
+  void trace_basic_info(const PARAM *param, Opt_trace_object *trace) {
+    trace->add("table_read_plan_type", get_name()).
+      add("index", param->table->key_info[param->real_keynr[key_idx]].name).
+      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. */
 
@@ -2015,6 +2047,19 @@ 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 *get_name(){return "ror_intersect";}
+  void trace_basic_info(const PARAM *param, Opt_trace_object *trace) {
+    trace->add("table_read_plan_type", get_name());
+    Opt_trace_array ota(param->thd->opt_trace,"ror_intersect of");
+    for (st_ror_scan_info **current= first_scan; 
+         current != last_scan; 
+         current++)
+    {
+      Opt_trace_object (param->thd->opt_trace).
+        add("index", param->table->key_info[(*current)->keynr].name).
+        add("records", (*current)->records);
+    }
+  }
 };
 
 
@@ -2033,6 +2078,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 *get_name(){return "ror_union";}
+  void trace_basic_info(const PARAM *param, Opt_trace_object *trace) {
+    trace->add("table_read_plan_type", get_name());
+    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 trp_info(param->thd->opt_trace);
+      (*current)->trace_basic_info(param,&trp_info);
+    }
+  }
 };
 
 
@@ -2051,6 +2108,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 *get_name(){return "index_merge";}
+  void trace_basic_info(const PARAM *param, Opt_trace_object *trace) {
+    trace->add("table_read_plan_type", get_name());
+    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 trp_info(param->thd->opt_trace);
+      (*current)->trace_basic_info(param,&trp_info);
+    }
+  }
 };
 
 
@@ -2078,6 +2147,12 @@ public:
   /* Number of records selected by the ranges in index_tree. */
   ha_rows quick_prefix_records;
 public:
+  const char *get_idx_name(){return index_info->name;}
+  const char *get_name(){return "group_min_max";}
+  void trace_basic_info(const PARAM *param, Opt_trace_object *trace) {
+    trace->add("table_read_plan_type", get_name()).
+      add("index", get_idx_name());
+  }
   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,
@@ -2246,12 +2321,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 trace_range(thd->opt_trace, "range_analysis");
   Opt_trace_object(thd->opt_trace, "table_scan").
-    add(head->file->stats.records, "records").
-    add(read_time, "cost");
-#endif
+    add("records", head->file->stats.records).
+    add("cost", read_time);
 
   keys_to_use.intersect(head->keys_in_use_for_query);
   if (!keys_to_use.is_clear_all())
@@ -2302,9 +2375,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 trace_idx(thd->opt_trace, "potential_range_indices");
       /*
         Make an array with description of all key parts of all table keys.
         This is used in get_mm_parts function.
@@ -2312,17 +2383,25 @@ 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 trace_idx_details(thd->opt_trace);
+        trace_idx_details.add("index", key_info->name);
         KEY_PART_INFO *key_part_info;
         if (!keys_to_use.is_set(idx))
+        {
+          trace_idx_details.add("usable", false).
+            add("cause", "not_in_keys_to_use");
           continue;
+        }
         if (key_info->flags & HA_FULLTEXT)
+        {
+          trace_idx_details.add("usable", false).
+            add("cause", "fulltext");
           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 trace_keypart(thd->opt_trace, "key_parts");
         for (uint part=0 ; part < key_info->key_parts ;
              part++, key_parts++, key_part_info++)
         {
@@ -2336,6 +2415,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;
+          trace_keypart.add(key_parts->field->field_name);
         }
         param.real_keynr[param.keys++]=idx;
       }
@@ -2359,12 +2439,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").
-        add(head->key_info[key_for_use].name, "index").
-        add(key_read_time, "cost").
-        add(chosen, "chosen");
-#endif
+      Opt_trace_object(thd->opt_trace, "best_full_index_scan").
+        add("index", head->key_info[key_for_use].name).
+        add("cost", key_read_time).
+        add("chosen", chosen);
     }
 
     TABLE_READ_PLAN *best_trp= NULL;
@@ -2377,6 +2455,8 @@ int SQL_SELECT::test_quick_select(THD *t
       {
         if (tree->type == SEL_TREE::IMPOSSIBLE)
         {
+          trace_range.add("usable", false).
+            add("cause", "impossible_select");
           records=0L;                      /* Return -1 from this function. */
           read_time= (double) HA_POS_ERROR;
           goto free_mem;
@@ -2386,7 +2466,10 @@ 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)
+        {
+          trace_range.add("range_scan_possible", false);
           tree= NULL;
+        }
       }
     }
 
@@ -2399,6 +2482,10 @@ int SQL_SELECT::test_quick_select(THD *t
     {
       param.table->quick_condition_rows= min(group_trp->records,
                                              head->file->stats.records);
+      Opt_trace_object (thd->opt_trace, "group_range_summary").
+        add("best_index", ((TRP_GROUP_MIN_MAX*)group_trp)->get_idx_name()).
+        add("records", group_trp->records).
+        add("cost", group_trp->read_cost);
       if (group_trp->read_cost < best_read_time)
       {
         best_trp= group_trp;
@@ -2414,14 +2501,18 @@ int SQL_SELECT::test_quick_select(THD *t
       */
       if (tree->merges.is_empty())
       {
+        Opt_trace_object trace_range(thd->opt_trace, 
+                                     "analyzing_range_alternatives");
         TRP_RANGE         *range_trp;
         TRP_ROR_INTERSECT *rori_trp;
         bool can_build_covering= FALSE;
+        bool found_range= false;
 
         /* 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)))
         {
+          found_range= true;
           best_trp= range_trp;
           best_read_time= best_trp->read_cost;
         }
@@ -2441,6 +2532,7 @@ int SQL_SELECT::test_quick_select(THD *t
           if ((rori_trp= get_best_ror_intersect(&param, tree, best_read_time,
                                                 &can_build_covering)))
           {
+            found_range= true;
             best_trp= rori_trp;
             best_read_time= best_trp->read_cost;
             /*
@@ -2450,7 +2542,10 @@ 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)))
+            {
+              trace_range.add("made_ror_intersect_covering", true);
               best_trp= rori_trp;
+            }
           }
         }
       }
@@ -2465,18 +2560,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 trace_idx_merge(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;
         }
       }
     }
@@ -2492,18 +2595,28 @@ int SQL_SELECT::test_quick_select(THD *t
         delete quick;
         quick= NULL;
       }
+
+      Opt_trace_object trace_range_summary(thd->opt_trace, 
+                                           "chosen_range_access_summary");
+      {
+        Opt_trace_object trp_info(thd->opt_trace, "details");
+        best_trp->trace_basic_info(&param,&trp_info);
+      }
+      trace_range_summary.add("total_records", quick->records);
+      trace_range_summary.add("total_cost", quick->read_time);
+      trace_range_summary.add("chosen", true);
     }
+    else
+      Opt_trace_object (thd->opt_trace, "chosen_range_access_summary").
+        add("chosen", false);
 
-#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;
   }
+  trace_range.add("records", records);
 #ifndef NO_OPT_TRACE_FOR_RANGE_OPT
-  oto.add(records, "records");
   OPT_TRACE2(thd->opt_trace, print_quick(quick, &needed_reg));
 #endif
 
@@ -3837,48 +3950,59 @@ 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 trace_best_disjunct(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;
+  {
+    /*
+      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.
+    */
+    Opt_trace_array ota(param->thd->opt_trace,"indices_to_merge");
+    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 trace_idx(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.
+        */
+        trace_idx.add("chosen", false).add("cause", "expensive");
+        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;
+      uint real_key= param->real_keynr[(*cur_child)->key_idx];
+      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 &&
+          real_key == param->table->s->primary_key)
+      {
+        cpk_scan= cur_child;
+        cpk_scan_records= (*cur_child)->records;
+      }
+      else
+        non_cpk_scan_records += (*cur_child)->records;
+
+      trace_idx.
+        add("index_to_merge", param->table->key_info[real_key].name).
+        add("new_cost", imerge_cost);
     }
-    else
-      non_cpk_scan_records += (*cur_child)->records;
   }
-
+  trace_best_disjunct.add("scan_cost", imerge_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) &&
@@ -3890,6 +4014,8 @@ TABLE_READ_PLAN *get_best_disjunct_quick
     */
     DBUG_PRINT("info", ("Sum of index_merge scans is more expensive than "
                         "full table scan, bailing out"));
+    trace_best_disjunct.add("chosen", false).
+      add("cause", "more_expensive_than_table_scan");
     DBUG_RETURN(NULL);
   }
 
@@ -3902,6 +4028,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;
+    trace_best_disjunct.add("use_ror_union", true).
+      add("cause", "always_cheaper_than_non_ror");
     goto skip_to_ror_scan;
   }
 
@@ -3928,6 +4056,8 @@ TABLE_READ_PLAN *get_best_disjunct_quick
   if (imerge_cost > read_time || 
       !param->thd->optimizer_switch_flag(OPTIMIZER_SWITCH_INDEX_MERGE_SORT_UNION))
   {
+    trace_best_disjunct.add("use_ror_index_merge", true).
+      add("cause", "index_merge_cheaper");
     goto build_ror_index_merge;
   }
 
@@ -3948,6 +4078,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);
+  trace_best_disjunct.add("total_cost", imerge_cost);
   DBUG_PRINT("info",("index_merge total cost: %g (wanted: less then %g)",
                      imerge_cost, read_time));
   if (imerge_cost < read_time)
@@ -3983,44 +4114,52 @@ 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(param->thd->opt_trace, "analyzing_ror_scans");
+    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;
 
-    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;
+      Opt_trace_object trp_info(param->thd->opt_trace);
+      (*cur_child)->trace_basic_info(param, &trp_info);
+
+      /*
+        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;
+        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;
   }
 
   /*
@@ -4052,6 +4191,8 @@ skip_to_ror_scan:
                      sweep_cost.total_cost();
   }
 
+  trace_best_disjunct.add("ror_union_cost", roru_total_cost).
+    add("members", n_child_scans);
   DBUG_PRINT("info", ("ROR-union: cost %g, %d members", roru_total_cost,
                       n_child_scans));
   TRP_ROR_UNION* roru;
@@ -4059,6 +4200,7 @@ skip_to_ror_scan:
   {
     if ((roru= new (param->mem_root) TRP_ROR_UNION))
     {
+      trace_best_disjunct.add("chosen", true);
       roru->first_ror= roru_read_plans;
       roru->last_ror= roru_read_plans + n_child_scans;
       roru->read_cost= roru_total_cost;
@@ -4066,34 +4208,12 @@ skip_to_ror_scan:
       DBUG_RETURN(roru);
     }
   }
+  trace_best_disjunct.add("chosen", false);
+
   DBUG_RETURN(imerge_trp);
 }
 
 
-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.
@@ -4614,9 +4734,17 @@ TRP_ROR_INTERSECT *get_best_ror_intersec
   double min_cost= DBL_MAX;
   DBUG_ENTER("get_best_ror_intersect");
 
+  Opt_trace_object trace_ror(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)
+      trace_ror.add("usable", false).add("cause", "too_few_ror_scans");
+    else
+      trace_ror.add("usable", false).add("done_tracing", false);
     DBUG_RETURN(NULL);
+  }
 
   /*
     Step1: Collect ROR-able SEL_ARGs and create ROR_SCAN_INFO for each of 
@@ -4683,28 +4811,42 @@ TRP_ROR_INTERSECT *get_best_ror_intersec
   ROR_SCAN_INFO **intersect_scans_best;
   cur_ror_scan= tree->ror_scans;
   intersect_scans_best= intersect_scans;
-  while (cur_ror_scan != tree->ror_scans_end && !intersect->is_covering)
   {
-    /* S= S + first(R);  R= R - first(R); */
-    if (!ror_intersect_add(intersect, *cur_ror_scan, FALSE))
+    Opt_trace_array ota(param->thd->opt_trace, "intersecting_indices");
+    while (cur_ror_scan != tree->ror_scans_end && !intersect->is_covering)
     {
-      cur_ror_scan++;
-      continue;
-    }
+      Opt_trace_object trace_idx(param->thd->opt_trace);
+      trace_idx.add("index", 
+                    param->table->key_info[(*cur_ror_scan)->keynr].name);
+      /* S= S + first(R);  R= R - first(R); */
+      if (!ror_intersect_add(intersect, *cur_ror_scan, FALSE))
+      {
+        trace_idx.add("used_in_intersect", false).
+          add("cause", "cost_not_reduced");
+        cur_ror_scan++;
+        continue;
+      }
     
-    *(intersect_scans_end++)= *(cur_ror_scan++);
-
-    if (intersect->total_cost < min_cost)
-    {
-      /* Local minimum found, save it */
-      ror_intersect_cpy(intersect_best, intersect);
-      intersect_scans_best= intersect_scans_end;
-      min_cost = intersect->total_cost;
+      trace_idx.add("used_in_intersect", true).
+        add("matching_records_now", intersect->out_rows).
+        add("cost_now", intersect->total_cost).
+        add("covering_now", intersect->is_covering);
+
+      *(intersect_scans_end++)= *(cur_ror_scan++);
+
+      if (intersect->total_cost < min_cost)
+      {
+        /* Local minimum found, save it */
+        ror_intersect_cpy(intersect_best, intersect);
+        intersect_scans_best= intersect_scans_end;
+        min_cost = intersect->total_cost;
+      }
     }
   }
 
   if (intersect_scans_best == intersect_scans)
   {
+    trace_ror.add("increases_selectivity", false).add("chosen", false);
     DBUG_PRINT("info", ("None of scans increase selectivity"));
     DBUG_RETURN(NULL);
   }
@@ -4728,9 +4870,22 @@ TRP_ROR_INTERSECT *get_best_ror_intersec
     if (ror_intersect_add(intersect, cpk_scan, TRUE) && 
         (intersect->total_cost < min_cost))
     {
+      Opt_trace_object (param->thd->opt_trace, "clustered_pk").
+        add("cpk_added_to_intersect", true).
+        add("new_cost", intersect->total_cost);
       cpk_scan_used= TRUE;
       intersect_best= intersect; //just set pointer here
     }
+    else
+      Opt_trace_object (param->thd->opt_trace, "clustered_pk").
+        add("cpk_added_to_intersect", false).add("cause", "increased_cost");
+  } 
+  else 
+  {
+    Opt_trace_object trace_cpk(param->thd->opt_trace, "clustered_pk");
+    trace_cpk.add("cpk_added_to_intersect", false);
+    cpk_scan ? trace_cpk.add("cause", "ror_is_covering")
+             : trace_cpk.add("cause", "no_clustered_pk_index");
   }
 
   /* Ok, return ROR-intersect plan if we have found one */
@@ -4755,10 +4910,18 @@ TRP_ROR_INTERSECT *get_best_ror_intersec
     trp->records= best_rows;
     trp->index_scan_costs= intersect_best->index_scan_costs;
     trp->cpk_scan= cpk_scan_used? cpk_scan: NULL;
+
+    trace_ror.add("records", trp->records).
+      add("cost", trp->read_cost).
+      add("is_covering", trp->is_covering).
+      add("cpk_scan", cpk_scan_used).
+      add("chosen", true);
+
     DBUG_PRINT("info", ("Returning non-covering ROR-intersect plan:"
                         "cost %g, records %lu",
                         trp->read_cost, (ulong) trp->records));
   }
+  else trace_ror.add("chosen", false);
   DBUG_RETURN(trp);
 }
 
@@ -4805,6 +4968,11 @@ TRP_ROR_INTERSECT *get_best_covering_ror
   ROR_SCAN_INFO **ror_scans_end= tree->ror_scans_end;
   DBUG_ENTER("get_best_covering_ror_intersect");
 
+  Opt_trace_object (param->thd->opt_trace).
+    add("get_best_covering_ror_intersect", true).
+    add("untested_code", true).
+    add("need_tracing",true);
+
   if (!param->thd->optimizer_switch_flag(OPTIMIZER_SWITCH_INDEX_MERGE_INTERSECT))
     DBUG_RETURN(NULL);
 
@@ -4964,12 +5132,11 @@ 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 ota(param->thd->opt_trace, "range_scan_alternatives");
 
   tree->ror_scans_map.clear_all();
   tree->n_ror_scans= 0;
+
   for (idx= 0,key=tree->keys, end=key+param->keys; key != end; key++,idx++)
   {
     if (*key)
@@ -4986,42 +5153,38 @@ 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");
-      oto2.add(param->table->key_info[keynr].name, "index");
-#endif
+      Opt_trace_object trace_idx(param->thd->opt_trace);
+      trace_idx.add("index", param->table->key_info[keynr].name);
+
       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
+      trace_idx.add("index_only", read_index_only).
+        add("records", found_records).
+        add("cost", cost.total_cost());
 
       if ((found_records != HA_POS_ERROR) && param->is_ror_scan)
       {
-#ifndef NO_OPT_TRACE_FOR_RANGE_OPT
-        oto2.add(true, "rowid_ordered");
-#endif
+        trace_idx.add("rowid_ordered", true);
         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
+        trace_idx.add("rowid_ordered", false);
 
       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
+        trace_idx.add("chosen", true);
         read_time=    found_read_time;
         best_records= found_records;
         key_to_read=  key;
         best_mrr_flags= mrr_flags;
         best_buf_size=  buf_size;
       }
+      else
+        trace_idx.add("chosen", false).add("cause", "higher_cost");
+
     }
   }
 
@@ -5037,10 +5200,6 @@ 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,
@@ -5100,10 +5259,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))
@@ -8085,7 +8246,6 @@ get_quick_select(PARAM *param,uint idx,S
     quick=new QUICK_RANGE_SELECT(param->thd, param->table,
                                  param->real_keynr[idx],
                                  test(parent_alloc), NULL, &create_err);
-
   if (quick)
   {
     if (create_err ||
@@ -9722,14 +9882,29 @@ get_best_group_min_max(PARAM *param, SEL
 
   DBUG_ENTER("get_best_group_min_max");
 
+  Opt_trace_object trace_group(thd->opt_trace, "group_index_range");
+
   /* Perform few 'cheap' tests whether this access method is applicable. */
   if (!join)
+  {
+    trace_group.add("chosen", false).add("cause", "no join");
     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 */
+  }
+  if (join->tables != 1)   /* The query must reference one table. */
+  {
+    trace_group.add("chosen", false).add("cause", "not_single_table");
+    DBUG_RETURN(NULL);
+  }
+  if (join->select_lex->olap == ROLLUP_TYPE) /* Check (B3) for ROLLUP */
+  {
+    trace_group.add("chosen", false).add("cause", "is_rollup");
     DBUG_RETURN(NULL);
+  }
   if (table->s->keys == 0)        /* There are no indexes to use. */
+  {
+    trace_group.add("chosen", false).add("cause", "no_index");
     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))
@@ -9741,7 +9916,10 @@ 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)
+  {
+    trace_group.add("chosen", false).add("cause", "not_group_by_or_distinct");
     DBUG_RETURN(NULL);
+  }
   /* Analyze the query in more detail. */
 
   if (join->sum_funcs[0])
@@ -9759,7 +9937,11 @@ get_best_group_min_max(PARAM *param, SEL
                min_max_item->sum_func() == Item_sum::AVG_DISTINCT_FUNC)
         continue;
       else
+      {
+        trace_group.add("chosen", false).
+          add("cause", "not_applicable_aggregate_function");
         DBUG_RETURN(NULL);
+      }
 
       /* The argument of MIN/MAX. */
       Item *expr= min_max_item->get_arg(0)->real_item();
@@ -9777,6 +9959,7 @@ get_best_group_min_max(PARAM *param, SEL
   /* Check (SA5). */
   if (join->select_distinct)
   {
+    trace_group.add("is_distinct_query", true);
     while ((item= select_items_it++))
     {
       if (item->real_item()->type() != Item::FIELD_ITEM)
@@ -9788,7 +9971,11 @@ 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)
+    {
+      trace_group.add("chosen", false).
+        add("cause", "group_field_is_expression");
       DBUG_RETURN(NULL);
+    }
   }
 
   /*
@@ -9810,9 +9997,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, "potential_group_range_indices");
   for (uint cur_index= 0 ; cur_index_info != cur_index_info_end ;
        cur_index_info++, cur_index++)
   {
+    Opt_trace_object trace_idx(thd->opt_trace);
+    trace_idx.add("index", table->key_info[cur_index].name);
     KEY_PART_INFO *cur_part;
     KEY_PART_INFO *end_part; /* Last part for loops. */
     /* Last index part. */
@@ -9832,7 +10022,10 @@ get_best_group_min_max(PARAM *param, SEL
     
     /* Check (B1) - if current index is covering. */
     if (!table->covering_keys.is_set(cur_index))
+    {
+      trace_idx.add("usable", false).add("covering", false);
       goto next_index;
+    }
 
     /*
       If the current storage manager is such that it appends the primary key to
@@ -9856,9 +10049,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))
+        {
+          trace_idx.add("usable", false).add("covering", false);
           goto next_index;                  // Field was not part of key
+        }
       }
     }
+    trace_idx.add("covering", true);
 
     /*
       Check (GA1) for GROUP BY queries.
@@ -9887,9 +10084,13 @@ get_best_group_min_max(PARAM *param, SEL
           used_key_parts_map.set_bit(max_key_part);
         }
         else
+        {
+          trace_idx.add("usable", false).add("needs_tracing",true);
           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
@@ -9953,7 +10154,11 @@ 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)
+      {
+        trace_idx.add("usable", false).
+          add("cause", "aggregate_column_not_suffix_in_idx");
         goto next_index;
+      }
       min_max_arg_part= cur_index_info->key_part + key_part_nr - 1;
     }
 
@@ -9994,7 +10199,10 @@ get_best_group_min_max(PARAM *param, SEL
                                     last_part, thd, cur_key_infix, 
                                     &cur_key_infix_len,
                                     &first_non_infix_part))
+        {
+          trace_idx.add("usable", false).add("todo", true);
           goto next_index;
+        }
       }
       else if (min_max_arg_part &&
                (min_max_arg_part - first_non_group_part > 0))
@@ -10003,6 +10211,7 @@ get_best_group_min_max(PARAM *param, SEL
           There is a gap but no range tree, thus no predicates at all for the
           non-group keyparts.
         */
+        trace_idx.add("usable", false).add("todo2", true);
         goto next_index;
       }
       else if (first_non_group_part && join->conds)
@@ -10026,7 +10235,10 @@ get_best_group_min_max(PARAM *param, SEL
         /* Check if cur_part is referenced in the WHERE clause. */
         if (join->conds->walk(&Item::find_item_in_field_list_processor, 0,
                               (uchar*) key_part_range))
+        {
+          trace_idx.add("usable", false).add("todo3", true);
           goto next_index;
+        }
       }
     }
 
@@ -10041,7 +10253,10 @@ get_best_group_min_max(PARAM *param, SEL
       for (; cur_part != last_part; cur_part++)
       {
         if (bitmap_is_set(table->read_set, cur_part->field->field_index))
+        {
+          trace_idx.add("usable", false).add("todo4", true);
           goto next_index;
+        }
       }
     }
 
@@ -10075,6 +10290,8 @@ get_best_group_min_max(PARAM *param, SEL
       Do not compare doubles directly because they may have different
       representations (64 vs. 80 bits).
     */
+    trace_idx.add("records", cur_records);
+    trace_idx.add("cost", cur_read_cost);
     if (cur_read_cost < best_read_cost - (DBL_EPSILON * cur_read_cost))
     {
       index_info= cur_index_info;
@@ -10094,6 +10311,7 @@ get_best_group_min_max(PARAM *param, SEL
 
   next_index:;
   }
+  
   if (!index_info) /* No usable index found. */
     DBUG_RETURN(NULL);
 
@@ -10102,8 +10320,10 @@ get_best_group_min_max(PARAM *param, SEL
       !check_group_min_max_predicates(join->conds, min_max_arg_item,
                                       (index_info->flags & HA_SPATIAL) ?
                                       Field::itMBR : Field::itRAW))
+  {
+    Opt_trace_object (thd->opt_trace, "not_traced");
     DBUG_RETURN(NULL);
-
+  }
   /* The query passes all tests, so construct a new TRP object. */
   read_plan= new (param->mem_root)
                  TRP_GROUP_MIN_MAX(have_min, have_max, is_agg_distinct,
@@ -10123,6 +10343,7 @@ get_best_group_min_max(PARAM *param, SEL
     read_plan->records=   best_records;
     if (read_time < best_read_cost && is_agg_distinct)
     {
+      Opt_trace_object (thd->opt_trace, "not_traced");
       read_plan->read_cost= 0;
       read_plan->use_index_scan();
     }
@@ -11780,13 +12001,13 @@ static void print_sel_tree(PARAM *param,
   }
   if (!tmp.length())
     tmp.append(STRING_WITH_LEN("(empty)"));
+//  tmp.append(STRING_WITH_LEN("\0"));
 
   DBUG_PRINT("info", ("SEL_TREE: %p (%s)  scans: %s", tree, msg, tmp.ptr()));
-  fprintf(DBUG_FILE,"SEL_TREE: %p (%s)  scans: %s", tree, msg, tmp.ptr());
+  fprintf(DBUG_FILE,"SEL_TREE: %p (%s)  scans: %s\n", tree, msg, tmp.ptr());
   DBUG_VOID_RETURN;
 }
 
-
 static void print_ror_scans_arr(TABLE *table, const char *msg,
                                 struct st_ror_scan_info **start,
                                 struct st_ror_scan_info **end)

=== 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-29 14:15:38 +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);
+
     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-27 09:26:39 +0000
+++ b/sql/sql_select.cc	2010-09-29 14:15:38 +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);
@@ -4943,14 +4947,27 @@ make_join_statistics(JOIN *join, TABLE_L
 
       for (s= stat ; s < stat_end ; s++)
       {
+        Opt_trace_object trace_table(join->thd->opt_trace);
+        trace_table.add("table", s->table->alias);
         if (s->type == JT_SYSTEM || s->type == JT_CONST)
         {
+          trace_table.add("found_records", 1).
+            add("records", 1).
+            add("cost", 1).
+            add("worst_seeks", 1.0);
+
+          if (s->ref.key_parts)
+            trace_table.add("index", (s->table->key_info+ s->ref.key)->name);
+
+          if (s->type == JT_SYSTEM)
+            trace_table.add("cause", "SYSTEM_table");
+          else
+            trace_table.add("cause", "CONST_table");
+
           /* 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("table", s->table->alias);
         /* 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();
@@ -4981,10 +4998,6 @@ make_join_statistics(JOIN *join, TABLE_L
              (s->table->pos_in_table_list->embedding &&             // (3)
               s->table->pos_in_table_list->embedding->sj_on_expr))) // (3)
         {
-#ifdef NO_OPT_TRACE_FOR_RANGE_OPT
-          Opt_trace_object(join->thd->opt_trace, "more_range_optimizer_trace").
-            add("TODO?", "yes!");
-#endif
           ha_rows records;
           SQL_SELECT *select;
           select= make_select(s->table, found_const_table_map,
@@ -5013,9 +5026,18 @@ make_join_statistics(JOIN *join, TABLE_L
             {
               /* Generate empty row */
               s->info= "Impossible ON condition";
+              trace_table.add("returning_empty_null_row", true).
+                add("cause", "impossible_on_condition").
+                add("found test hit1", "YAY");
               found_const_table_map|= s->table->map;
               s->type= JT_CONST;
               mark_as_null_row(s->table);		// All fields are NULL
+            } 
+            else 
+            {
+              trace_table.add("records", 0).
+                add("cause", "impossible_where_condition").
+                add("found test hit2", "YAY");
             }
           }
           if (records != HA_POS_ERROR)
@@ -6494,11 +6516,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. */
@@ -6508,10 +6533,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;
@@ -6527,8 +6554,32 @@ add_group_and_distinct_keys(JOIN *join, 
     possible_keys.intersect(cur_item->field->part_of_key);
   }
 
-  if (!possible_keys.is_clear_all())
+  if (!possible_keys.is_clear_all() && 
+      !(possible_keys == join_tab->const_keys))
+  {
+    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 trace_summary(thd->opt_trace, "const_keys_added");
+  {
+    Opt_trace_array trace_key(thd->opt_trace,"keys");
+    for (uint j=0 ; j < nokeys ; j++)
+      if (new_keys.is_set(j) && !existing_keys.is_set(j))
+        trace_key.add(key_info[j].name);
+  }
+  trace_summary.add("cause", cause);
 }
 
 
@@ -6970,8 +7021,8 @@ best_access_path(JOIN      *join,
       loose_scan_opt.next_ref_key();
       DBUG_PRINT("info", ("Considering ref access on key %s",
                           keyuse->table->key_info[keyuse->key].name));
-      Opt_trace_object oto1(thd->opt_trace);
-      oto1.add("access_type", "index").add("index", keyinfo->name);
+      Opt_trace_object trace_access_idx(thd->opt_trace);
+      trace_access_idx.add("access_type", "index").add("index", keyinfo->name);
 
       /* 
          True if we find some keys from the range optimizer that match more
@@ -7027,7 +7078,7 @@ best_access_path(JOIN      *join,
       */
       if (!found_part && !ft_key && !loose_scan_opt.have_a_case())
       {
-        oto1.add("usable", false);
+        trace_access_idx.add("usable", false);
         goto done_with_index;                  // Nothing usable found
       }
 
@@ -7306,7 +7357,7 @@ best_access_path(JOIN      *join,
         loose_scan_opt.check_ref_access_part2(key, start_key, records, tmp);
 
       } /* not ft_key */
-      oto1.add("cost", tmp).add("records", records);
+      trace_access_idx.add("records", records).add("cost", tmp);
       /** @todo trace quick_matches_more_parts etc? */
       if (tmp < best_time - records/(double) TIME_FOR_COMPARE ||
           (quick_matches_more_parts && 
@@ -7321,13 +7372,13 @@ best_access_path(JOIN      *join,
         best_ref_depends_map= found_ref;
       }
   done_with_index:
-      oto1.add("chosen", best_key == start_key);
+      trace_access_idx.add("chosen", best_key == start_key);
     } /* for each key */
     records= best_records;
   }
 
-  Opt_trace_object oto1(thd->opt_trace);
-  oto1.add("access_type", "table scan");
+  Opt_trace_object trace_access_scan(thd->opt_trace);
+  trace_access_scan.add("access_type", "scan");
   /*
     Don't test table scan if it can't be better.
     Prefer key lookup if we would use the same key for scanning.
@@ -7358,27 +7409,31 @@ best_access_path(JOIN      *join,
   */
   if (!(records >= s->found_records || best > s->read_time))            // (1)
   {
-    oto1.add("cost", s->read_time).add("records", s->found_records);
+    trace_access_scan.add("cost", s->read_time).
+      add("records", s->found_records).
+      add("chosen", false);
     goto skip_table_scan;
   }
 
   if ((s->quick && best_key && s->quick->index == best_key->key &&      // (2)
        best_max_key_part >= s->table->quick_key_parts[best_key->key]))  // (2)
   {
-    oto1.add("heuristic_index_must_be_cheaper", true);
+    trace_access_scan.add("chosen", false).
+      add("cause", "heuristic_index_cheaper");
     goto skip_table_scan;
   }
 
   if (((s->table->file->ha_table_flags() & HA_TABLE_SCAN_ON_INDEX) &&     // (3)
        ! s->table->covering_keys.is_clear_all() && best_key && !s->quick))// (3)
   {
-    oto1.add("index_is_covering", true);
+    trace_access_scan.add("chosen", false).
+      add("cause", "covering_index_better_than_full_scan");
     goto skip_table_scan;
   }
 
   if ((s->table->force_index && best_key && !s->quick))                 // (4)
   {
-    oto1.add("FORCE_INDEX_used", true);
+    trace_access_scan.add("chosen", false).add("cause", "force_index");
     goto skip_table_scan;
   }
 
@@ -7411,6 +7466,7 @@ best_access_path(JOIN      *join,
 
     if (s->quick)
     {
+      trace_access_scan.add("using_range_access", true);
       /*
         For each record we:
         - read record range through 'quick'
@@ -7428,6 +7484,7 @@ best_access_path(JOIN      *join,
     }
     else
     {
+      trace_access_scan.add("using_range_access", false);
       /* Estimate cost of reading table. */
       if (s->table->force_index && !best_key)
         tmp= s->table->file->read_time(s->ref.key, 1, s->records);
@@ -7447,6 +7504,8 @@ best_access_path(JOIN      *join,
       }
       else
       {
+        trace_access_scan.add("using_join_cache", true).
+          add("join_cache_has_tracing", false);
         /*
           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
@@ -7466,7 +7525,8 @@ best_access_path(JOIN      *join,
       }
     }
 
-    oto1.add("cost", tmp).add("records", rows2double(rnd_records));
+    Opt_trace_object (thd->opt_trace, "with_where_cond_processing").
+      add("records", rows2double(rnd_records)).add("cost", tmp);
     /*
       We estimate the cost of evaluating WHERE clause for found records
       as record_count * rnd_records / TIME_FOR_COMPARE. This cost plus
@@ -7489,11 +7549,10 @@ best_access_path(JOIN      *join,
                                                join->outer_join)));
     }
   }
+  trace_access_scan.add("chosen", best_key == NULL);
 
 skip_table_scan:
 
-  oto1.add("chosen", best_key == NULL);
-
   /* Update the cost information for the current partial plan */
   pos->records_read= records;
   pos->read_time=    best;
@@ -7509,7 +7568,10 @@ skip_table_scan:
       idx == join->const_tables &&
       s->table == join->sort_by_table &&
       join->unit->select_limit_cnt >= records)
+  {
+    trace_access_scan.add("use_temp_table", true);
     join->sort_by_table= (TABLE*) 1;  // Must use temporary table
+  }
 
   DBUG_VOID_RETURN;
 }
@@ -9812,32 +9874,36 @@ static bool make_join_select(JOIN *join,
 	    if (sel->cond && !sel->cond->fixed)
 	      sel->cond->quick_fix_field();
 
-	    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)
             {
-	      /*
-		Before reporting "Impossible WHERE" for the whole query
-		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,
+              Opt_trace_object trace_recheck(join->thd->opt_trace);
+              trace_recheck.add("rechecking_if_index_can_be_used", true).
+                add("needs_tracing", true);
+              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
+                                          join->unit->select_limit_cnt), 0,
+                                         FALSE) < 0)
+              {
+                /*
+                  Before reporting "Impossible WHERE" for the whole query
+                  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,
+                                           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
+              }
+              else
+                sel->cond=orig_cond;
             }
-            else
-	      sel->cond=orig_cond;
-
 	    /* Fix for EXPLAIN */
 	    if (sel->quick)
 	      join->best_positions[i].records_read= (double)sel->quick->records;


Attachment: [text/bzr-bundle] bzr/jorgen.loland@oracle.com-20100929141538-nyoyn0gns8jm7foz.bundle
Thread
bzr commit into mysql-next-mr-bugfixing branch (jorgen.loland:3217) WL#5594Jorgen Loland29 Sep