#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(¶m, 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(¶m, 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(¶m, 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(¶m, 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(¶m, 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(¶m,&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#5594 | Jorgen Loland | 29 Sep |
