#At file:///export/home/jl208045/mysql/wl4800/mysql-next-mr-opt-backporting-wl4800-patchcleanup/ based on revid:guilhem@stripped
3203 Jorgen Loland 2010-09-17
WL4800 - Optimizer trace/debugger
Intended for premature review only - will not be pushed.
Changeset contains
* --trace-protocol for mtr
* Tracing for the range optimizer
modified:
client/mysqltest.cc
mysql-test/mysql-test-run.pl
sql/opt_range.cc
sql/opt_range.h
sql/sql_select.cc
=== modified file 'client/mysqltest.cc'
--- a/client/mysqltest.cc 2010-08-16 07:30:40 +0000
+++ b/client/mysqltest.cc 2010-09-17 13:08:12 +0000
@@ -87,7 +87,7 @@ enum {
OPT_PS_PROTOCOL=OPT_MAX_CLIENT_OPTION, OPT_SP_PROTOCOL,
OPT_CURSOR_PROTOCOL, OPT_VIEW_PROTOCOL, OPT_MAX_CONNECT_RETRIES,
OPT_MAX_CONNECTIONS, OPT_MARK_PROGRESS, OPT_LOG_DIR,
- OPT_TAIL_LINES, OPT_RESULT_FORMAT_VERSION
+ OPT_TAIL_LINES, OPT_RESULT_FORMAT_VERSION, OPT_TRACE_PROTOCOL
};
static int record= 0, opt_sleep= -1;
@@ -107,6 +107,7 @@ static my_bool opt_mark_progress= 0;
static my_bool ps_protocol= 0, ps_protocol_enabled= 0;
static my_bool sp_protocol= 0, sp_protocol_enabled= 0;
static my_bool view_protocol= 0, view_protocol_enabled= 0;
+static my_bool trace_protocol= 0, trace_protocol_enabled= 0;
static my_bool cursor_protocol= 0, cursor_protocol_enabled= 0;
static my_bool parsing_disabled= 0;
static my_bool display_result_vertically= FALSE, display_result_lower= FALSE,
@@ -194,6 +195,7 @@ static ulong connection_retry_sleep= 100
static my_regex_t ps_re; /* the query can be run using PS protocol */
static my_regex_t sp_re; /* the query can be run as a SP */
static my_regex_t view_re; /* the query can be run as a view*/
+static my_regex_t trace_re; /* the query can be traced with optimizer trace*/
static void init_re(void);
static int match_re(my_regex_t *, char *);
@@ -6069,6 +6071,9 @@ static struct my_option my_long_options[
{"view-protocol", OPT_VIEW_PROTOCOL, "Use views for select.",
&view_protocol, &view_protocol, 0,
GET_BOOL, NO_ARG, 0, 0, 0, 0, 0, 0},
+ {"trace-protocol", OPT_TRACE_PROTOCOL, "Optimizer trace all selects.",
+ &trace_protocol, &trace_protocol, 0,
+ GET_BOOL, NO_ARG, 0, 0, 0, 0, 0, 0},
{"connect_timeout", OPT_CONNECT_TIMEOUT,
"Number of seconds before connection timeout.",
&opt_connect_timeout, &opt_connect_timeout, 0, GET_UINT, REQUIRED_ARG,
@@ -7547,6 +7552,35 @@ void run_query(struct st_connection *cn,
DBUG_VOID_RETURN;
}
+void run_trace(struct st_connection *cn, struct st_command *command, int flags)
+{
+ if (trace_protocol_enabled &&
+ !command->expected_errors.count &&
+ match_re(&trace_re, command->query))
+ {
+ st_command save_command= *command;
+ DYNAMIC_STRING query_str;
+ DYNAMIC_STRING ds_warning_messages;
+
+ init_dynamic_string(&ds_warning_messages, "", 0, 2048);
+ init_dynamic_string(&query_str,
+ "select trace from information_schema.optimizer_trace",
+ 256, 256);
+ command->query= query_str.str;
+ command->query_len= query_str.length;
+ command->end= strend(command->query);
+
+ run_query(cn, command, flags);
+
+ init_dynamic_string(&ds_warning_messages, "", 0, 2048);
+
+ dynstr_free(&query_str);
+ dynstr_free(&ds_warning_messages);
+
+ *command= save_command;
+ }
+}
+
/****************************************************************************/
/*
Functions to detect different SQL statements
@@ -7606,9 +7640,21 @@ void init_re(void)
"^("
"[[:space:]]*SELECT[[:space:]])";
+ /*
+ Filter queries that can be optimizer-traced.
+ Todo:
+ - Also matches "select trace from i_s.optimizer_trace", but it works
+ - Does not match insert/update/delete
+ */
+ const char *trace_re_str =
+ "^("
+ "[[:space:]]*SELECT[[:space:]]|"
+ "[[:space:]]*EXPLAIN[[:space:]]SELECT[[:space:]])";
+
init_re_comp(&ps_re, ps_re_str);
init_re_comp(&sp_re, sp_re_str);
init_re_comp(&view_re, view_re_str);
+ init_re_comp(&trace_re, trace_re_str);
}
@@ -7945,6 +7991,7 @@ int main(int argc, char **argv)
var_set_int("$PS_PROTOCOL", ps_protocol);
var_set_int("$SP_PROTOCOL", sp_protocol);
var_set_int("$VIEW_PROTOCOL", view_protocol);
+ var_set_int("$TRACE_PROTOCOL", trace_protocol);
var_set_int("$CURSOR_PROTOCOL", cursor_protocol);
DBUG_PRINT("info",("result_file: '%s'",
@@ -7967,6 +8014,7 @@ int main(int argc, char **argv)
ps_protocol_enabled= ps_protocol;
sp_protocol_enabled= sp_protocol;
view_protocol_enabled= view_protocol;
+ trace_protocol_enabled= trace_protocol;
cursor_protocol_enabled= cursor_protocol;
/* Cursor protcol implies ps protocol */
if (cursor_protocol_enabled)
@@ -8196,6 +8244,7 @@ int main(int argc, char **argv)
save_file[0]= 0;
}
run_query(cur_con, command, flags);
+ run_trace(cur_con, command, flags);
command_executed++;
command->last_argument= command->end;
=== modified file 'mysql-test/mysql-test-run.pl'
--- a/mysql-test/mysql-test-run.pl 2010-09-03 20:49:28 +0000
+++ b/mysql-test/mysql-test-run.pl 2010-09-17 13:08:12 +0000
@@ -171,6 +171,7 @@ my $opt_ps_protocol;
my $opt_sp_protocol;
my $opt_cursor_protocol;
my $opt_view_protocol;
+my $opt_trace_protocol;
our $opt_debug;
our @opt_cases; # The test cases names in argv
@@ -843,6 +844,7 @@ sub command_line_setup {
'ps-protocol' => \$opt_ps_protocol,
'sp-protocol' => \$opt_sp_protocol,
'view-protocol' => \$opt_view_protocol,
+ 'trace-protocol' => \$opt_trace_protocol,
'cursor-protocol' => \$opt_cursor_protocol,
'ssl|with-openssl' => \$opt_ssl,
'skip-ssl' => \$opt_skip_ssl,
@@ -1441,6 +1443,11 @@ sub command_line_setup {
unless @valgrind_args;
}
+ if ( $opt_trace_protocol )
+ {
+ push(@opt_extra_mysqld_opt, "--optimizer_trace=enabled=on,one_line=off");
+ }
+
if ( $opt_valgrind )
{
# Set valgrind_options to default unless already defined
@@ -5029,6 +5036,11 @@ sub start_mysqltest ($) {
mtr_add_arg($args, "--view-protocol");
}
+ if ( $opt_trace_protocol )
+ {
+ mtr_add_arg($args, "--trace-protocol");
+ }
+
if ( $opt_cursor_protocol )
{
mtr_add_arg($args, "--cursor-protocol");
@@ -5450,6 +5462,7 @@ Options to control what engine/variation
cursor-protocol Use the cursor protocol between client and server
(implies --ps-protocol)
view-protocol Create a view to execute all non updating queries
+ trace-protocol Print optimizer trace
sp-protocol Create a stored procedure to execute all queries
compress Use the compressed protocol between client and server
ssl Use ssl protocol between client and server
=== modified file 'sql/opt_range.cc'
--- a/sql/opt_range.cc 2010-09-05 16:08:07 +0000
+++ b/sql/opt_range.cc 2010-09-17 13:08:12 +0000
@@ -30,7 +30,8 @@
and builds lists of intervals (in index/partitioning space), such that
all possible records that match the condition are contained within the
intervals.
- The entry point for the range analysis module is get_mm_tree() function.
+ The entry point for the range analysis module is get_mm_tree() (mm=min_max)
+ function.
The lists are returned in form of complicated structure of interlinked
SEL_TREE/SEL_IMERGE/SEL_ARG objects.
@@ -1953,6 +1954,9 @@ public:
static void operator delete(void *ptr,size_t size) { TRASH(ptr, size); }
static void operator delete(void *ptr, MEM_ROOT *mem_root) { /* Never called */ }
virtual ~TABLE_READ_PLAN() {} /* Remove gcc warning */
+ virtual const char *trp_name() {DBUG_ASSERT(false); return NULL; }
+ virtual void trace_basic_info(PARAM *param, Opt_trace_object *oto)
+ {DBUG_ASSERT(false);}
};
@@ -1994,8 +1998,36 @@ public:
}
DBUG_RETURN(quick);
}
+ const char *trp_name(){return "range";}
+ void trace_basic_info(PARAM *param, Opt_trace_object *oto) {
+ oto->add(trp_name(), "TRP type").
+ add(param->table->s->key_info[key_idx].name,"index").
+ add(records,"records");
+ }
};
+typedef struct st_ror_scan_info
+{
+ uint idx; /* # of used key in param->keys */
+ uint keynr; /* # of used key in table */
+ ha_rows records; /* estimate of # records this scan will return */
+
+ /* Set of intervals over key fields that will be used for row retrieval. */
+ SEL_ARG *sel_arg;
+
+ /* Fields used in the query and covered by this ROR scan. */
+ MY_BITMAP covered_fields;
+ uint used_fields_covered; /* # of set bits in covered_fields */
+ int key_rec_length; /* length of key record (including rowid) */
+
+ /*
+ Cost of reading all index records with values in sel_arg intervals set
+ (assuming there is no need to access full table records)
+ */
+ double index_read_cost;
+ uint first_uncovered_field; /* first unused bit in covered_fields */
+ uint key_components; /* # of parts in the key */
+} ROR_SCAN_INFO;
/* Plan for QUICK_ROR_INTERSECT_SELECT scan. */
@@ -2013,6 +2045,24 @@ public:
struct st_ror_scan_info *cpk_scan; /* Clustered PK scan, if there is one */
bool is_covering; /* TRUE if no row retrieval phase is necessary */
double index_scan_costs; /* SUM(cost(index_scan)) */
+ const char *trp_name(){return "ror_intersect";}
+ void trace_basic_info(PARAM *param, Opt_trace_object *oto) {
+ oto->add(trp_name(), "TRP type");
+ Opt_trace_array ota(param->thd->opt_trace,"ror_intersect of");
+
+ char buff[1024];
+ String tmp(buff,sizeof(buff),&my_charset_bin);
+ tmp.length(0);
+ uint records= 0;
+ for (st_ror_scan_info **current= first_scan;
+ current != last_scan;
+ current++)
+ {
+ Opt_trace_object (param->thd->opt_trace).
+ add(param->table->s->key_info[(*current)->idx].name, "index").
+ add((*current)->records, "records");
+ }
+ }
};
@@ -2031,6 +2081,18 @@ public:
MEM_ROOT *parent_alloc);
TABLE_READ_PLAN **first_ror; /* array of ptrs to plans for merged scans */
TABLE_READ_PLAN **last_ror; /* end of the above array */
+ const char *trp_name(){return "ror_union";}
+ void trace_basic_info(PARAM *param, Opt_trace_object *oto) {
+ oto->add(trp_name(), "TRP type");
+ Opt_trace_array ota(param->thd->opt_trace,"ror union of");
+ for (TABLE_READ_PLAN **current= first_ror;
+ current != last_ror;
+ current++)
+ {
+ Opt_trace_object details(param->thd->opt_trace);
+ (*current)->trace_basic_info(param,&details);
+ }
+ }
};
@@ -2049,6 +2111,18 @@ public:
MEM_ROOT *parent_alloc);
TRP_RANGE **range_scans; /* array of ptrs to plans of merged scans */
TRP_RANGE **range_scans_end; /* end of the array */
+ const char *trp_name(){return "index_merge";}
+ void trace_basic_info(PARAM *param, Opt_trace_object *oto) {
+ oto->add(trp_name(), "TRP type");
+ Opt_trace_array ota(param->thd->opt_trace,"index_merge of");
+ for (TRP_RANGE **current= range_scans;
+ current != range_scans_end;
+ current++)
+ {
+ Opt_trace_object details(param->thd->opt_trace);
+ (*current)->trace_basic_info(param,&details);
+ }
+ }
};
@@ -2076,6 +2150,10 @@ public:
/* Number of records selected by the ranges in index_tree. */
ha_rows quick_prefix_records;
public:
+ const char *trp_name(){return "group_min_max";}
+ void trace_basic_info(PARAM *param, Opt_trace_object *oto) {
+ oto->add(trp_name(), "TRP type").add("more here","todo");
+ }
TRP_GROUP_MIN_MAX(bool have_min_arg, bool have_max_arg,
bool have_agg_distinct_arg,
KEY_PART_INFO *min_max_arg_part_arg,
@@ -2244,12 +2322,10 @@ int SQL_SELECT::test_quick_select(THD *t
else if (read_time <= 2.0 && !force_quick_range)
DBUG_RETURN(0); /* No need for quick select */
-#ifndef NO_OPT_TRACE_FOR_RANGE_OPT
Opt_trace_object oto(thd->opt_trace, "range_analysis");
Opt_trace_object(thd->opt_trace, "table_scan").
add(head->file->stats.records, "records").
add(read_time, "cost");
-#endif
keys_to_use.intersect(head->keys_in_use_for_query);
if (!keys_to_use.is_clear_all())
@@ -2300,9 +2376,7 @@ int SQL_SELECT::test_quick_select(THD *t
thd->mem_root= &alloc;
{
-#ifndef NO_OPT_TRACE_FOR_RANGE_OPT
- Opt_trace_object oto1(thd->opt_trace, "indices");
-#endif
+ Opt_trace_array ota(thd->opt_trace, "possible min-max indices");
/*
Make an array with description of all key parts of all table keys.
This is used in get_mm_parts function.
@@ -2310,17 +2384,23 @@ int SQL_SELECT::test_quick_select(THD *t
key_info= head->key_info;
for (idx=0 ; idx < head->s->keys ; idx++, key_info++)
{
-#ifndef NO_OPT_TRACE_FOR_RANGE_OPT
- oto1.add(key_info->name, "index");
-#endif
+ Opt_trace_object oto2(thd->opt_trace);
+ oto2.add(key_info->name, "index");
KEY_PART_INFO *key_part_info;
if (!keys_to_use.is_set(idx))
+ {
+ oto2.add("false","usable").add("not applicable","cause");
continue;
+ }
if (key_info->flags & HA_FULLTEXT)
+ {
+ oto2.add("false","usable").add("fulltext","cause");
continue; // ToDo: ft-keys in non-ft ranges, if possible SerG
+ }
param.key[param.keys]=key_parts;
key_part_info= key_info->key_part;
+ Opt_trace_array ota1(thd->opt_trace, "key parts");
for (uint part=0 ; part < key_info->key_parts ;
part++, key_parts++, key_part_info++)
{
@@ -2334,6 +2414,7 @@ int SQL_SELECT::test_quick_select(THD *t
(key_info->flags & HA_SPATIAL) ? Field::itMBR : Field::itRAW;
/* Only HA_PART_KEY_SEG is used */
key_parts->flag= (uint8) key_part_info->key_part_flag;
+ ota1.add(key_parts->field->field_name);
}
param.real_keynr[param.keys++]=idx;
}
@@ -2357,12 +2438,10 @@ int SQL_SELECT::test_quick_select(THD *t
chosen= true;
}
-#ifndef NO_OPT_TRACE_FOR_RANGE_OPT
- Opt_trace_object(thd->opt_trace, "best_covering_scan").
+ Opt_trace_object(thd->opt_trace, "best full index scan").
add(head->key_info[key_for_use].name, "index").
add(key_read_time, "cost").
add(chosen, "chosen");
-#endif
}
TABLE_READ_PLAN *best_trp= NULL;
@@ -2375,6 +2454,7 @@ int SQL_SELECT::test_quick_select(THD *t
{
if (tree->type == SEL_TREE::IMPOSSIBLE)
{
+ oto.add(false,"usable").add("SEL_TREE::IMPOSSIBLE","cause");
records=0L; /* Return -1 from this function. */
read_time= (double) HA_POS_ERROR;
goto free_mem;
@@ -2384,7 +2464,11 @@ int SQL_SELECT::test_quick_select(THD *t
can construct a group-min-max quick select
*/
if (tree->type != SEL_TREE::KEY && tree->type != SEL_TREE::KEY_SMALLER)
+ {
+ oto.add(false,"range possible").
+ add("non-KEY comparison and not '<' ","cause");
tree= NULL;
+ }
}
}
@@ -2395,8 +2479,11 @@ int SQL_SELECT::test_quick_select(THD *t
group_trp= get_best_group_min_max(¶m, tree, best_read_time);
if (group_trp)
{
+ Opt_trace_object o(thd->opt_trace, "group min max select");
+ o.add("high priority", "more here");
param.table->quick_condition_rows= min(group_trp->records,
head->file->stats.records);
+ o.add(group_trp->read_cost,"cost");
if (group_trp->read_cost < best_read_time)
{
best_trp= group_trp;
@@ -2412,14 +2499,19 @@ int SQL_SELECT::test_quick_select(THD *t
*/
if (tree->merges.is_empty())
{
+ Opt_trace_object o(thd->opt_trace, "analyzing min-max range");
TRP_RANGE *range_trp;
TRP_ROR_INTERSECT *rori_trp;
bool can_build_covering= FALSE;
+ int type= 0;
+ int index= 0;
/* Get best 'range' plan and prepare data for making other plans */
if ((range_trp= get_key_scans_params(¶m, tree, FALSE, TRUE,
best_read_time)))
{
+ type= 1;
+ index= range_trp->key_idx;
best_trp= range_trp;
best_read_time= best_trp->read_cost;
}
@@ -2439,6 +2531,8 @@ int SQL_SELECT::test_quick_select(THD *t
if ((rori_trp= get_best_ror_intersect(¶m, tree, best_read_time,
&can_build_covering)))
{
+ o.add(true,"todo more on ror intersect");
+ type= 2;
best_trp= rori_trp;
best_read_time= best_trp->read_cost;
/*
@@ -2448,9 +2542,19 @@ int SQL_SELECT::test_quick_select(THD *t
if (!rori_trp->is_covering && can_build_covering &&
(rori_trp= get_best_covering_ror_intersect(¶m, tree,
best_read_time)))
+ {
+ type= 3;
best_trp= rori_trp;
+ }
}
}
+
+ o.add((longlong)type, "chosen plan").add(true,"need something better");
+
+ if (type == 0)
+ o.add(true, "more to do here");
+ if (type == 1)
+ o.add (param.table->key_info[index].name, "best index for range");
}
else
{
@@ -2463,18 +2567,26 @@ int SQL_SELECT::test_quick_select(THD *t
DBUG_PRINT("info",("No range reads possible,"
" trying to construct index_merge"));
List_iterator_fast<SEL_IMERGE> it(tree->merges);
- while ((imerge= it++))
{
- new_conj_trp= get_best_disjunct_quick(¶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 ota(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;
}
}
}
@@ -2490,19 +2602,23 @@ int SQL_SELECT::test_quick_select(THD *t
delete quick;
quick= NULL;
}
+ Opt_trace_object oto2(thd->opt_trace, "quick select");
+
+ best_trp->trace_basic_info(¶m,&oto2);
+
+ oto2.add(quick->records,"total records");
+ oto2.add(quick->read_time,"total cost");
+ oto2.add("maybe","trace quick->mrr_flags?");
}
+
+ oto.add(param.table->quick_condition_rows, "quick_condition_rows (SP)");
-#ifndef NO_OPT_TRACE_FOR_RANGE_OPT
- oto.add(param.table->quick_condition_rows, "quick_condition_rows");
-#endif
- free_mem:
+free_mem:
free_root(&alloc,MYF(0)); // Return memory & allocator
thd->mem_root= param.old_root;
thd->no_errors=0;
}
-#ifndef NO_OPT_TRACE_FOR_RANGE_OPT
- oto.add(records, "records");
-#endif
+ oto.add(records, "records (SP)");
OPT_TRACE2(thd->opt_trace, print_quick(quick, &needed_reg));
/*
@@ -3835,48 +3951,54 @@ TABLE_READ_PLAN *get_best_disjunct_quick
DBUG_ENTER("get_best_disjunct_quick");
DBUG_PRINT("info", ("Full table scan cost: %g", read_time));
+ Opt_trace_object o(param->thd->opt_trace);
if (!(range_scans= (TRP_RANGE**)alloc_root(param->mem_root,
sizeof(TRP_RANGE*)*
n_child_scans)))
DBUG_RETURN(NULL);
- /*
- Collect best 'range' scan for each of disjuncts, and, while doing so,
- analyze possibility of ROR scans. Also calculate some values needed by
- other parts of the code.
- */
- for (ptree= imerge->trees, cur_child= range_scans;
- ptree != imerge->trees_next;
- ptree++, cur_child++)
- {
- DBUG_EXECUTE("info", print_sel_tree(param, *ptree, &(*ptree)->keys_map,
- "tree in SEL_IMERGE"););
- if (!(*cur_child= get_key_scans_params(param, *ptree, TRUE, FALSE, read_time)))
- {
- /*
- One of index scans in this index_merge is more expensive than entire
- table read for another available option. The entire index_merge (and
- any possible ROR-union) will be more expensive then, too. We continue
- here only to update SQL_SELECT members.
- */
- imerge_too_expensive= TRUE;
- }
- if (imerge_too_expensive)
- continue;
+ {
+ Opt_trace_array a(param->thd->opt_trace,"indices to merge");
+ /*
+ Collect best 'range' scan for each of disjuncts, and, while doing so,
+ analyze possibility of ROR scans. Also calculate some values needed by
+ other parts of the code.
+ */
+ for (ptree= imerge->trees, cur_child= range_scans;
+ ptree != imerge->trees_next;
+ ptree++, cur_child++)
+ {
+ DBUG_EXECUTE("info", print_sel_tree(param, *ptree, &(*ptree)->keys_map,
+ "tree in SEL_IMERGE"););
+ Opt_trace_object o2(param->thd->opt_trace);
+ if (!(*cur_child= get_key_scans_params(param, *ptree, TRUE, FALSE, read_time)))
+ {
+ /*
+ One of index scans in this index_merge is more expensive than entire
+ table read for another available option. The entire index_merge (and
+ any possible ROR-union) will be more expensive then, too. We continue
+ here only to update SQL_SELECT members.
+ */
+ o2.add("yay","a hit").add(true,"expensive").add(false,"chosen");
+ imerge_too_expensive= TRUE;
+ }
+ if (imerge_too_expensive)
+ continue;
- imerge_cost += (*cur_child)->read_cost;
- all_scans_ror_able &= ((*ptree)->n_ror_scans > 0);
- all_scans_rors &= (*cur_child)->is_ror;
- if (pk_is_clustered &&
- param->real_keynr[(*cur_child)->key_idx] ==
- param->table->s->primary_key)
- {
- cpk_scan= cur_child;
- cpk_scan_records= (*cur_child)->records;
+ imerge_cost += (*cur_child)->read_cost;
+ all_scans_ror_able &= ((*ptree)->n_ror_scans > 0);
+ all_scans_rors &= (*cur_child)->is_ror;
+ if (pk_is_clustered &&
+ param->real_keynr[(*cur_child)->key_idx] ==
+ param->table->s->primary_key)
+ {
+ cpk_scan= cur_child;
+ cpk_scan_records= (*cur_child)->records;
+ }
+ else
+ non_cpk_scan_records += (*cur_child)->records;
}
- else
- non_cpk_scan_records += (*cur_child)->records;
}
-
+ o.add(imerge_cost,"scan cost");
DBUG_PRINT("info", ("index_merge scans cost %g", imerge_cost));
if (imerge_too_expensive || (imerge_cost > read_time) ||
((non_cpk_scan_records+cpk_scan_records >= param->table->file->stats.records) &&
@@ -3888,6 +4010,7 @@ TABLE_READ_PLAN *get_best_disjunct_quick
*/
DBUG_PRINT("info", ("Sum of index_merge scans is more expensive than "
"full table scan, bailing out"));
+ o.add(false,"chosen").add("more expensive than table scan","cause");
DBUG_RETURN(NULL);
}
@@ -3900,6 +4023,8 @@ TABLE_READ_PLAN *get_best_disjunct_quick
param->thd->optimizer_switch_flag(OPTIMIZER_SWITCH_INDEX_MERGE_UNION))
{
roru_read_plans= (TABLE_READ_PLAN**)range_scans;
+ o.add(true,"Use ROR-union").
+ add("ROR always cheaper than non-ROR","cause");
goto skip_to_ror_scan;
}
@@ -3910,6 +4035,7 @@ TABLE_READ_PLAN *get_best_disjunct_quick
is done in QUICK_RANGE_SELECT::row_in_ranges)
*/
imerge_cost += non_cpk_scan_records / TIME_FOR_COMPARE_ROWID;
+ o.add(imerge_cost,"clustered scan new cost");
}
/* Calculate cost(rowid_to_row_scan) */
@@ -3921,11 +4047,13 @@ TABLE_READ_PLAN *get_best_disjunct_quick
&sweep_cost);
imerge_cost += sweep_cost.total_cost();
}
+ o.add(imerge_cost,"cost with sweep");
DBUG_PRINT("info",("index_merge cost with rowid-to-row scan: %g",
imerge_cost));
if (imerge_cost > read_time ||
!param->thd->optimizer_switch_flag(OPTIMIZER_SWITCH_INDEX_MERGE_SORT_UNION))
{
+ o.add(true,"going for ror index merge").add("yay","more here");
goto build_ror_index_merge;
}
@@ -3946,6 +4074,7 @@ TABLE_READ_PLAN *get_best_disjunct_quick
Unique::get_use_cost(param->imerge_cost_buff, (uint)non_cpk_scan_records,
param->table->file->ref_length,
param->thd->variables.sortbuff_size);
+ o.add(imerge_cost,"total cost");
DBUG_PRINT("info",("index_merge total cost: %g (wanted: less then %g)",
imerge_cost, read_time));
if (imerge_cost < read_time)
@@ -3981,44 +4110,50 @@ skip_to_ror_scan:
cur_roru_plan= roru_read_plans;
/* Find 'best' ROR scan for each of trees in disjunction */
- for (ptree= imerge->trees, cur_child= range_scans;
- ptree != imerge->trees_next;
- ptree++, cur_child++, cur_roru_plan++)
{
- /*
- Assume the best ROR scan is the one that has cheapest full-row-retrieval
- scan cost.
- Also accumulate index_only scan costs as we'll need them to calculate
- overall index_intersection cost.
- */
- double cost;
- if ((*cur_child)->is_ror)
+ Opt_trace_array ota_x(param->thd->opt_trace, "analyzing ROR scans");
+ ota_x.add("this is where to continue with ror");
+ for (ptree= imerge->trees, cur_child= range_scans;
+ ptree != imerge->trees_next;
+ ptree++, cur_child++, cur_roru_plan++)
{
- /* Ok, we have index_only cost, now get full rows scan cost */
- cost= param->table->file->
- read_time(param->real_keynr[(*cur_child)->key_idx], 1,
- (*cur_child)->records) +
- rows2double((*cur_child)->records) / TIME_FOR_COMPARE;
- }
- else
- cost= read_time;
+ Opt_trace_object inner_trace(param->thd->opt_trace);
+ (*cur_child)->trace_basic_info(param, &inner_trace);
+ /*
+ Assume the best ROR scan is the one that has cheapest full-row-retrieval
+ scan cost.
+ Also accumulate index_only scan costs as we'll need them to calculate
+ overall index_intersection cost.
+ */
+ double cost;
+ if ((*cur_child)->is_ror)
+ {
+ /* Ok, we have index_only cost, now get full rows scan cost */
+ cost= param->table->file->
+ read_time(param->real_keynr[(*cur_child)->key_idx], 1,
+ (*cur_child)->records) +
+ rows2double((*cur_child)->records) / TIME_FOR_COMPARE;
+ }
+ else
+ cost= read_time;
- TABLE_READ_PLAN *prev_plan= *cur_child;
- if (!(*cur_roru_plan= get_best_ror_intersect(param, *ptree, cost,
- &dummy)))
- {
- if (prev_plan->is_ror)
- *cur_roru_plan= prev_plan;
+ TABLE_READ_PLAN *prev_plan= *cur_child;
+ if (!(*cur_roru_plan= get_best_ror_intersect(param, *ptree, cost,
+ &dummy)))
+ {
+ if (prev_plan->is_ror)
+ *cur_roru_plan= prev_plan;
+ else
+ DBUG_RETURN(imerge_trp);
+ roru_index_costs += (*cur_roru_plan)->read_cost;
+ }
else
- DBUG_RETURN(imerge_trp);
- roru_index_costs += (*cur_roru_plan)->read_cost;
+ roru_index_costs +=
+ ((TRP_ROR_INTERSECT*)(*cur_roru_plan))->index_scan_costs;
+ roru_total_records += (*cur_roru_plan)->records;
+ roru_intersect_part *= (*cur_roru_plan)->records /
+ param->table->file->stats.records;
}
- else
- roru_index_costs +=
- ((TRP_ROR_INTERSECT*)(*cur_roru_plan))->index_scan_costs;
- roru_total_records += (*cur_roru_plan)->records;
- roru_intersect_part *= (*cur_roru_plan)->records /
- param->table->file->stats.records;
}
/*
@@ -4050,6 +4185,8 @@ skip_to_ror_scan:
sweep_cost.total_cost();
}
+ o.add(roru_total_cost, "ROR-union cost").
+ add((ulonglong)n_child_scans, "members");
DBUG_PRINT("info", ("ROR-union: cost %g, %d members", roru_total_cost,
n_child_scans));
TRP_ROR_UNION* roru;
@@ -4068,30 +4205,6 @@ skip_to_ror_scan:
}
-typedef struct st_ror_scan_info
-{
- uint idx; /* # of used key in param->keys */
- uint keynr; /* # of used key in table */
- ha_rows records; /* estimate of # records this scan will return */
-
- /* Set of intervals over key fields that will be used for row retrieval. */
- SEL_ARG *sel_arg;
-
- /* Fields used in the query and covered by this ROR scan. */
- MY_BITMAP covered_fields;
- uint used_fields_covered; /* # of set bits in covered_fields */
- int key_rec_length; /* length of key record (including rowid) */
-
- /*
- Cost of reading all index records with values in sel_arg intervals set
- (assuming there is no need to access full table records)
- */
- double index_read_cost;
- uint first_uncovered_field; /* first unused bit in covered_fields */
- uint key_components; /* # of parts in the key */
-} ROR_SCAN_INFO;
-
-
/*
Create ROR_SCAN_INFO* structure with a single ROR scan on index idx using
sel_arg set of intervals.
@@ -4612,9 +4725,19 @@ TRP_ROR_INTERSECT *get_best_ror_intersec
double min_cost= DBL_MAX;
DBUG_ENTER("get_best_ror_intersect");
+ Opt_trace_object oto1(param->thd->opt_trace, "analyzing ror intersect");
+
if ((tree->n_ror_scans < 2) || !param->table->file->stats.records ||
!param->thd->optimizer_switch_flag(OPTIMIZER_SWITCH_INDEX_MERGE_INTERSECT))
+ {
+ if (tree->n_ror_scans >= 2)
+ oto1.add(false,"usable").add("not sure","cause");
+ else
+ oto1.add(false,"usable").add("too few ror scans","cause");
DBUG_RETURN(NULL);
+ }
+
+ oto1.add(true,"need more on ror here");
/*
Step1: Collect ROR-able SEL_ARGs and create ROR_SCAN_INFO for each of
@@ -4737,6 +4860,7 @@ TRP_ROR_INTERSECT *get_best_ror_intersec
{
if (!(trp= new (param->mem_root) TRP_ROR_INTERSECT))
DBUG_RETURN(trp);
+ Opt_trace_object(param->thd->opt_trace, "set first_scan 1");
if (!(trp->first_scan=
(ROR_SCAN_INFO**)alloc_root(param->mem_root,
sizeof(ROR_SCAN_INFO*)*best_num)))
@@ -4897,6 +5021,7 @@ TRP_ROR_INTERSECT *get_best_covering_ror
if (!(trp= new (param->mem_root) TRP_ROR_INTERSECT))
DBUG_RETURN(trp);
uint best_num= (ror_scan_mark - tree->ror_scans);
+ Opt_trace_object(param->thd->opt_trace, "set first_scan 2");
if (!(trp->first_scan= (ROR_SCAN_INFO**)alloc_root(param->mem_root,
sizeof(ROR_SCAN_INFO*)*
best_num)))
@@ -4962,9 +5087,8 @@ static TRP_RANGE *get_key_scans_params(P
*/
DBUG_EXECUTE("info", print_sel_tree(param, tree, &tree->keys_map,
"tree scans"););
-#ifndef NO_OPT_TRACE_FOR_RANGE_OPT
- Opt_trace_object oto1(param->thd->opt_trace, "index_scans_parameters");
-#endif
+ Opt_trace_array oto1(param->thd->opt_trace, "analyzing range scan");
+ oto1.add("need >1 key test");
tree->ror_scans_map.clear_all();
tree->n_ror_scans= 0;
@@ -4984,42 +5108,34 @@ static TRP_RANGE *get_key_scans_params(P
bool read_index_only= index_read_must_be_used ? TRUE :
(bool) param->table->covering_keys.is_set(keynr);
-#ifndef NO_OPT_TRACE_FOR_RANGE_OPT
- Opt_trace_object oto2(param->thd->opt_trace, "range_scan");
+ Opt_trace_object oto2(param->thd->opt_trace);
oto2.add(param->table->key_info[keynr].name, "index");
-#endif
found_records= check_quick_select(param, idx, read_index_only, *key,
update_tbl_stats, &mrr_flags,
&buf_size, &cost);
-#ifndef NO_OPT_TRACE_FOR_RANGE_OPT
oto2.add(read_index_only, "index_only").add(found_records, "records");
-#endif
if ((found_records != HA_POS_ERROR) && param->is_ror_scan)
{
-#ifndef NO_OPT_TRACE_FOR_RANGE_OPT
oto2.add(true, "rowid_ordered");
-#endif
tree->n_ror_scans++;
tree->ror_scans_map.set_bit(idx);
}
-#ifndef NO_OPT_TRACE_FOR_RANGE_OPT
else
oto2.add(false, "rowid_ordered");
-#endif
if (found_records != HA_POS_ERROR &&
read_time > (found_read_time= cost.total_cost()))
{
-#ifndef NO_OPT_TRACE_FOR_RANGE_OPT
oto2.add(found_read_time, "cost").add(true, "chosen");
-#endif
read_time= found_read_time;
best_records= found_records;
key_to_read= key;
best_mrr_flags= mrr_flags;
best_buf_size= buf_size;
}
+ else
+ oto2.add(false, "chosen");
}
}
@@ -5035,14 +5151,15 @@ static TRP_RANGE *get_key_scans_params(P
read_plan->is_ror= tree->ror_scans_map.is_set(idx);
read_plan->read_cost= read_time;
read_plan->mrr_buf_size= best_buf_size;
-#ifndef NO_OPT_TRACE_FOR_RANGE_OPT
- oto1.add(param->table->key_info[param->real_keynr[idx]].name,
- "index_for_best_range_access");
-#endif
//DBUG_PRINT("info",
// ("Returning range plan for key %s, cost %g, records %lu",
// param->table->key_info[param->real_keynr[idx]].name,
// read_plan->read_cost, (ulong) read_plan->records));
+ Opt_trace_object oto2(param->thd->opt_trace);
+ oto2.add(param->table->key_info[param->real_keynr[idx]].name,
+ "index_for_best_range_access").
+ add("need test for no key to read","todo").
+ add("remove this","todo2");
}
}
else
@@ -5098,10 +5215,12 @@ QUICK_SELECT_I *TRP_ROR_INTERSECT::make_
"creating ROR-intersect",
first_scan, last_scan););
alloc= parent_alloc? parent_alloc: &quick_intrsect->alloc;
- for (; first_scan != last_scan;++first_scan)
+ for (st_ror_scan_info **current= first_scan;
+ current != last_scan;
+ current++)
{
- if (!(quick= get_quick_select(param, (*first_scan)->idx,
- (*first_scan)->sel_arg,
+ if (!(quick= get_quick_select(param, (*current)->idx,
+ (*current)->sel_arg,
HA_MRR_USE_DEFAULT_IMPL | HA_MRR_SORTED,
0, alloc)) ||
quick_intrsect->push_quick_back(quick))
@@ -9683,18 +9802,32 @@ get_best_group_min_max(PARAM *param, SEL
DBUG_ENTER("get_best_group_min_max");
+ Opt_trace_object oto(thd->opt_trace, "group min max select");
+
/* Perform few 'cheap' tests whether this access method is applicable. */
if (!join)
+ {
+ oto.add("false","chosen").add("no join","cause");
DBUG_RETURN(NULL); /* This is not a select statement. */
+ }
if ((join->tables != 1) || /* The query must reference one table. */
(join->select_lex->olap == ROLLUP_TYPE)) /* Check (B3) for ROLLUP */
+ {
+ oto.add("false","chosen").add("rollup or >1 table","cause");
DBUG_RETURN(NULL);
+ }
if (table->s->keys == 0) /* There are no indexes to use. */
+ {
+ oto.add("false","chosen").add("no indexes to use","cause");
DBUG_RETURN(NULL);
+ }
/* Check (SA1,SA4) and store the only MIN/MAX argument - the C attribute.*/
if (join->make_sum_func_list(join->all_fields, join->fields_list, 1))
+ {
+ oto.add("false","chosen").add("don't know yet","cause");
DBUG_RETURN(NULL);
+ }
List_iterator<Item> select_items_it(join->fields_list);
is_agg_distinct = is_indexed_agg_distinct(join, &agg_distinct_flds);
@@ -9702,13 +9835,18 @@ get_best_group_min_max(PARAM *param, SEL
if ((!join->group_list) && /* Neither GROUP BY nor a DISTINCT query. */
(!join->select_distinct) &&
!is_agg_distinct)
+ {
+ oto.add("false","chosen").add("query not GROUP BY or DISTINCT","cause");
DBUG_RETURN(NULL);
+ }
+
/* Analyze the query in more detail. */
if (join->sum_funcs[0])
{
Item_sum *min_max_item;
Item_sum **func_ptr= join->sum_funcs;
+
while ((min_max_item= *(func_ptr++)))
{
if (min_max_item->sum_func() == Item_sum::MIN_FUNC)
@@ -9720,7 +9858,10 @@ get_best_group_min_max(PARAM *param, SEL
min_max_item->sum_func() == Item_sum::AVG_DISTINCT_FUNC)
continue;
else
+ {
+ oto.add("false","chosen").add("not MIN/MAX or COUNT/SUM/AVG DISTINCT","cause");
DBUG_RETURN(NULL);
+ }
/* The argument of MIN/MAX. */
Item *expr= min_max_item->get_arg(0)->real_item();
@@ -9738,6 +9879,7 @@ get_best_group_min_max(PARAM *param, SEL
/* Check (SA5). */
if (join->select_distinct)
{
+ oto.add("true","select distinct").add("true","todo");
while ((item= select_items_it++))
{
if (item->real_item()->type() != Item::FIELD_ITEM)
@@ -9749,7 +9891,10 @@ get_best_group_min_max(PARAM *param, SEL
for (tmp_group= join->group_list; tmp_group; tmp_group= tmp_group->next)
{
if ((*tmp_group->item)->real_item()->type() != Item::FIELD_ITEM)
+ {
+ oto.add("false","chosen").add("group field is expression","cause");
DBUG_RETURN(NULL);
+ }
}
/*
@@ -9771,9 +9916,12 @@ get_best_group_min_max(PARAM *param, SEL
ha_rows cur_quick_prefix_records= 0;
uint cur_param_idx=MAX_KEY;
+ Opt_trace_array ota(thd->opt_trace, "looking for covering keys");
for (uint cur_index= 0 ; cur_index_info != cur_index_info_end ;
cur_index_info++, cur_index++)
{
+ Opt_trace_object o(thd->opt_trace);
+ o.add(table->key_info[cur_index].name, "index");
KEY_PART_INFO *cur_part;
KEY_PART_INFO *end_part; /* Last part for loops. */
/* Last index part. */
@@ -9793,7 +9941,10 @@ get_best_group_min_max(PARAM *param, SEL
/* Check (B1) - if current index is covering. */
if (!table->covering_keys.is_set(cur_index))
+ {
+ o.add("false","usable").add("not covering key", "cause");
goto next_index;
+ }
/*
If the current storage manager is such that it appends the primary key to
@@ -9817,9 +9968,13 @@ get_best_group_min_max(PARAM *param, SEL
*/
if (bitmap_is_set(table->read_set, cur_field->field_index) &&
!cur_field->part_of_key_not_clustered.is_set(cur_index))
+ {
+ o.add("false","usable").add("not covering", "cause");
goto next_index; // Field was not part of key
+ }
}
}
+ o.add("true", "covering");
/*
Check (GA1) for GROUP BY queries.
@@ -9848,9 +10003,13 @@ get_best_group_min_max(PARAM *param, SEL
used_key_parts_map.set_bit(max_key_part);
}
else
+ {
+ o.add("false","usable").add("don't know", "cause");
goto next_index;
+ }
}
}
+
/*
Check (GA2) if this is a DISTINCT query.
If GA2, then Store a new ORDER object in group_fields_array at the
@@ -9914,10 +10073,14 @@ get_best_group_min_max(PARAM *param, SEL
{
key_part_nr= get_field_keypart(cur_index_info, min_max_arg_item->field);
if (key_part_nr <= cur_group_key_parts)
+ {
+ o.add("false","usable").add("todo SA2", "cause");
goto next_index;
+ }
min_max_arg_part= cur_index_info->key_part + key_part_nr - 1;
}
+ o.add("need more here","CONTINUE HERE");
/*
Check (NGA1, NGA2) and extract a sequence of constants to be used as part
of all search keys.
=== modified file 'sql/opt_range.h'
--- a/sql/opt_range.h 2010-08-05 13:51:44 +0000
+++ b/sql/opt_range.h 2010-09-17 13:08:12 +0000
@@ -33,6 +33,7 @@
*/
#include "sql_class.h" // set_var.h: THD
#include "set_var.h" /* Item */
+#include "opt_trace.h"
class JOIN;
class Item_sum;
@@ -302,6 +303,8 @@ public:
/* Get type of this quick select - one of the QS_TYPE_* values */
virtual int get_type() = 0;
+ virtual const char* get_type_name() { DBUG_ASSERT(false); }
+
/*
Initialize this quick select as a merged scan inside a ROR-union or a ROR-
intersection scan. The caller must not additionally call init() if this
@@ -470,6 +473,7 @@ public:
void save_last_pos()
{ file->position(record); }
int get_type() { return QS_TYPE_RANGE; }
+ const char* get_type_name(){ return "range"; }
void add_keys_and_lengths(String *key_names, String *used_lengths);
void add_info_string(String *str);
#if !defined(DBUG_OFF) || defined(OPTIMIZER_TRACE)
@@ -567,6 +571,7 @@ public:
bool reverse_sorted() { return false; }
bool unique_key_range() { return false; }
int get_type() { return QS_TYPE_INDEX_MERGE; }
+ const char* get_type_name(){ return "index_merge"; }
void add_keys_and_lengths(String *key_names, String *used_lengths);
void add_info_string(String *str);
bool is_keys_used(const MY_BITMAP *fields);
@@ -627,6 +632,7 @@ public:
bool reverse_sorted() { return false; }
bool unique_key_range() { return false; }
int get_type() { return QS_TYPE_ROR_INTERSECT; }
+ const char* get_type_name(){ return "ror_intersect"; }
void add_keys_and_lengths(String *key_names, String *used_lengths);
void add_info_string(String *str);
bool is_keys_used(const MY_BITMAP *fields);
@@ -682,6 +688,7 @@ public:
bool reverse_sorted() { return false; }
bool unique_key_range() { return false; }
int get_type() { return QS_TYPE_ROR_UNION; }
+ const char* get_type_name(){ return "ror_union"; }
void add_keys_and_lengths(String *key_names, String *used_lengths);
void add_info_string(String *str);
bool is_keys_used(const MY_BITMAP *fields);
@@ -808,6 +815,7 @@ public:
bool reverse_sorted() { return false; }
bool unique_key_range() { return false; }
int get_type() { return QS_TYPE_GROUP_MIN_MAX; }
+ const char* get_type_name(){ return "group_min_max"; }
void add_keys_and_lengths(String *key_names, String *used_lengths);
#if !defined(DBUG_OFF) || defined(OPTIMIZER_TRACE)
void dbug_dump(int indent, bool verbose);
@@ -829,6 +837,7 @@ public:
int get_next();
bool reverse_sorted() { return 1; }
int get_type() { return QS_TYPE_RANGE_DESC; }
+ const char* get_type_name(){ return "range_desc"; }
QUICK_SELECT_I *make_reverse(uint used_key_parts_arg)
{
return this; // is already reverse sorted
@@ -863,6 +872,7 @@ class SQL_SELECT :public Sql_alloc {
bool check_quick(THD *thd, bool force_quick_range, ha_rows limit)
{
key_map tmp(key_map::ALL_BITS);
+ Opt_trace_object oto(thd->opt_trace);
return test_quick_select(thd, tmp, 0, limit, force_quick_range, FALSE) < 0;
}
inline bool skip_record(THD *thd, bool *skip_record)
@@ -886,6 +896,7 @@ public:
int reset() { return 0; }
int get_next() { return file->ft_read(record); }
int get_type() { return QS_TYPE_FULLTEXT; }
+ const char* get_type_name(){ return "fulltext"; }
};
FT_SELECT *get_ft_select(THD *thd, TABLE *table, uint key);
=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc 2010-09-05 16:08:07 +0000
+++ b/sql/sql_select.cc 2010-09-17 13:08:12 +0000
@@ -259,6 +259,10 @@ void select_describe(JOIN *join, bool ne
bool distinct, const char *message=NullS);
static Item *remove_additional_cond(Item* conds);
static void add_group_and_distinct_keys(JOIN *join, JOIN_TAB *join_tab);
+static void trace_indices_added_group_distinct(THD *thd,
+ JOIN_TAB *join_tab,
+ const key_map new_keys,
+ char* cause);
static bool replace_subcondition(JOIN *join, Item **tree,
Item *old_cond, Item *new_cond,
bool do_fix_fields);
@@ -4932,14 +4936,27 @@ make_join_statistics(JOIN *join, TABLE_L
for (s= stat ; s < stat_end ; s++)
{
+ Opt_trace_object oto1(join->thd->opt_trace);
+ oto1.add(s->table->alias, "table");
if (s->type == JT_SYSTEM || s->type == JT_CONST)
{
+ oto1.add("1","found_records").
+ add("1","records").
+ add("1","cost").
+ add("1.0","worst_seeks");
+
+ if (s->ref.key_parts)
+ oto1.add((s->table->key_info+ s->ref.key)->name,"index");
+
+ if (s->type == JT_SYSTEM)
+ oto1.add("SYSTEM table","cause");
+ else
+ oto1.add("CONST table","cause");
+
/* Only one matching row */
s->found_records= s->records= s->read_time=1; s->worst_seeks= 1.0;
continue;
}
- Opt_trace_object oto1(join->thd->opt_trace);
- oto1.add(s->table->alias, "table");
/* Approximate found rows and time to read them */
s->found_records= s->records= s->table->file->stats.records;
s->read_time= (ha_rows) s->table->file->scan_time();
@@ -4978,10 +4995,6 @@ make_join_statistics(JOIN *join, TABLE_L
1, &error);
if (!select)
goto error;
-#ifdef NO_OPT_TRACE_FOR_RANGE_OPT
- Opt_trace_object(join->thd->opt_trace, "more_range_optimizer_trace").
- add("yes!", "TODO?");
-#endif
records= get_quick_record_count(join->thd, select, s->table,
&s->const_keys, join->row_limit);
s->quick= select->quick;
@@ -5002,9 +5015,18 @@ make_join_statistics(JOIN *join, TABLE_L
{
/* Generate empty row */
s->info= "Impossible ON condition";
+ oto1.add("returning empty NULL row","info").
+ add("Impossible ON condition","cause").
+ add("YAY","found test hit1");
found_const_table_map|= s->table->map;
s->type= JT_CONST;
mark_as_null_row(s->table); // All fields are NULL
+ }
+ else
+ {
+ oto1.add("0","records").
+ add("Impossible WHERE condition","cause").
+ add("YAY","found test hit2");
}
}
if (records != HA_POS_ERROR)
@@ -6414,11 +6436,14 @@ add_group_and_distinct_keys(JOIN *join,
Item_field *cur_item;
key_map possible_keys;
+ char* cause;
+
if (join->group_list)
{ /* Collect all query fields referenced in the GROUP clause. */
for (cur_group= join->group_list; cur_group; cur_group= cur_group->next)
(*cur_group->item)->walk(&Item::collect_item_field_processor, 0,
(uchar*) &indexed_fields);
+ cause= (char*)"group by\0";
}
else if (join->select_distinct)
{ /* Collect all query fields referenced in the SELECT clause. */
@@ -6428,10 +6453,12 @@ add_group_and_distinct_keys(JOIN *join,
while ((item= select_items_it++))
item->walk(&Item::collect_item_field_processor, 0,
(uchar*) &indexed_fields);
+ cause= (char*)"distinct\0";
}
else if (is_indexed_agg_distinct(join, &indexed_fields))
{
join->sort_and_group= 1;
+ cause= (char*) "indexed distinct aggregate";
}
else
return;
@@ -6448,7 +6475,30 @@ add_group_and_distinct_keys(JOIN *join,
}
if (!possible_keys.is_clear_all())
+ {
+ trace_indices_added_group_distinct(join->thd, join_tab,
+ possible_keys, cause);
join_tab->const_keys.merge(possible_keys);
+ }
+}
+
+static void trace_indices_added_group_distinct(THD *thd,
+ JOIN_TAB *join_tab,
+ const key_map new_keys,
+ char* cause)
+{
+ KEY *key_info= join_tab->table->key_info;
+ key_map existing_keys= join_tab->const_keys;
+ uint nokeys= join_tab->table->s->keys;
+
+ Opt_trace_object oto1(thd->opt_trace, "const_keys added");
+ {
+ Opt_trace_array ota1(thd->opt_trace,"keys");
+ for (uint j=0 ; j < nokeys ; j++)
+ if (new_keys.is_set(j) && !existing_keys.is_set(j))
+ ota1.add(key_info[j].name);
+ }
+ oto1.add(cause,"cause");
}
@@ -7247,7 +7297,7 @@ best_access_path(JOIN *join,
}
Opt_trace_object oto1(thd->opt_trace);
- oto1.add("table scan", "access_type");
+ oto1.add("scan", "access_type");
/*
Don't test table scan if it can't be better.
Prefer key lookup if we would use the same key for scanning.
@@ -7331,6 +7381,7 @@ best_access_path(JOIN *join,
if (s->quick)
{
+ oto1.add(s->quick->get_type_name(),"using quick");
/*
For each record we:
- read record range through 'quick'
@@ -7367,6 +7418,7 @@ best_access_path(JOIN *join,
}
else
{
+ oto1.add("yay","add something about join buffer");
/*
We read the table as many times as join buffer becomes full.
It would be more exact to round the result of the division with
@@ -7429,7 +7481,10 @@ skip_table_scan:
idx == join->const_tables &&
s->table == join->sort_by_table &&
join->unit->select_limit_cnt >= records)
+ {
+ oto1.add(true, "use temp table");
join->sort_by_table= (TABLE*) 1; // Must use temporary table
+ }
DBUG_VOID_RETURN;
}
@@ -9740,6 +9795,8 @@ static bool make_join_select(JOIN *join,
if (sel->cond && !sel->cond->fixed)
sel->cond->quick_fix_field();
+ Opt_trace_object oto(join->thd->opt_trace); //temporary fix
+ oto.add(true,"need some description here");
if (sel->test_quick_select(thd, tab->keys,
used_tables & ~ current_map,
(join->select_options &
@@ -9753,15 +9810,19 @@ static bool make_join_select(JOIN *join,
we have to check isn't it only "impossible ON" instead
*/
sel->cond=orig_cond;
- if (!*tab->on_expr_ref ||
- sel->test_quick_select(thd, tab->keys,
+ if (!*tab->on_expr_ref)
+ DBUG_RETURN(1); // Impossible WHERE
+ else
+ {
+ if (sel->test_quick_select(thd, tab->keys,
used_tables & ~ current_map,
(join->select_options &
OPTION_FOUND_ROWS ?
HA_POS_ERROR :
join->unit->select_limit_cnt),0,
- FALSE) < 0)
- DBUG_RETURN(1); // Impossible WHERE
+ FALSE) < 0)
+ DBUG_RETURN(1); // Impossible WHERE
+ }
}
else
sel->cond=orig_cond;
@@ -18253,6 +18314,8 @@ test_if_quick_select(JOIN_TAB *tab)
{
delete tab->select->quick;
tab->select->quick=0;
+ Opt_trace_object oto(tab->join->thd->opt_trace); //temporary fix
+ oto.add(true,"need some description here2");
return tab->select->test_quick_select(tab->join->thd, tab->keys,
(table_map) 0, HA_POS_ERROR, 0,
FALSE);
Attachment: [text/bzr-bundle] bzr/jorgen.loland@oracle.com-20100917130812-1betv09fdpv1lan0.bundle
