summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/r/analyze_stmt.result14
-rw-r--r--mysql-test/r/show_explain.result2
-rw-r--r--mysql-test/t/analyze_stmt.test26
-rw-r--r--sql/protocol.h36
-rw-r--r--sql/sql_class.h14
-rw-r--r--sql/sql_explain.cc7
-rw-r--r--sql/sql_explain.h6
-rw-r--r--sql/sql_parse.cc4
-rw-r--r--sql/sql_select.cc830
-rw-r--r--sql/sql_select.h5
-rw-r--r--sql/sql_show.cc198
-rw-r--r--sql/sql_show.h35
-rw-r--r--sql/table.h16
13 files changed, 738 insertions, 455 deletions
diff --git a/mysql-test/r/analyze_stmt.result b/mysql-test/r/analyze_stmt.result
index f388450760a..0e62654f256 100644
--- a/mysql-test/r/analyze_stmt.result
+++ b/mysql-test/r/analyze_stmt.result
@@ -61,3 +61,17 @@ id select_type table type possible_keys key key_len ref rows r_rows filtered r_f
NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL 5 NULL NULL
drop table t1;
drop table t0;
+#
+# Try a subquery.
+#
+create table t0 (a int, b int);
+insert into t0 values
+(0,0),(1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9);
+create table t1 (a int, b int);
+insert into t1 values (1,1),(2,2),(3,3);
+# See .test file for the right values of r_rows and r_filtered.
+analyze select a, a in (select t0.b from t0 where t0.b+1=t1.b+1) from t1;
+id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 3 3 100.00 100.00
+2 DEPENDENT SUBQUERY t0 ALL NULL NULL NULL NULL 10 3 100.00 33.33 Using where
+drop table t0,t1;
diff --git a/mysql-test/r/show_explain.result b/mysql-test/r/show_explain.result
index a4d12ce05ce..3695384bac4 100644
--- a/mysql-test/r/show_explain.result
+++ b/mysql-test/r/show_explain.result
@@ -641,7 +641,7 @@ set debug_dbug='+d,show_explain_probe_join_exec_start';
SHOW INDEX FROM t1;
show explain for $thr2;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE STATISTICS ALL NULL NULL NULL NULL NULL Skip_open_table; Scanned all databases
+1 SIMPLE STATISTICS ALL NULL TABLE_SCHEMA,TABLE_NAME NULL NULL NULL Open_full_table; Scanned 0 databases
Warnings:
Note 1003 SHOW INDEX FROM t1
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment
diff --git a/mysql-test/t/analyze_stmt.test b/mysql-test/t/analyze_stmt.test
index 135163ca128..5169b342cde 100644
--- a/mysql-test/t/analyze_stmt.test
+++ b/mysql-test/t/analyze_stmt.test
@@ -34,5 +34,29 @@ insert into t1 select a,a from t0;
analyze (select * from t1 A where a<5) union (select * from t1 B where a in (5,6));
analyze (select * from t1 A where a<5) union (select * from t1 B where a in (1,2));
drop table t1;
-
drop table t0;
+
+--echo #
+--echo # Try a subquery.
+--echo #
+create table t0 (a int, b int);
+insert into t0 values
+ (0,0),(1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9);
+
+create table t1 (a int, b int);
+insert into t1 values (1,1),(2,2),(3,3);
+
+#
+# t1 t0
+# a=1 (0,1) 2 rows
+# a=2 (0,1,2) 3 rows
+# a=3 (0,1,2,3) 4 rows
+#
+# TOTAL TOTAL= 9 rows. 3 executions, avg=3 rows.
+# WHERE is satisfied for 1 row per query, which gives filtered=33.3
+
+--echo # See .test file for the right values of r_rows and r_filtered.
+analyze select a, a in (select t0.b from t0 where t0.b+1=t1.b+1) from t1;
+
+drop table t0,t1;
+
diff --git a/sql/protocol.h b/sql/protocol.h
index c58de68289f..5129f68d706 100644
--- a/sql/protocol.h
+++ b/sql/protocol.h
@@ -210,6 +210,42 @@ public:
virtual enum enum_protocol_type type() { return PROTOCOL_BINARY; };
};
+
+/*
+ A helper for "ANALYZE $stmt" which looks a real network procotol but doesn't
+ write results to the network.
+
+ At first glance, class select_send looks like a more appropriate place to
+ implement the "write nothing" hook. This is not true, because
+ - we need to evaluate the value of every item, and do it the way
+ select_send does it (i.e. call item->val_int() or val_real() or...)
+ - select_send::send_data() has some other code, like telling the storage
+ engine that the row can be unlocked. We want to keep that also.
+ as a result, "ANALYZE $stmt" uses a select_send_analyze which still uses
+ select_send::send_data() & co., and also uses Protocol_discard object.
+*/
+
+class Protocol_discard : public Protocol_text
+{
+public:
+ Protocol_discard(THD *thd_arg) : Protocol_text(thd_arg) {}
+ /* The real writing is done only in write() */
+ virtual bool write() { return 0; }
+ virtual bool send_result_set_metadata(List<Item> *list, uint flags)
+ {
+ // Don't pas Protocol::SEND_NUM_ROWS | Protocol::SEND_EOF flags
+ return Protocol_text::send_result_set_metadata(list, 0);
+ }
+
+ // send_error is intentionally not overloaded.
+ virtual bool send_eof(uint server_status, uint statement_warn_count)
+ {
+ return 0;
+ }
+
+};
+
+
void send_warning(THD *thd, uint sql_errno, const char *err=0);
bool net_send_error(THD *thd, uint sql_errno, const char *err,
const char* sqlstate);
diff --git a/sql/sql_class.h b/sql/sql_class.h
index a7991952a79..65b9b2bdbec 100644
--- a/sql/sql_class.h
+++ b/sql/sql_class.h
@@ -3959,19 +3959,21 @@ public:
virtual void cleanup();
};
+
+/*
+ We need this class, because select_send::send_eof() will call ::my_eof.
+
+ See also class Protocol_discard.
+*/
+
class select_send_analyze : public select_send
{
-bool discard_data;
bool send_result_set_metadata(List<Item> &list, uint flags) { return 0; }
- /*
- ANALYZE-todo: we should call val_int() (or val_str() or whatever) to
- compute the columns. If we don't, it's not full execution.
- */
- int send_data(List<Item> &items) { return 0; }
bool send_eof() { return 0; }
void abort_result_set() {}
};
+
class select_to_file :public select_result_interceptor {
protected:
sql_exchange *exchange;
diff --git a/sql/sql_explain.cc b/sql/sql_explain.cc
index dcec94927b4..58029ba7907 100644
--- a/sql/sql_explain.cc
+++ b/sql/sql_explain.cc
@@ -345,6 +345,13 @@ int Explain_node::print_explain_for_children(Explain_query *query,
}
+void Explain_select::replace_table(uint idx, Explain_table_access *new_tab)
+{
+ delete join_tabs[idx];
+ join_tabs[idx]= new_tab;
+}
+
+
Explain_select::~Explain_select()
{
if (join_tabs)
diff --git a/sql/sql_explain.h b/sql/sql_explain.h
index 203235f9f5c..417cfc0fffc 100644
--- a/sql/sql_explain.h
+++ b/sql/sql_explain.h
@@ -129,6 +129,12 @@ public:
join_tabs[n_join_tabs++]= tab;
return false;
}
+
+ /*
+ This is used to save the results of "late" test_if_skip_sort_order() calls
+ that are made from JOIN::exec
+ */
+ void replace_table(uint idx, Explain_table_access *new_tab);
public:
int select_id;
diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc
index 42a3045cb1d..721bdbdc031 100644
--- a/sql/sql_parse.cc
+++ b/sql/sql_parse.cc
@@ -5260,10 +5260,13 @@ static bool execute_sqlcom_select(THD *thd, TABLE_LIST *all_tables)
{
//psergey-todo: ANALYZE should hook in here...
select_result *save_result;
+ Protocol *save_protocol;
if (lex->analyze_stmt)
{
save_result= result;
result= new select_send_analyze();
+ save_protocol= thd->protocol;
+ thd->protocol= new Protocol_discard(thd);
}
else
{
@@ -5280,6 +5283,7 @@ static bool execute_sqlcom_select(THD *thd, TABLE_LIST *all_tables)
result= save_result;
if (!result && !(result= new select_send()))
return 1;
+ thd->protocol= save_protocol;
thd->lex->explain->send_explain(thd);
if (result != lex->result)
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 6090eb31487..46d42a409c4 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -1873,6 +1873,9 @@ TODO: make view to decide if it is possible to write to WHERE directly or make S
}
}
+ if ((select_lex->options & OPTION_SCHEMA_TABLE))
+ optimize_schema_tables_reads(this);
+
tmp_having= having;
if (select_options & SELECT_DESCRIBE)
{
@@ -1919,6 +1922,7 @@ setup_subq_exit:
error= 0;
derived_exit:
+
select_lex->mark_const_derived(zero_result_cause);
DBUG_RETURN(0);
}
@@ -2059,6 +2063,7 @@ int JOIN::init_execution()
&join_tab[const_tables].table->
keys_in_use_for_order_by))
order=0;
+ join_tab[const_tables].update_explain_data(const_tables);
}
}
@@ -2383,10 +2388,12 @@ void JOIN::exec()
if (!exec_saved_explain)
{
+#if 0
save_explain_data(thd->lex->explain, true /* can overwrite */,
need_tmp,
order != 0 && !skip_sort_order,
select_distinct);
+#endif
exec_saved_explain= true;
}
@@ -2564,15 +2571,19 @@ void JOIN::exec_inner()
simple_order= simple_group;
skip_sort_order= 0;
}
+ bool made_call= false;
if (order &&
(order != group_list || !(select_options & SELECT_BIG_RESULT)) &&
(const_tables == table_count ||
((simple_order || skip_sort_order) &&
- test_if_skip_sort_order(&join_tab[const_tables], order,
+ (made_call=true) &&
+ test_if_skip_sort_order(&join_tab[const_tables], order,
select_limit, 0,
&join_tab[const_tables].table->
keys_in_use_for_query))))
order=0;
+ if (made_call)
+ join_tab[const_tables].update_explain_data(const_tables);
having= tmp_having;
select_describe(this, need_tmp,
order != 0 && !skip_sort_order,
@@ -20535,7 +20546,12 @@ create_sort_index(THD *thd, JOIN *join, ORDER *order,
test_if_skip_sort_order(tab,order,select_limit,0,
is_order_by ? &table->keys_in_use_for_order_by :
&table->keys_in_use_for_group_by))
+ {
+ tab->update_explain_data(join->const_tables);
DBUG_RETURN(0);
+ }
+ tab->update_explain_data(join->const_tables);
+
for (ORDER *ord= join->order; ord; ord= ord->next)
length++;
if (!(join->sortorder=
@@ -23248,6 +23264,416 @@ void append_possible_keys(String *str, TABLE *table, key_map possible_keys)
}
}
+// TODO: this function is only applicable for the first non-const optimization
+// join tab.
+void JOIN_TAB::update_explain_data(uint idx)
+{
+ if (this == first_breadth_first_tab(join, WALK_OPTIMIZATION_TABS) &&
+ join->select_lex->select_number != INT_MAX &&
+ join->select_lex->select_number != UINT_MAX)
+ {
+ Explain_table_access *eta= new Explain_table_access();
+ JOIN_TAB* const first_top_tab= first_breadth_first_tab(join, WALK_OPTIMIZATION_TABS);
+ save_explain_data(eta, join->const_table_map, join->select_distinct, first_top_tab);
+
+ Explain_select *sel= join->thd->lex->explain->get_select(join->select_lex->select_number);
+ sel->replace_table(idx, eta);
+ }
+}
+
+
+void JOIN_TAB::save_explain_data(Explain_table_access *eta, table_map prefix_tables,
+ bool distinct, JOIN_TAB *first_top_tab)
+{
+ int quick_type;
+ const CHARSET_INFO *cs= system_charset_info;
+
+ JOIN_TAB *tab= this;
+ THD *thd=join->thd;
+
+ TABLE *table=tab->table;
+ TABLE_LIST *table_list= tab->table->pos_in_table_list;
+ char buff4[512];
+ my_bool key_read;
+ char table_name_buffer[SAFE_NAME_LEN];
+ String tmp4(buff4,sizeof(buff4),cs);
+ KEY *key_info= 0;
+ uint key_len= 0;
+ tmp4.length(0);
+ quick_type= -1;
+ QUICK_SELECT_I *quick= NULL;
+
+ eta->key.set(thd->mem_root, NULL, (uint)-1);
+ eta->quick_info= NULL;
+
+ tab->tracker= &eta->tracker;
+
+ /* id */
+ if (tab->bush_root_tab)
+ {
+ JOIN_TAB *first_sibling= tab->bush_root_tab->bush_children->start;
+ eta->sjm_nest_select_id= first_sibling->emb_sj_nest->sj_subq_pred->get_identifier();
+ }
+ else
+ eta->sjm_nest_select_id= 0;
+
+ /* select_type is kept in Explain_select */
+
+ /* table */
+ if (table->derived_select_number)
+ {
+ /* Derived table name generation */
+ int len= my_snprintf(table_name_buffer, sizeof(table_name_buffer)-1,
+ "<derived%u>",
+ table->derived_select_number);
+ eta->table_name.copy(table_name_buffer, len, cs);
+ }
+ else if (tab->bush_children)
+ {
+ JOIN_TAB *ctab= tab->bush_children->start;
+ /* table */
+ int len= my_snprintf(table_name_buffer,
+ sizeof(table_name_buffer)-1,
+ "<subquery%d>",
+ ctab->emb_sj_nest->sj_subq_pred->get_identifier());
+ eta->table_name.copy(table_name_buffer, len, cs);
+ }
+ else
+ {
+ TABLE_LIST *real_table= table->pos_in_table_list;
+ eta->table_name.copy(real_table->alias, strlen(real_table->alias), cs);
+ }
+
+ /* "partitions" column */
+ {
+#ifdef WITH_PARTITION_STORAGE_ENGINE
+ partition_info *part_info;
+ if (!table->derived_select_number &&
+ (part_info= table->part_info))
+ {
+ make_used_partitions_str(part_info, &eta->used_partitions);
+ eta->used_partitions_set= true;
+ }
+ else
+ eta->used_partitions_set= false;
+#else
+ /* just produce empty column if partitioning is not compiled in */
+ eta->used_partitions_set= false;
+#endif
+ }
+
+ /* "type" column */
+ enum join_type tab_type= tab->type;
+ if ((tab->type == JT_ALL || tab->type == JT_HASH) &&
+ tab->select && tab->select->quick && tab->use_quick != 2)
+ {
+ quick= tab->select->quick;
+ quick_type= tab->select->quick->get_type();
+ if ((quick_type == QUICK_SELECT_I::QS_TYPE_INDEX_MERGE) ||
+ (quick_type == QUICK_SELECT_I::QS_TYPE_INDEX_INTERSECT) ||
+ (quick_type == QUICK_SELECT_I::QS_TYPE_ROR_INTERSECT) ||
+ (quick_type == QUICK_SELECT_I::QS_TYPE_ROR_UNION))
+ tab_type= tab->type == JT_ALL ? JT_INDEX_MERGE : JT_HASH_INDEX_MERGE;
+ else
+ tab_type= tab->type == JT_ALL ? JT_RANGE : JT_HASH_RANGE;
+ }
+ eta->type= tab_type;
+
+ /* Build "possible_keys" value */
+ append_possible_keys(&eta->possible_keys_str, table, tab->keys);
+
+ /* Build "key", "key_len", and "ref" */
+ if (tab_type == JT_NEXT)
+ {
+ key_info= table->key_info+tab->index;
+ key_len= key_info->key_length;
+ }
+ else if (tab->ref.key_parts)
+ {
+ key_info= tab->get_keyinfo_by_key_no(tab->ref.key);
+ key_len= tab->ref.key_length;
+ }
+
+ /*
+ In STRAIGHT_JOIN queries, there can be join tabs with JT_CONST type
+ that still have quick selects.
+ */
+ if (tab->select && tab->select->quick && tab_type != JT_CONST)
+ {
+ eta->quick_info= tab->select->quick->get_explain(thd->mem_root);
+ }
+
+ if (key_info) /* 'index' or 'ref' access */
+ {
+ eta->key.set(thd->mem_root, key_info->name, key_len);
+
+ if (tab->ref.key_parts && tab_type != JT_FT)
+ {
+ store_key **ref=tab->ref.key_copy;
+ for (uint kp= 0; kp < tab->ref.key_parts; kp++)
+ {
+ if (tmp4.length())
+ tmp4.append(',');
+
+ if ((key_part_map(1) << kp) & tab->ref.const_ref_part_map)
+ tmp4.append("const");
+ else
+ {
+ tmp4.append((*ref)->name(), strlen((*ref)->name()), cs);
+ ref++;
+ }
+ }
+ }
+ }
+
+ if (tab_type == JT_HASH_NEXT) /* full index scan + hash join */
+ {
+ eta->hash_next_key.set(thd->mem_root,
+ table->key_info[tab->index].name,
+ table->key_info[tab->index].key_length);
+ }
+
+ if (key_info)
+ {
+ if (key_info && tab_type != JT_NEXT)
+ {
+ eta->ref.copy(tmp4);
+ eta->ref_set= true;
+ }
+ else
+ eta->ref_set= false;
+ }
+ else
+ {
+ if (table_list && /* SJM bushes don't have table_list */
+ table_list->schema_table &&
+ table_list->schema_table->i_s_requested_object & OPTIMIZE_I_S_TABLE)
+ {
+ IS_table_read_plan *is_table_read_plan= table_list->is_table_read_plan;
+ const char *tmp_buff;
+ int f_idx;
+ StringBuffer<64> key_name_buf;
+ if (is_table_read_plan->has_db_lookup_value())
+ {
+ /* The "key" has the name of the column referring to the database */
+ f_idx= table_list->schema_table->idx_field1;
+ tmp_buff= table_list->schema_table->fields_info[f_idx].field_name;
+ key_name_buf.append(tmp_buff, strlen(tmp_buff), cs);
+ }
+ if (is_table_read_plan->has_table_lookup_value())
+ {
+ if (is_table_read_plan->has_db_lookup_value())
+ key_name_buf.append(',');
+
+ f_idx= table_list->schema_table->idx_field2;
+ tmp_buff= table_list->schema_table->fields_info[f_idx].field_name;
+ key_name_buf.append(tmp_buff, strlen(tmp_buff), cs);
+ }
+
+ if (key_name_buf.length())
+ eta->key.set(thd->mem_root, key_name_buf.c_ptr_safe(), -1);
+ }
+ eta->ref_set= false;
+ }
+
+ /* "rows" */
+ if (table_list /* SJM bushes don't have table_list */ &&
+ table_list->schema_table)
+ {
+ /* I_S tables have rows=extra=NULL */
+ eta->rows_set= false;
+ eta->filtered_set= false;
+ }
+ else
+ {
+ double examined_rows= tab->get_examined_rows();
+
+ eta->rows_set= true;
+ eta->rows= (ha_rows) examined_rows;
+
+ /* "filtered" */
+ float f= 0.0;
+ if (examined_rows)
+ {
+ double pushdown_cond_selectivity= tab->cond_selectivity;
+ if (pushdown_cond_selectivity == 1.0)
+ f= (float) (100.0 * tab->records_read / examined_rows);
+ else
+ f= (float) (100.0 * pushdown_cond_selectivity);
+ }
+ set_if_smaller(f, 100.0);
+ eta->filtered_set= true;
+ eta->filtered= f;
+ }
+
+ /* Build "Extra" field and save it */
+ key_read=table->key_read;
+ if ((tab_type == JT_NEXT || tab_type == JT_CONST) &&
+ table->covering_keys.is_set(tab->index))
+ key_read=1;
+ if (quick_type == QUICK_SELECT_I::QS_TYPE_ROR_INTERSECT &&
+ !((QUICK_ROR_INTERSECT_SELECT*)quick)->need_to_fetch_row)
+ key_read=1;
+
+ if (tab->info)
+ {
+ eta->push_extra(tab->info);
+ }
+ else if (tab->packed_info & TAB_INFO_HAVE_VALUE)
+ {
+ if (tab->packed_info & TAB_INFO_USING_INDEX)
+ eta->push_extra(ET_USING_INDEX);
+ if (tab->packed_info & TAB_INFO_USING_WHERE)
+ eta->push_extra(ET_USING_WHERE);
+ if (tab->packed_info & TAB_INFO_FULL_SCAN_ON_NULL)
+ eta->push_extra(ET_FULL_SCAN_ON_NULL_KEY);
+ }
+ else
+ {
+ uint keyno= MAX_KEY;
+ if (tab->ref.key_parts)
+ keyno= tab->ref.key;
+ else if (tab->select && quick)
+ keyno = quick->index;
+
+ if (keyno != MAX_KEY && keyno == table->file->pushed_idx_cond_keyno &&
+ table->file->pushed_idx_cond)
+ eta->push_extra(ET_USING_INDEX_CONDITION);
+ else if (tab->cache_idx_cond)
+ eta->push_extra(ET_USING_INDEX_CONDITION_BKA);
+
+ if (quick_type == QUICK_SELECT_I::QS_TYPE_ROR_UNION ||
+ quick_type == QUICK_SELECT_I::QS_TYPE_ROR_INTERSECT ||
+ quick_type == QUICK_SELECT_I::QS_TYPE_INDEX_INTERSECT ||
+ quick_type == QUICK_SELECT_I::QS_TYPE_INDEX_MERGE)
+ {
+ eta->push_extra(ET_USING);
+ }
+ if (tab->select)
+ {
+ if (tab->use_quick == 2)
+ {
+ eta->push_extra(ET_RANGE_CHECKED_FOR_EACH_RECORD);
+ eta->range_checked_map= tab->keys;
+ }
+ else if (tab->select->cond ||
+ (tab->cache_select && tab->cache_select->cond))
+ {
+ const COND *pushed_cond= tab->table->file->pushed_cond;
+
+ if (((thd->variables.optimizer_switch &
+ OPTIMIZER_SWITCH_ENGINE_CONDITION_PUSHDOWN) ||
+ (tab->table->file->ha_table_flags() &
+ HA_MUST_USE_TABLE_CONDITION_PUSHDOWN)) &&
+ pushed_cond)
+ {
+ eta->push_extra(ET_USING_WHERE_WITH_PUSHED_CONDITION);
+ /*
+ psergey-todo: what to do? This was useful with NDB only.
+
+ if (explain_flags & DESCRIBE_EXTENDED)
+ {
+ extra.append(STRING_WITH_LEN(": "));
+ ((COND *)pushed_cond)->print(&extra, QT_ORDINARY);
+ }
+ */
+ }
+ else
+ eta->push_extra(ET_USING_WHERE);
+ }
+ }
+ if (table_list /* SJM bushes don't have table_list */ &&
+ table_list->schema_table &&
+ table_list->schema_table->i_s_requested_object & OPTIMIZE_I_S_TABLE)
+ {
+ if (!table_list->table_open_method)
+ eta->push_extra(ET_SKIP_OPEN_TABLE);
+ else if (table_list->table_open_method == OPEN_FRM_ONLY)
+ eta->push_extra(ET_OPEN_FRM_ONLY);
+ else
+ eta->push_extra(ET_OPEN_FULL_TABLE);
+ /* psergey-note: the following has a bug.*/
+ if (table_list->is_table_read_plan->has_db_lookup_value() &&
+ table_list->is_table_read_plan->has_table_lookup_value())
+ eta->push_extra(ET_SCANNED_0_DATABASES);
+ else if (table_list->is_table_read_plan->has_db_lookup_value() ||
+ table_list->is_table_read_plan->has_table_lookup_value())
+ eta->push_extra(ET_SCANNED_1_DATABASE);
+ else
+ eta->push_extra(ET_SCANNED_ALL_DATABASES);
+ }
+ if (key_read)
+ {
+ if (quick_type == QUICK_SELECT_I::QS_TYPE_GROUP_MIN_MAX)
+ {
+ QUICK_GROUP_MIN_MAX_SELECT *qgs=
+ (QUICK_GROUP_MIN_MAX_SELECT *) tab->select->quick;
+ eta->push_extra(ET_USING_INDEX_FOR_GROUP_BY);
+ eta->loose_scan_is_scanning= qgs->loose_scan_is_scanning();
+ }
+ else
+ eta->push_extra(ET_USING_INDEX);
+ }
+ if (table->reginfo.not_exists_optimize)
+ eta->push_extra(ET_NOT_EXISTS);
+
+ if (quick_type == QUICK_SELECT_I::QS_TYPE_RANGE)
+ {
+ explain_append_mrr_info((QUICK_RANGE_SELECT*)(tab->select->quick),
+ &eta->mrr_type);
+ if (eta->mrr_type.length() > 0)
+ eta->push_extra(ET_USING_MRR);
+ }
+
+ if (distinct & test_all_bits(prefix_tables, join->select_list_used_tables))
+ eta->push_extra(ET_DISTINCT);
+ if (tab->loosescan_match_tab)
+ {
+ eta->push_extra(ET_LOOSESCAN);
+ }
+
+ if (tab->first_weedout_table)
+ eta->push_extra(ET_START_TEMPORARY);
+ if (tab->check_weed_out_table)
+ eta->push_extra(ET_END_TEMPORARY);
+ else if (tab->do_firstmatch)
+ {
+ if (tab->do_firstmatch == /*join->join_tab*/ first_top_tab - 1)
+ eta->push_extra(ET_FIRST_MATCH);
+ else
+ {
+ eta->push_extra(ET_FIRST_MATCH);
+ TABLE *prev_table=tab->do_firstmatch->table;
+ if (prev_table->derived_select_number)
+ {
+ char namebuf[NAME_LEN];
+ /* Derived table name generation */
+ int len= my_snprintf(namebuf, sizeof(namebuf)-1,
+ "<derived%u>",
+ prev_table->derived_select_number);
+ eta->firstmatch_table_name.append(namebuf, len);
+ }
+ else
+ eta->firstmatch_table_name.append(prev_table->pos_in_table_list->alias);
+ }
+ }
+
+ for (uint part= 0; part < tab->ref.key_parts; part++)
+ {
+ if (tab->ref.cond_guards[part])
+ {
+ eta->push_extra(ET_FULL_SCAN_ON_NULL_KEY);
+ break;
+ }
+ }
+
+ if (tab->cache)
+ {
+ eta->push_extra(ET_USING_JOIN_BUFFER);
+ tab->cache->save_explain_data(&eta->bka_type);
+ }
+ }
+}
/*
Save Query Plan Footprint
@@ -23262,9 +23688,6 @@ int JOIN::save_explain_data_intern(Explain_query *output, bool need_tmp_table,
{
Explain_node *explain_node;
JOIN *join= this; /* Legacy: this code used to be a non-member function */
- THD *thd=join->thd;
- const CHARSET_INFO *cs= system_charset_info;
- int quick_type;
int error= 0;
DBUG_ENTER("JOIN::save_explain_data_intern");
DBUG_PRINT("info", ("Select 0x%lx, type %s, message %s",
@@ -23305,27 +23728,9 @@ int JOIN::save_explain_data_intern(Explain_query *output, bool need_tmp_table,
for (JOIN_TAB *tab= first_breadth_first_tab(join, WALK_OPTIMIZATION_TABS); tab;
tab= next_breadth_first_tab(join, WALK_OPTIMIZATION_TABS, tab))
{
- uint select_id;
- if (tab->bush_root_tab)
- {
- JOIN_TAB *first_sibling= tab->bush_root_tab->bush_children->start;
- select_id= first_sibling->emb_sj_nest->sj_subq_pred->get_identifier();
- }
- else
- select_id= join->select_lex->select_number;
- TABLE *table=tab->table;
- TABLE_LIST *table_list= tab->table->pos_in_table_list;
- char buff4[512];
- my_bool key_read;
- char table_name_buffer[SAFE_NAME_LEN];
- String tmp4(buff4,sizeof(buff4),cs);
- KEY *key_info= 0;
- uint key_len= 0;
- tmp4.length(0);
- quick_type= -1;
- QUICK_SELECT_I *quick= NULL;
JOIN_TAB *saved_join_tab= NULL;
+ TABLE *table=tab->table;
/* Don't show eliminated tables */
if (table->map & join->eliminated_tables)
@@ -23343,385 +23748,20 @@ int JOIN::save_explain_data_intern(Explain_query *output, bool need_tmp_table,
Explain_table_access *eta= new (output->mem_root) Explain_table_access;
xpl_sel->add_table(eta);
- eta->key.set(thd->mem_root, NULL, (uint)-1);
- eta->quick_info= NULL;
-
- tab->tracker= &eta->tracker;
-
- /* id */
- if (tab->bush_root_tab)
- eta->sjm_nest_select_id= select_id;
- else
- eta->sjm_nest_select_id= 0;
-
- /* select_type */
- xpl_sel->select_type= join->select_lex->type;
-
- /* table */
- if (table->derived_select_number)
- {
- /* Derived table name generation */
- int len= my_snprintf(table_name_buffer, sizeof(table_name_buffer)-1,
- "<derived%u>",
- table->derived_select_number);
- eta->table_name.copy(table_name_buffer, len, cs);
- }
- else if (tab->bush_children)
- {
- JOIN_TAB *ctab= tab->bush_children->start;
- /* table */
- int len= my_snprintf(table_name_buffer,
- sizeof(table_name_buffer)-1,
- "<subquery%d>",
- ctab->emb_sj_nest->sj_subq_pred->get_identifier());
- eta->table_name.copy(table_name_buffer, len, cs);
- }
- else
- {
- TABLE_LIST *real_table= table->pos_in_table_list;
- eta->table_name.copy(real_table->alias, strlen(real_table->alias), cs);
- }
-
- /* "partitions" column */
- {
-#ifdef WITH_PARTITION_STORAGE_ENGINE
- partition_info *part_info;
- if (!table->derived_select_number &&
- (part_info= table->part_info))
- {
- make_used_partitions_str(part_info, &eta->used_partitions);
- eta->used_partitions_set= true;
- }
- else
- eta->used_partitions_set= false;
-#else
- /* just produce empty column if partitioning is not compiled in */
- eta->used_partitions_set= false;
-#endif
- }
-
- /* "type" column */
- enum join_type tab_type= tab->type;
- if ((tab->type == JT_ALL || tab->type == JT_HASH) &&
- tab->select && tab->select->quick && tab->use_quick != 2)
- {
- quick= tab->select->quick;
- quick_type= tab->select->quick->get_type();
- if ((quick_type == QUICK_SELECT_I::QS_TYPE_INDEX_MERGE) ||
- (quick_type == QUICK_SELECT_I::QS_TYPE_INDEX_INTERSECT) ||
- (quick_type == QUICK_SELECT_I::QS_TYPE_ROR_INTERSECT) ||
- (quick_type == QUICK_SELECT_I::QS_TYPE_ROR_UNION))
- tab_type= tab->type == JT_ALL ? JT_INDEX_MERGE : JT_HASH_INDEX_MERGE;
- else
- tab_type= tab->type == JT_ALL ? JT_RANGE : JT_HASH_RANGE;
- }
- eta->type= tab_type;
-
- /* Build "possible_keys" value */
- append_possible_keys(&eta->possible_keys_str, table, tab->keys);
-
- /* Build "key", "key_len", and "ref" */
- if (tab_type == JT_NEXT)
- {
- key_info= table->key_info+tab->index;
- key_len= key_info->key_length;
- }
- else if (tab->ref.key_parts)
- {
- key_info= tab->get_keyinfo_by_key_no(tab->ref.key);
- key_len= tab->ref.key_length;
- }
-
- /*
- In STRAIGHT_JOIN queries, there can be join tabs with JT_CONST type
- that still have quick selects.
- */
- if (tab->select && tab->select->quick && tab_type != JT_CONST)
- {
- eta->quick_info= tab->select->quick->get_explain(thd->mem_root);
- }
-
- if (key_info) /* 'index' or 'ref' access */
- {
- eta->key.set(thd->mem_root, key_info->name, key_len);
-
- if (tab->ref.key_parts && tab_type != JT_FT)
- {
- store_key **ref=tab->ref.key_copy;
- for (uint kp= 0; kp < tab->ref.key_parts; kp++)
- {
- if (tmp4.length())
- tmp4.append(',');
-
- if ((key_part_map(1) << kp) & tab->ref.const_ref_part_map)
- tmp4.append("const");
- else
- {
- tmp4.append((*ref)->name(), strlen((*ref)->name()), cs);
- ref++;
- }
- }
- }
- }
-
- if (tab_type == JT_HASH_NEXT) /* full index scan + hash join */
- {
- eta->hash_next_key.set(thd->mem_root,
- table->key_info[tab->index].name,
- table->key_info[tab->index].key_length);
- }
-
- if (key_info)
- {
- if (key_info && tab_type != JT_NEXT)
- {
- eta->ref.copy(tmp4);
- eta->ref_set= true;
- }
- else
- eta->ref_set= false;
- }
- else
- {
- if (table_list && /* SJM bushes don't have table_list */
- table_list->schema_table &&
- table_list->schema_table->i_s_requested_object & OPTIMIZE_I_S_TABLE)
- {
- const char *tmp_buff;
- int f_idx;
- StringBuffer<64> key_name_buf;
- if (table_list->has_db_lookup_value)
- {
- /* The "key" has the name of the column referring to the database */
- f_idx= table_list->schema_table->idx_field1;
- tmp_buff= table_list->schema_table->fields_info[f_idx].field_name;
- key_name_buf.append(tmp_buff, strlen(tmp_buff), cs);
- }
- if (table_list->has_table_lookup_value)
- {
- if (table_list->has_db_lookup_value)
- key_name_buf.append(',');
-
- f_idx= table_list->schema_table->idx_field2;
- tmp_buff= table_list->schema_table->fields_info[f_idx].field_name;
- key_name_buf.append(tmp_buff, strlen(tmp_buff), cs);
- }
-
- if (key_name_buf.length())
- eta->key.set(thd->mem_root, key_name_buf.c_ptr_safe(), -1);
- }
- eta->ref_set= false;
- }
-
- /* "rows" */
- if (table_list /* SJM bushes don't have table_list */ &&
- table_list->schema_table)
- {
- /* I_S tables have rows=extra=NULL */
- eta->rows_set= false;
- eta->filtered_set= false;
- }
- else
- {
- double examined_rows= tab->get_examined_rows();
- eta->rows_set= true;
- eta->rows= (ha_rows) examined_rows;
+ tab->save_explain_data(eta, used_tables, distinct, first_top_tab);
- /* "filtered" */
- float f= 0.0;
- if (examined_rows)
- {
- double pushdown_cond_selectivity= tab->cond_selectivity;
- if (pushdown_cond_selectivity == 1.0)
- f= (float) (100.0 * tab->records_read / examined_rows);
- else
- f= (float) (100.0 * pushdown_cond_selectivity);
- }
- set_if_smaller(f, 100.0);
- eta->filtered_set= true;
- eta->filtered= f;
- }
-
- /* Build "Extra" field and save it */
- key_read=table->key_read;
- if ((tab_type == JT_NEXT || tab_type == JT_CONST) &&
- table->covering_keys.is_set(tab->index))
- key_read=1;
- if (quick_type == QUICK_SELECT_I::QS_TYPE_ROR_INTERSECT &&
- !((QUICK_ROR_INTERSECT_SELECT*)quick)->need_to_fetch_row)
- key_read=1;
-
- if (tab->info)
+ if (need_tmp_table)
{
- eta->push_extra(tab->info);
+ need_tmp_table=0;
+ xpl_sel->using_temporary= true;
}
- else if (tab->packed_info & TAB_INFO_HAVE_VALUE)
+ if (need_order)
{
- if (tab->packed_info & TAB_INFO_USING_INDEX)
- eta->push_extra(ET_USING_INDEX);
- if (tab->packed_info & TAB_INFO_USING_WHERE)
- eta->push_extra(ET_USING_WHERE);
- if (tab->packed_info & TAB_INFO_FULL_SCAN_ON_NULL)
- eta->push_extra(ET_FULL_SCAN_ON_NULL_KEY);
+ need_order=0;
+ xpl_sel->using_filesort= true;
}
- else
- {
- uint keyno= MAX_KEY;
- if (tab->ref.key_parts)
- keyno= tab->ref.key;
- else if (tab->select && quick)
- keyno = quick->index;
-
- if (keyno != MAX_KEY && keyno == table->file->pushed_idx_cond_keyno &&
- table->file->pushed_idx_cond)
- eta->push_extra(ET_USING_INDEX_CONDITION);
- else if (tab->cache_idx_cond)
- eta->push_extra(ET_USING_INDEX_CONDITION_BKA);
-
- if (quick_type == QUICK_SELECT_I::QS_TYPE_ROR_UNION ||
- quick_type == QUICK_SELECT_I::QS_TYPE_ROR_INTERSECT ||
- quick_type == QUICK_SELECT_I::QS_TYPE_INDEX_INTERSECT ||
- quick_type == QUICK_SELECT_I::QS_TYPE_INDEX_MERGE)
- {
- eta->push_extra(ET_USING);
- }
- if (tab->select)
- {
- if (tab->use_quick == 2)
- {
- eta->push_extra(ET_RANGE_CHECKED_FOR_EACH_RECORD);
- eta->range_checked_map= tab->keys;
- }
- else if (tab->select->cond ||
- (tab->cache_select && tab->cache_select->cond))
- {
- const COND *pushed_cond= tab->table->file->pushed_cond;
-
- if (((thd->variables.optimizer_switch &
- OPTIMIZER_SWITCH_ENGINE_CONDITION_PUSHDOWN) ||
- (tab->table->file->ha_table_flags() &
- HA_MUST_USE_TABLE_CONDITION_PUSHDOWN)) &&
- pushed_cond)
- {
- eta->push_extra(ET_USING_WHERE_WITH_PUSHED_CONDITION);
- /*
- psergey-todo: what to do? This was useful with NDB only.
- if (explain_flags & DESCRIBE_EXTENDED)
- {
- extra.append(STRING_WITH_LEN(": "));
- ((COND *)pushed_cond)->print(&extra, QT_ORDINARY);
- }
- */
- }
- else
- eta->push_extra(ET_USING_WHERE);
- }
- }
- if (table_list /* SJM bushes don't have table_list */ &&
- table_list->schema_table &&
- table_list->schema_table->i_s_requested_object & OPTIMIZE_I_S_TABLE)
- {
- if (!table_list->table_open_method)
- eta->push_extra(ET_SKIP_OPEN_TABLE);
- else if (table_list->table_open_method == OPEN_FRM_ONLY)
- eta->push_extra(ET_OPEN_FRM_ONLY);
- else
- eta->push_extra(ET_OPEN_FULL_TABLE);
- /* psergey-note: the following has a bug.*/
- if (table_list->has_db_lookup_value &&
- table_list->has_table_lookup_value)
- eta->push_extra(ET_SCANNED_0_DATABASES);
- else if (table_list->has_db_lookup_value ||
- table_list->has_table_lookup_value)
- eta->push_extra(ET_SCANNED_1_DATABASE);
- else
- eta->push_extra(ET_SCANNED_ALL_DATABASES);
- }
- if (key_read)
- {
- if (quick_type == QUICK_SELECT_I::QS_TYPE_GROUP_MIN_MAX)
- {
- QUICK_GROUP_MIN_MAX_SELECT *qgs=
- (QUICK_GROUP_MIN_MAX_SELECT *) tab->select->quick;
- eta->push_extra(ET_USING_INDEX_FOR_GROUP_BY);
- eta->loose_scan_is_scanning= qgs->loose_scan_is_scanning();
- }
- else
- eta->push_extra(ET_USING_INDEX);
- }
- if (table->reginfo.not_exists_optimize)
- eta->push_extra(ET_NOT_EXISTS);
-
- if (quick_type == QUICK_SELECT_I::QS_TYPE_RANGE)
- {
- explain_append_mrr_info((QUICK_RANGE_SELECT*)(tab->select->quick),
- &eta->mrr_type);
- if (eta->mrr_type.length() > 0)
- eta->push_extra(ET_USING_MRR);
- }
-
- if (need_tmp_table)
- {
- need_tmp_table=0;
- xpl_sel->using_temporary= true;
- }
- if (need_order)
- {
- need_order=0;
- xpl_sel->using_filesort= true;
- }
- if (distinct & test_all_bits(used_tables,
- join->select_list_used_tables))
- eta->push_extra(ET_DISTINCT);
- if (tab->loosescan_match_tab)
- {
- eta->push_extra(ET_LOOSESCAN);
- }
-
- if (tab->first_weedout_table)
- eta->push_extra(ET_START_TEMPORARY);
- if (tab->check_weed_out_table)
- eta->push_extra(ET_END_TEMPORARY);
- else if (tab->do_firstmatch)
- {
- if (tab->do_firstmatch == /*join->join_tab*/ first_top_tab - 1)
- eta->push_extra(ET_FIRST_MATCH);
- else
- {
- eta->push_extra(ET_FIRST_MATCH);
- TABLE *prev_table=tab->do_firstmatch->table;
- if (prev_table->derived_select_number)
- {
- char namebuf[NAME_LEN];
- /* Derived table name generation */
- int len= my_snprintf(namebuf, sizeof(namebuf)-1,
- "<derived%u>",
- prev_table->derived_select_number);
- eta->firstmatch_table_name.append(namebuf, len);
- }
- else
- eta->firstmatch_table_name.append(prev_table->pos_in_table_list->alias);
- }
- }
-
- for (uint part= 0; part < tab->ref.key_parts; part++)
- {
- if (tab->ref.cond_guards[part])
- {
- eta->push_extra(ET_FULL_SCAN_ON_NULL_KEY);
- break;
- }
- }
-
- if (tab->cache)
- {
- eta->push_extra(ET_USING_JOIN_BUFFER);
- tab->cache->save_explain_data(&eta->bka_type);
- }
- }
-
if (saved_join_tab)
tab= saved_join_tab;
diff --git a/sql/sql_select.h b/sql/sql_select.h
index e31767f8835..c1eadd90fcb 100644
--- a/sql/sql_select.h
+++ b/sql/sql_select.h
@@ -537,6 +537,11 @@ typedef struct st_join_table {
}
void remove_redundant_bnl_scan_conds();
+
+ void save_explain_data(Explain_table_access *eta, table_map prefix_tables,
+ bool distinct, struct st_join_table *first_top_tab);
+
+ void update_explain_data(uint idx);
} JOIN_TAB;
diff --git a/sql/sql_show.cc b/sql/sql_show.cc
index 375be489867..61c085903d8 100644
--- a/sql/sql_show.cc
+++ b/sql/sql_show.cc
@@ -121,12 +121,6 @@ append_algorithm(TABLE_LIST *table, String *buff);
static COND * make_cond_for_info_schema(COND *cond, TABLE_LIST *table);
-typedef struct st_lookup_field_values
-{
- LEX_STRING db_value, table_value;
- bool wild_db_value, wild_table_value;
-} LOOKUP_FIELD_VALUES;
-
bool get_lookup_field_values(THD *, COND *, TABLE_LIST *, LOOKUP_FIELD_VALUES *);
/***************************************************************************
@@ -4628,6 +4622,10 @@ public:
from frm files and storage engine are filled by the function
get_all_tables().
+ @note This function assumes optimize_for_get_all_tables() has been
+ run for the table and produced a "read plan" in
+ tables->is_table_read_plan.
+
@param[in] thd thread handler
@param[in] tables I_S table
@param[in] cond 'WHERE' condition
@@ -4644,16 +4642,16 @@ int get_all_tables(THD *thd, TABLE_LIST *tables, COND *cond)
TABLE_LIST table_acl_check;
SELECT_LEX *lsel= tables->schema_select_lex;
ST_SCHEMA_TABLE *schema_table= tables->schema_table;
- LOOKUP_FIELD_VALUES lookup_field_vals;
+ IS_table_read_plan *plan= tables->is_table_read_plan;
enum enum_schema_tables schema_table_idx;
Dynamic_array<LEX_STRING*> db_names;
- COND *partial_cond= 0;
+ Item *partial_cond= plan->partial_cond;
int error= 1;
Open_tables_backup open_tables_state_backup;
#ifndef NO_EMBEDDED_ACCESS_CHECKS
Security_context *sctx= thd->security_ctx;
#endif
- uint table_open_method;
+ uint table_open_method= tables->table_open_method;
bool can_deadlock;
DBUG_ENTER("get_all_tables");
@@ -4677,9 +4675,6 @@ int get_all_tables(THD *thd, TABLE_LIST *tables, COND *cond)
thd->reset_n_backup_open_tables_state(&open_tables_state_backup);
schema_table_idx= get_schema_table_idx(schema_table);
- tables->table_open_method= table_open_method=
- get_table_open_method(tables, schema_table, schema_table_idx);
- DBUG_PRINT("open_method", ("%d", tables->table_open_method));
/*
this branch processes SHOW FIELDS, SHOW INDEXES commands.
see sql_parse.cc, prepare_schema_table() function where
@@ -4703,44 +4698,12 @@ int get_all_tables(THD *thd, TABLE_LIST *tables, COND *cond)
goto err;
}
- if (get_lookup_field_values(thd, cond, tables, &lookup_field_vals))
+ if (plan->no_rows)
{
error= 0;
goto err;
}
- DBUG_PRINT("info",("db_name='%s', table_name='%s'",
- lookup_field_vals.db_value.str,
- lookup_field_vals.table_value.str));
-
- if (!lookup_field_vals.wild_db_value && !lookup_field_vals.wild_table_value)
- {
- /*
- if lookup value is empty string then
- it's impossible table name or db name
- */
- if ((lookup_field_vals.db_value.str &&
- !lookup_field_vals.db_value.str[0]) ||
- (lookup_field_vals.table_value.str &&
- !lookup_field_vals.table_value.str[0]))
- {
- error= 0;
- goto err;
- }
- }
-
- if (lookup_field_vals.db_value.length &&
- !lookup_field_vals.wild_db_value)
- tables->has_db_lookup_value= TRUE;
- if (lookup_field_vals.table_value.length &&
- !lookup_field_vals.wild_table_value)
- tables->has_table_lookup_value= TRUE;
-
- if (tables->has_db_lookup_value && tables->has_table_lookup_value)
- partial_cond= 0;
- else
- partial_cond= make_cond_for_info_schema(cond, tables);
-
if (lex->describe)
{
/* EXPLAIN SELECT */
@@ -4750,7 +4713,7 @@ int get_all_tables(THD *thd, TABLE_LIST *tables, COND *cond)
bzero((char*) &table_acl_check, sizeof(table_acl_check));
- if (make_db_list(thd, &db_names, &lookup_field_vals))
+ if (make_db_list(thd, &db_names, &plan->lookup_field_vals))
goto err;
for (size_t i=0; i < db_names.elements(); i++)
{
@@ -4765,7 +4728,7 @@ int get_all_tables(THD *thd, TABLE_LIST *tables, COND *cond)
{
Dynamic_array<LEX_STRING*> table_names;
int res= make_table_name_list(thd, &table_names, lex,
- &lookup_field_vals, db_name);
+ &plan->lookup_field_vals, db_name);
if (res == 2) /* Not fatal error, continue */
continue;
if (res)
@@ -4802,8 +4765,8 @@ int get_all_tables(THD *thd, TABLE_LIST *tables, COND *cond)
already created by make_table_name_list() function).
*/
if (!table_open_method && schema_table_idx == SCH_TABLES &&
- (!lookup_field_vals.table_value.length ||
- lookup_field_vals.wild_table_value))
+ (!plan->lookup_field_vals.table_value.length ||
+ plan->lookup_field_vals.wild_table_value))
{
table->field[0]->store(STRING_WITH_LEN("def"), system_charset_info);
if (schema_table_store_record(thd, table))
@@ -7980,6 +7943,137 @@ int make_schema_select(THD *thd, SELECT_LEX *sel,
/*
+ Optimize reading from an I_S table.
+
+ @detail
+ This function prepares a plan for populating an I_S table with
+ get_all_tables().
+
+ The plan is in IS_table_read_plan structure, it is saved in
+ tables->is_table_read_plan.
+
+ @return
+ false - Ok
+ true - Out Of Memory
+
+*/
+
+static bool optimize_for_get_all_tables(THD *thd, TABLE_LIST *tables, COND *cond)
+{
+ SELECT_LEX *lsel= tables->schema_select_lex;
+ ST_SCHEMA_TABLE *schema_table= tables->schema_table;
+ enum enum_schema_tables schema_table_idx;
+ IS_table_read_plan *plan;
+ DBUG_ENTER("get_all_tables");
+
+ if (!(plan= new IS_table_read_plan()))
+ DBUG_RETURN(1);
+
+ tables->is_table_read_plan= plan;
+
+ schema_table_idx= get_schema_table_idx(schema_table);
+ tables->table_open_method= get_table_open_method(tables, schema_table,
+ schema_table_idx);
+ DBUG_PRINT("open_method", ("%d", tables->table_open_method));
+
+ /*
+ this branch processes SHOW FIELDS, SHOW INDEXES commands.
+ see sql_parse.cc, prepare_schema_table() function where
+ this values are initialized
+ */
+ if (lsel && lsel->table_list.first)
+ {
+ /* These do not need to have a query plan */
+ goto end;
+ }
+
+ if (get_lookup_field_values(thd, cond, tables, &plan->lookup_field_vals))
+ {
+ plan->no_rows= true;
+ goto end;
+ }
+
+ DBUG_PRINT("info",("db_name='%s', table_name='%s'",
+ plan->lookup_field_vals.db_value.str,
+ plan->lookup_field_vals.table_value.str));
+
+ if (!plan->lookup_field_vals.wild_db_value &&
+ !plan->lookup_field_vals.wild_table_value)
+ {
+ /*
+ if lookup value is empty string then
+ it's impossible table name or db name
+ */
+ if ((plan->lookup_field_vals.db_value.str &&
+ !plan->lookup_field_vals.db_value.str[0]) ||
+ (plan->lookup_field_vals.table_value.str &&
+ !plan->lookup_field_vals.table_value.str[0]))
+ {
+ plan->no_rows= true;
+ goto end;
+ }
+ }
+
+ if (plan->has_db_lookup_value() && plan->has_table_lookup_value())
+ plan->partial_cond= 0;
+ else
+ plan->partial_cond= make_cond_for_info_schema(cond, tables);
+
+end:
+ DBUG_RETURN(0);
+}
+
+
+/*
+ This is the optimizer part of get_schema_tables_result().
+*/
+
+bool optimize_schema_tables_reads(JOIN *join)
+{
+ THD *thd= join->thd;
+ bool result= 0;
+ DBUG_ENTER("optimize_schema_tables_reads");
+
+ for (JOIN_TAB *tab= first_linear_tab(join, WITH_CONST_TABLES);
+ tab;
+ tab= next_linear_tab(join, tab, WITH_BUSH_ROOTS))
+ {
+ if (!tab->table || !tab->table->pos_in_table_list)
+ continue;
+
+ TABLE_LIST *table_list= tab->table->pos_in_table_list;
+ if (table_list->schema_table && thd->fill_information_schema_tables())
+ {
+ /* A value of 0 indicates a dummy implementation */
+ if (table_list->schema_table->fill_table == 0)
+ continue;
+
+ /* skip I_S optimizations specific to get_all_tables */
+ if (table_list->schema_table->fill_table != get_all_tables)
+ continue;
+
+ Item *cond= tab->select_cond;
+ if (tab->cache_select && tab->cache_select->cond)
+ {
+ /*
+ If join buffering is used, we should use the condition that is
+ attached to the join cache. Cache condition has a part of WHERE that
+ can be checked when we're populating this table.
+ join_tab->select_cond is of no interest, because it only has
+ conditions that depend on both this table and previous tables in the
+ join order.
+ */
+ cond= tab->cache_select->cond;
+ }
+
+ optimize_for_get_all_tables(thd, table_list, cond);
+ }
+ }
+ DBUG_RETURN(result);
+}
+
+
+/*
Fill temporary schema tables before SELECT
SYNOPSIS
@@ -7987,6 +8081,10 @@ int make_schema_select(THD *thd, SELECT_LEX *sel,
join join which use schema tables
executed_place place where I_S table processed
+ SEE ALSO
+ The optimization part is done by get_schema_tables_result(). This function
+ is run on query execution.
+
RETURN
FALSE success
TRUE error
@@ -8007,7 +8105,7 @@ bool get_schema_tables_result(JOIN *join,
for (JOIN_TAB *tab= first_linear_tab(join, WITH_CONST_TABLES);
tab;
- tab= next_linear_tab(join, tab, WITHOUT_BUSH_ROOTS))
+ tab= next_linear_tab(join, tab, WITH_BUSH_ROOTS))
{
if (!tab->table || !tab->table->pos_in_table_list)
break;
diff --git a/sql/sql_show.h b/sql/sql_show.h
index 708a77d74cd..96f271d34c1 100644
--- a/sql/sql_show.h
+++ b/sql/sql_show.h
@@ -150,6 +150,41 @@ public:
void call_in_target_thread();
};
+typedef struct st_lookup_field_values
+{
+ LEX_STRING db_value, table_value;
+ bool wild_db_value, wild_table_value;
+} LOOKUP_FIELD_VALUES;
+
+
+/*
+ INFORMATION_SCHEMA: Execution plan for get_all_tables() call
+*/
+
+class IS_table_read_plan : public Sql_alloc
+{
+public:
+ IS_table_read_plan() : no_rows(false) {}
+
+ bool no_rows;
+
+ LOOKUP_FIELD_VALUES lookup_field_vals;
+ Item *partial_cond;
+
+ bool has_db_lookup_value()
+ {
+ return (lookup_field_vals.db_value.length &&
+ !lookup_field_vals.wild_db_value);
+ }
+ bool has_table_lookup_value()
+ {
+ return (lookup_field_vals.table_value.length &&
+ !lookup_field_vals.wild_table_value);
+ }
+};
+
+bool optimize_schema_tables_reads(JOIN *join);
+
/* Handle the ignored database directories list for SHOW/I_S. */
bool ignore_db_dirs_init();
void ignore_db_dirs_free();
diff --git a/sql/table.h b/sql/table.h
index 86e03cdaaf5..6fa0aaaa3b8 100644
--- a/sql/table.h
+++ b/sql/table.h
@@ -1499,6 +1499,7 @@ typedef struct st_schema_table
uint i_s_requested_object; /* the object we need to open(TABLE | VIEW) */
} ST_SCHEMA_TABLE;
+class IS_table_read_plan;
/*
Types of derived tables. The ending part is a bitmap of phases that are
@@ -2044,12 +2045,23 @@ struct TABLE_LIST
/* TRUE <=> this table is a const one and was optimized away. */
bool optimized_away;
+ /* I_S: Flags to open_table (e.g. OPEN_TABLE_ONLY or OPEN_VIEW_ONLY) */
uint i_s_requested_object;
- bool has_db_lookup_value;
- bool has_table_lookup_value;
+
+ /*
+ I_S: how to read the tables (SKIP_OPEN_TABLE/OPEN_FRM_ONLY/OPEN_FULL_TABLE)
+ */
uint table_open_method;
+ /*
+ I_S: where the schema table was filled
+ (this is a hack. The code should be able to figure out whether reading
+ from I_S should be done by create_sort_index() or by JOIN::exec.)
+ */
enum enum_schema_table_state schema_table_state;
+ /* Something like a "query plan" for reading INFORMATION_SCHEMA table */
+ IS_table_read_plan *is_table_read_plan;
+
MDL_request mdl_request;
#ifdef WITH_PARTITION_STORAGE_ENGINE
View Lorry Controller Status