summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/r/subselect.result2
-rw-r--r--mysql-test/r/subselect_innodb.result4
-rw-r--r--mysql-test/r/subselect_mat_cost_bugs.result2
-rw-r--r--mysql-test/r/subselect_no_mat.result2
-rw-r--r--mysql-test/r/subselect_no_opts.result2
-rw-r--r--mysql-test/r/subselect_no_scache.result2
-rw-r--r--mysql-test/r/subselect_no_semijoin.result2
-rw-r--r--sql/sql_select.cc54
-rw-r--r--sql/sql_select.h13
9 files changed, 52 insertions, 31 deletions
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index 3ea6c0be398..8c71c09c1ac 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -4196,7 +4196,7 @@ INSERT INTO t1 VALUES (1,1),(2,1);
EXPLAIN SELECT 1 FROM t1 WHERE a = (SELECT COUNT(*) FROM t1 GROUP BY b);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ref a a 5 const 1 Using where; Using index
-2 SUBQUERY internal_tmp_table ALL group_key NULL NULL NULL 1 Using temporary; Using filesort
+2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort
DROP TABLE t1;
CREATE TABLE t1 (id int NOT NULL, st CHAR(2), INDEX idx(id));
INSERT INTO t1 VALUES
diff --git a/mysql-test/r/subselect_innodb.result b/mysql-test/r/subselect_innodb.result
index a0f05a26a46..a4670872fad 100644
--- a/mysql-test/r/subselect_innodb.result
+++ b/mysql-test/r/subselect_innodb.result
@@ -333,7 +333,7 @@ WHERE (SELECT DISTINCT b FROM t3) > 0);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 const PRIMARY PRIMARY 4 const 1 Using where; Using index
2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
-3 SUBQUERY internal_tmp_table ALL group_key NULL NULL NULL 0 Using temporary
+3 SUBQUERY t3 ALL NULL NULL NULL NULL 1 Using temporary
SELECT *
FROM t1
WHERE t1.a = (
@@ -386,7 +386,7 @@ select 1 from t1 where 1 like (select 1 from t1 where 1 <=> (select 1 from t1 gr
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 1
2 SUBQUERY t1 ALL NULL NULL NULL NULL 1
-3 SUBQUERY internal_tmp_table ALL group_key NULL NULL NULL 1 Using temporary; Using filesort
+3 SUBQUERY t1 ALL NULL NULL NULL NULL 1 Using temporary; Using filesort
select 1 from t1 where 1 like (select 1 from t1 where 1 <=> (select 1 from t1 group by a1));
1
1
diff --git a/mysql-test/r/subselect_mat_cost_bugs.result b/mysql-test/r/subselect_mat_cost_bugs.result
index 1d03f2e9fe6..8c95c8637aa 100644
--- a/mysql-test/r/subselect_mat_cost_bugs.result
+++ b/mysql-test/r/subselect_mat_cost_bugs.result
@@ -148,7 +148,7 @@ FROM t2 GROUP BY f1
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
2 SUBQUERY t1 system NULL NULL NULL NULL 1
-3 SUBQUERY internal_tmp_table ALL group_key NULL NULL NULL 1 Using temporary; Using filesort
+3 SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort
drop table t1, t2, t3;
#
# LP BUG#715034 Item_sum_distinct::clear(): Assertion `tree != 0' failed
diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result
index 8ce097787f1..48ab96dcaaa 100644
--- a/mysql-test/r/subselect_no_mat.result
+++ b/mysql-test/r/subselect_no_mat.result
@@ -4200,7 +4200,7 @@ INSERT INTO t1 VALUES (1,1),(2,1);
EXPLAIN SELECT 1 FROM t1 WHERE a = (SELECT COUNT(*) FROM t1 GROUP BY b);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ref a a 5 const 1 Using where; Using index
-2 SUBQUERY internal_tmp_table ALL group_key NULL NULL NULL 1 Using temporary; Using filesort
+2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort
DROP TABLE t1;
CREATE TABLE t1 (id int NOT NULL, st CHAR(2), INDEX idx(id));
INSERT INTO t1 VALUES
diff --git a/mysql-test/r/subselect_no_opts.result b/mysql-test/r/subselect_no_opts.result
index 466d8c8fd06..9ab4e23b091 100644
--- a/mysql-test/r/subselect_no_opts.result
+++ b/mysql-test/r/subselect_no_opts.result
@@ -4196,7 +4196,7 @@ INSERT INTO t1 VALUES (1,1),(2,1);
EXPLAIN SELECT 1 FROM t1 WHERE a = (SELECT COUNT(*) FROM t1 GROUP BY b);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ref a a 5 const 1 Using where; Using index
-2 SUBQUERY internal_tmp_table ALL group_key NULL NULL NULL 1 Using temporary; Using filesort
+2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort
DROP TABLE t1;
CREATE TABLE t1 (id int NOT NULL, st CHAR(2), INDEX idx(id));
INSERT INTO t1 VALUES
diff --git a/mysql-test/r/subselect_no_scache.result b/mysql-test/r/subselect_no_scache.result
index 1fde0d08d7e..d47aa956ead 100644
--- a/mysql-test/r/subselect_no_scache.result
+++ b/mysql-test/r/subselect_no_scache.result
@@ -4202,7 +4202,7 @@ INSERT INTO t1 VALUES (1,1),(2,1);
EXPLAIN SELECT 1 FROM t1 WHERE a = (SELECT COUNT(*) FROM t1 GROUP BY b);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ref a a 5 const 1 Using where; Using index
-2 SUBQUERY internal_tmp_table ALL group_key NULL NULL NULL 1 Using temporary; Using filesort
+2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort
DROP TABLE t1;
CREATE TABLE t1 (id int NOT NULL, st CHAR(2), INDEX idx(id));
INSERT INTO t1 VALUES
diff --git a/mysql-test/r/subselect_no_semijoin.result b/mysql-test/r/subselect_no_semijoin.result
index ba50b8522eb..96e6c2182fa 100644
--- a/mysql-test/r/subselect_no_semijoin.result
+++ b/mysql-test/r/subselect_no_semijoin.result
@@ -4196,7 +4196,7 @@ INSERT INTO t1 VALUES (1,1),(2,1);
EXPLAIN SELECT 1 FROM t1 WHERE a = (SELECT COUNT(*) FROM t1 GROUP BY b);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ref a a 5 const 1 Using where; Using index
-2 SUBQUERY internal_tmp_table ALL group_key NULL NULL NULL 1 Using temporary; Using filesort
+2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort
DROP TABLE t1;
CREATE TABLE t1 (id int NOT NULL, st CHAR(2), INDEX idx(id));
INSERT INTO t1 VALUES
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 720b4e418d7..dc09488632c 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -271,8 +271,11 @@ Item_equal *find_item_equal(COND_EQUAL *cond_equal, Field *field,
bool *inherited_fl);
JOIN_TAB *first_depth_first_tab(JOIN* join);
JOIN_TAB *next_depth_first_tab(JOIN* join, JOIN_TAB* tab);
-JOIN_TAB *first_breadth_first_tab(JOIN *join);
-JOIN_TAB *next_breadth_first_tab(JOIN *join, JOIN_TAB *tab);
+
+enum enum_exec_or_opt {WALK_OPTIMIZATION_TABS , WALK_EXECUTION_TABS};
+JOIN_TAB *first_breadth_first_tab(JOIN *join, enum enum_exec_or_opt tabs_kind);
+JOIN_TAB *next_breadth_first_tab(JOIN *join, enum enum_exec_or_opt tabs_kind,
+ JOIN_TAB *tab);
/**
This handles SELECT with and without UNION.
@@ -6649,12 +6652,12 @@ double JOIN::get_examined_rows()
{
ha_rows examined_rows;
double prev_fanout= 1;
- JOIN_TAB *tab= first_breadth_first_tab(this);
+ JOIN_TAB *tab= first_breadth_first_tab(this, WALK_OPTIMIZATION_TABS);
JOIN_TAB *prev_tab= tab;
examined_rows= tab->get_examined_rows();
- while ((tab= next_breadth_first_tab(this, tab)))
+ while ((tab= next_breadth_first_tab(this, WALK_OPTIMIZATION_TABS, tab)))
{
prev_fanout *= prev_tab->records_read;
examined_rows+= (ha_rows) (tab->get_examined_rows() * prev_fanout);
@@ -7269,23 +7272,30 @@ prev_record_reads(POSITION *positions, uint idx, table_map found_ref)
Enumerate join tabs in breadth-first fashion, including const tables.
*/
-JOIN_TAB *first_breadth_first_tab(JOIN *join)
+JOIN_TAB *first_breadth_first_tab(JOIN *join, enum enum_exec_or_opt tabs_kind)
{
- return join->join_tab; /* There's always one (i.e. first) table */
+ /* There's always one (i.e. first) table */
+ return (tabs_kind == WALK_EXECUTION_TABS)? join->join_tab:
+ join->table_access_tabs;
}
-JOIN_TAB *next_breadth_first_tab(JOIN *join, JOIN_TAB *tab)
+JOIN_TAB *next_breadth_first_tab(JOIN *join, enum enum_exec_or_opt tabs_kind,
+ JOIN_TAB *tab)
{
+ JOIN_TAB* const first_top_tab= first_breadth_first_tab(join, tabs_kind);
+ const uint n_top_tabs_count= (tabs_kind == WALK_EXECUTION_TABS)?
+ join->top_join_tab_count:
+ join->top_table_access_tabs_count;
if (!tab->bush_root_tab)
{
/* We're at top level. Get the next top-level tab */
tab++;
- if (tab < join->join_tab + join->top_join_tab_count)
+ if (tab < first_top_tab + n_top_tabs_count)
return tab;
/* No more top-level tabs. Switch to enumerating SJM nest children */
- tab= join->join_tab;
+ tab= first_top_tab;
}
else
{
@@ -7309,7 +7319,7 @@ JOIN_TAB *next_breadth_first_tab(JOIN *join, JOIN_TAB *tab)
Ok, "tab" points to a top-level table, and we need to find the next SJM
nest and enter it.
*/
- for (; tab < join->join_tab + join->top_join_tab_count; tab++)
+ for (; tab < first_top_tab + n_top_tabs_count; tab++)
{
if (tab->bush_children)
return tab->bush_children->start;
@@ -7333,7 +7343,7 @@ JOIN_TAB *first_top_level_tab(JOIN *join, enum enum_with_const_tables with_const
JOIN_TAB *next_top_level_tab(JOIN *join, JOIN_TAB *tab)
{
- tab= next_breadth_first_tab(join, tab);
+ tab= next_breadth_first_tab(join, WALK_EXECUTION_TABS, tab);
if (tab && tab->bush_root_tab)
tab= NULL;
return tab;
@@ -7633,6 +7643,12 @@ get_best_combination(JOIN *join)
join->top_join_tab_count= join->join_tab_ranges.head()->end -
join->join_tab_ranges.head()->start;
+ /*
+ Save pointers to select join tabs for SHOW EXPLAIN
+ */
+ join->table_access_tabs= join->join_tab;
+ join->top_table_access_tabs_count= join->top_join_tab_count;
+
update_depend_map(join);
DBUG_RETURN(0);
}
@@ -21389,8 +21405,8 @@ static void select_describe(JOIN *join, bool need_tmp_table, bool need_order,
bool printing_materialize_nest= FALSE;
uint select_id= join->select_lex->select_number;
- for (JOIN_TAB *tab= first_breadth_first_tab(join); tab;
- tab= next_breadth_first_tab(join, tab))
+ for (JOIN_TAB *tab= first_breadth_first_tab(join, WALK_OPTIMIZATION_TABS); tab;
+ tab= next_breadth_first_tab(join, WALK_OPTIMIZATION_TABS, tab))
{
if (tab->bush_root_tab)
{
@@ -21473,16 +21489,8 @@ static void select_describe(JOIN *join, bool need_tmp_table, bool need_order,
else
{
TABLE_LIST *real_table= table->pos_in_table_list;
- /*
- Internal temporary tables have no corresponding table reference
- object. Such a table may appear in EXPLAIN when a subquery that needs
- a temporary table has been executed, and JOIN::exec replaced the
- original JOIN with a plan to access the data in the temp table
- (made by JOIN::make_simple_join).
- */
- const char *tab_name= real_table ? real_table->alias :
- "internal_tmp_table";
- item_list.push_back(new Item_string(tab_name, strlen(tab_name), cs));
+ item_list.push_back(new Item_string(real_table->alias,
+ strlen(real_table->alias), cs));
}
/* "partitions" column */
if (join->thd->lex->describe & DESCRIBE_PARTITIONS)
diff --git a/sql/sql_select.h b/sql/sql_select.h
index 0ed976ac36a..be5f523a7e2 100644
--- a/sql/sql_select.h
+++ b/sql/sql_select.h
@@ -897,6 +897,19 @@ protected:
public:
JOIN_TAB *join_tab, **best_ref;
+
+ /*
+ For "Using temporary+Using filesort" queries, JOIN::join_tab can point to
+ either:
+ 1. array of join tabs describing how to run the select, or
+ 2. array of single join tab describing read from the temporary table.
+
+ SHOW EXPLAIN code needs to read/show #1. This is why two next members are
+ there for saving it.
+ */
+ JOIN_TAB *table_access_tabs;
+ uint top_table_access_tabs_count;
+
JOIN_TAB **map2table; ///< mapping between table indexes and JOIN_TABs
JOIN_TAB *join_tab_save; ///< saved join_tab for subquery reexecution
View Lorry Controller Status