3375 Gleb Shchepa 2011-07-02
WL#4897: Add EXPLAIN INSERT/UPDATE/DELETE
Merge-time fix for the bug: the Server loses "Using temporary"
and/or "Using filesort" messages if the respective JOIN_TAB
is not the first one in the join.
@ mysql-test/include/explain_non_select.inc
Test case for the bugfix has been added.
@ mysql-test/r/compress.result
Remove unnecessary result modification (will be eliminated
by the consolidated patch.
@ mysql-test/r/distinct.result
Remove unnecessary result modification (will be eliminated
by the consolidated patch.
@ mysql-test/r/group_by.result
Remove unnecessary result modification (will be eliminated
by the consolidated patch.
@ mysql-test/r/group_min_max.result
Remove unnecessary result modification (will be eliminated
by the consolidated patch.
@ mysql-test/r/information_schema.result
Remove unnecessary result modification (will be eliminated
by the consolidated patch.
@ mysql-test/r/innodb_explain_non_select.result
Test case for the bugfix has been added.
@ mysql-test/r/join_cache_jcl5.result
Remove unnecessary result modification (will be eliminated
by the consolidated patch.
@ mysql-test/r/join_cache_jcl6.result
Remove unnecessary result modification (will be eliminated
by the consolidated patch.
@ mysql-test/r/join_cache_jcl7.result
Remove unnecessary result modification (will be eliminated
by the consolidated patch.
@ mysql-test/r/join_cache_jcl8.result
Remove unnecessary result modification (will be eliminated
by the consolidated patch.
@ mysql-test/r/myisam_explain_non_select.result
Test case for the bugfix has been added.
@ mysql-test/suite/innodb/r/innodb_mysql.result
Remove unnecessary result modification (will be eliminated
by the consolidated patch.
@ sql/opt_explain.cc
New Explain_table_base::explain_tmptable_and_filesort function
has been introduced to append "Using temporary" and/or
"Using filesort" messages to the "extra" column.
The Explain_join::explain_extra function has been modified to
1) fix the bug and
2) remove unnecessary modifications of many result files by this WL.
modified:
mysql-test/include/explain_non_select.inc
mysql-test/r/compress.result
mysql-test/r/distinct.result
mysql-test/r/group_by.result
mysql-test/r/group_min_max.result
mysql-test/r/information_schema.result
mysql-test/r/innodb_explain_non_select.result
mysql-test/r/join_cache_jcl5.result
mysql-test/r/join_cache_jcl6.result
mysql-test/r/join_cache_jcl7.result
mysql-test/r/join_cache_jcl8.result
mysql-test/r/myisam_explain_non_select.result
mysql-test/suite/innodb/r/innodb_mysql.result
sql/opt_explain.cc
3374 Gleb Shchepa 2011-06-23
WL#4897: Add EXPLAIN INSERT/UPDATE/DELETE
Post-review bugfix:
after EXPLAIN bulk INSERT...SELECT and bulk INSERT...SELECT
to a # MyISAM table the SELECT query may fail with the
"1030: Got error 124 from storage engine" error message.
The select_insert class functions have been modified
to not to call ha_start_bulk_insert() on EXPLAIN query.
@ mysql-test/include/explain_non_select.inc
Test case has been added.
@ mysql-test/r/innodb_explain_non_select.result
Test case has been added.
@ mysql-test/r/myisam_explain_non_select.result
Test case has been added.
@ sql/sql_insert.cc
The select_insert class functions have been modified
to not to call ha_start_bulk_insert() on EXPLAIN query.
modified:
mysql-test/include/explain_non_select.inc
mysql-test/r/innodb_explain_non_select.result
mysql-test/r/myisam_explain_non_select.result
sql/sql_insert.cc
=== modified file 'mysql-test/include/explain_non_select.inc'
--- a/mysql-test/include/explain_non_select.inc 2011-06-22 21:48:04 +0000
+++ b/mysql-test/include/explain_non_select.inc 2011-07-02 13:53:01 +0000
@@ -740,3 +740,13 @@ SELECT * FROM t1 LEFT JOIN t2 ON t1.c1 =
DROP TABLE t1, t2;
+--echo #73
+
+CREATE TABLE t1 (id INT);
+CREATE TABLE t2 (id INT);
+INSERT INTO t1 VALUES (1), (2);
+
+EXPLAIN SELECT * FROM t1 LEFT JOIN t2 USING(id) GROUP BY t1.id;
+
+DROP TABLE t1,t2;
+
=== modified file 'mysql-test/r/compress.result'
--- a/mysql-test/r/compress.result 2011-06-16 14:13:47 +0000
+++ b/mysql-test/r/compress.result 2011-07-02 13:53:01 +0000
@@ -1434,7 +1434,7 @@ companynr companynr
41 40
explain select distinct t2.companynr,t4.companynr from t2,t4 where t2.companynr=t4.companynr+1;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t4 index NULL PRIMARY 1 NULL 12 Using temporary; Using index
+1 SIMPLE t4 index NULL PRIMARY 1 NULL 12 Using index; Using temporary
1 SIMPLE t2 ALL NULL NULL NULL NULL 1199 Using where; Using join buffer (BNL, incremental buffers)
select t2.fld1,t2.companynr,fld3,period from t3,t2 where t2.fld1 = 38208 and t2.fld1=t3.t2nr and period = 1008 or t2.fld1 = 38008 and t2.fld1 =t3.t2nr and period = 1008;
fld1 companynr fld3 period
=== modified file 'mysql-test/r/distinct.result'
--- a/mysql-test/r/distinct.result 2011-06-16 14:13:47 +0000
+++ b/mysql-test/r/distinct.result 2011-07-02 13:53:01 +0000
@@ -189,7 +189,7 @@ insert into t4 select * from t3;
insert into t3 select * from t4;
explain select distinct t1.a from t1,t3 where t1.a=t3.a;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 index PRIMARY PRIMARY 4 NULL 4 Using temporary; Using index
+1 SIMPLE t1 index PRIMARY PRIMARY 4 NULL 4 Using index; Using temporary
1 SIMPLE t3 ref a a 5 test.t1.a 11 Using index; Distinct
select distinct t1.a from t1,t3 where t1.a=t3.a;
a
@@ -299,7 +299,7 @@ WHERE
((t1.id=j_lj_t2.id AND t2_lj.id IS NULL) OR (t1.id=t2.id AND t2.idx=2))
AND ((t1.id=j_lj_t3.id AND t3_lj.id IS NULL) OR (t1.id=t3.id AND t3.idx=2));
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 index id id 4 NULL 2 Using temporary; Using index
+1 SIMPLE t1 index id id 4 NULL 2 Using index; Using temporary
1 SIMPLE t2 index id id 8 NULL 1 Using index; Distinct; Using join buffer (BNL, incremental buffers)
1 SIMPLE t3 index id id 8 NULL 1 Using index; Distinct; Using join buffer (BNL, incremental buffers)
1 SIMPLE j_lj_t2 index id id 4 NULL 2 Using where; Using index; Distinct; Using join buffer (BNL, incremental buffers)
@@ -753,7 +753,7 @@ INSERT INTO t1(a, b, c) VALUES (1, 1, 1)
(1, 2, 3);
EXPLAIN SELECT DISTINCT a, b, d, c FROM t1;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range NULL PRIMARY 16 NULL 7 Using temporary; Using index for group-by
+1 SIMPLE t1 range NULL PRIMARY 16 NULL 7 Using index for group-by; Using temporary
SELECT DISTINCT a, b, d, c FROM t1;
a b d c
1 1 0 1
=== modified file 'mysql-test/r/group_by.result'
--- a/mysql-test/r/group_by.result 2011-06-16 14:13:47 +0000
+++ b/mysql-test/r/group_by.result 2011-07-02 13:53:01 +0000
@@ -304,10 +304,10 @@ spid sum(userid)
1 1
explain select sql_big_result score,count(*) from t1 group by score desc;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 index NULL score 3 NULL 8 Using filesort; Using index
+1 SIMPLE t1 index NULL score 3 NULL 8 Using index; Using filesort
explain select sql_big_result score,count(*) from t1 group by score desc order by null;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 index NULL score 3 NULL 8 Using filesort; Using index
+1 SIMPLE t1 index NULL score 3 NULL 8 Using index; Using filesort
select sql_big_result score,count(*) from t1 group by score desc;
score count(*)
3 5
@@ -905,7 +905,7 @@ id select_type table type possible_keys
1 SIMPLE t1 index NULL b 5 NULL 128 Using index
EXPLAIN SELECT SQL_BIG_RESULT b, sum(1) FROM t1 GROUP BY b;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 index NULL b 5 NULL 128 Using filesort; Using index
+1 SIMPLE t1 index NULL b 5 NULL 128 Using index; Using filesort
SELECT b, sum(1) FROM t1 GROUP BY b;
b sum(1)
0 6
@@ -1648,7 +1648,7 @@ NULL
1
EXPLAIN SELECT b from t2 GROUP BY b;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 index NULL a 10 NULL 6 Using temporary; Using filesort; Using index
+1 SIMPLE t2 index NULL a 10 NULL 6 Using index; Using temporary; Using filesort
SELECT b from t2 GROUP BY b;
b
NULL
=== modified file 'mysql-test/r/group_min_max.result'
--- a/mysql-test/r/group_min_max.result 2011-06-16 14:13:47 +0000
+++ b/mysql-test/r/group_min_max.result 2011-07-02 13:53:01 +0000
@@ -1714,7 +1714,7 @@ id select_type table type possible_keys
1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 14 Using where; Using index for group-by
explain select distinct b from t1 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range NULL idx_t1_1 147 NULL 17 Using where; Using temporary; Using filesort; Using index for group-by
+1 SIMPLE t1 range NULL idx_t1_1 147 NULL 17 Using where; Using index for group-by; Using temporary; Using filesort
explain select distinct a1,a2,b from t2;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t2 range NULL idx_t2_1 146 NULL # Using index for group-by
@@ -1729,7 +1729,7 @@ id select_type table type possible_keys
1 SIMPLE t2 range idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 146 NULL # Using where; Using index for group-by
explain select distinct b from t2 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 range NULL idx_t2_1 146 NULL # Using where; Using temporary; Using filesort; Using index for group-by
+1 SIMPLE t2 range NULL idx_t2_1 146 NULL # Using where; Using index for group-by; Using temporary; Using filesort
select distinct a1,a2,b from t1;
a1 a2 b
a a a
@@ -2354,7 +2354,7 @@ a MIN(b) MAX(b)
EXPLAIN
SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a DESC;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range NULL break_it 10 NULL 7 Using temporary; Using filesort; Using index for group-by
+1 SIMPLE t1 range NULL break_it 10 NULL 7 Using index for group-by; Using temporary; Using filesort
SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a DESC;
a MIN(b) MAX(b)
4 1 3
@@ -2444,7 +2444,7 @@ a b
3 13
explain extended select sql_buffer_result a, max(b)+1 from t1 where a = 0 group by a;
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE t1 range PRIMARY,index PRIMARY 4 NULL 3 100.00 Using where; Using temporary; Using index for group-by
+1 SIMPLE t1 range PRIMARY,index PRIMARY 4 NULL 3 100.00 Using where; Using index for group-by; Using temporary
Warnings:
Note 1003 select sql_buffer_result `test`.`t1`.`a` AS `a`,(max(`test`.`t1`.`b`) + 1) AS `max(b)+1` from `test`.`t1` where (`test`.`t1`.`a` = 0) group by `test`.`t1`.`a`
drop table t1;
@@ -2833,7 +2833,7 @@ COUNT(DISTINCT b)
8
EXPLAIN SELECT COUNT(DISTINCT a) FROM t1 GROUP BY b;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 index NULL a 10 NULL 16 Using filesort; Using index
+1 SIMPLE t1 index NULL a 10 NULL 16 Using index; Using filesort
SELECT COUNT(DISTINCT a) FROM t1 GROUP BY b;
COUNT(DISTINCT a)
2
@@ -2882,7 +2882,7 @@ SELECT 1 FROM t1 GROUP BY a HAVING COUNT
1
EXPLAIN SELECT COUNT(DISTINCT t1_1.a) FROM t1 t1_1, t1 t1_2 GROUP BY t1_1.a;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1_1 index NULL a 10 NULL 16 Using temporary; Using filesort; Using index
+1 SIMPLE t1_1 index NULL a 10 NULL 16 Using index; Using temporary; Using filesort
1 SIMPLE t1_2 index NULL a 10 NULL 16 Using index; Using join buffer (BNL, incremental buffers)
SELECT COUNT(DISTINCT t1_1.a) FROM t1 t1_1, t1 t1_2 GROUP BY t1_1.a;
COUNT(DISTINCT t1_1.a)
=== modified file 'mysql-test/r/information_schema.result'
--- a/mysql-test/r/information_schema.result 2011-05-30 11:03:42 +0000
+++ b/mysql-test/r/information_schema.result 2011-07-02 13:53:01 +0000
@@ -1286,7 +1286,7 @@ from information_schema.tables
order by object_schema;
explain select * from v1;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE tables ALL NULL NULL NULL NULL NULL Using filesort; Open_frm_only; Scanned all databases
+1 SIMPLE tables ALL NULL NULL NULL NULL NULL Open_frm_only; Scanned all databases; Using filesort
explain select * from (select table_name from information_schema.tables) as a;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY <derived2> system NULL NULL NULL NULL 0 const row not found
=== modified file 'mysql-test/r/innodb_explain_non_select.result'
--- a/mysql-test/r/innodb_explain_non_select.result 2011-06-22 21:48:04 +0000
+++ b/mysql-test/r/innodb_explain_non_select.result 2011-07-02 13:53:01 +0000
@@ -3076,3 +3076,12 @@ id select_type table type possible_keys
INSERT INTO t2 SELECT * FROM t1;
SELECT * FROM t1 LEFT JOIN t2 ON t1.c1 = t2.c1;
DROP TABLE t1, t2;
+#73
+CREATE TABLE t1 (id INT);
+CREATE TABLE t2 (id INT);
+INSERT INTO t1 VALUES (1), (2);
+EXPLAIN SELECT * FROM t1 LEFT JOIN t2 USING(id) GROUP BY t1.id;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort
+1 SIMPLE t2 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (BNL, incremental buffers)
+DROP TABLE t1,t2;
=== modified file 'mysql-test/r/join_cache_jcl5.result'
--- a/mysql-test/r/join_cache_jcl5.result 2011-06-16 14:13:47 +0000
+++ b/mysql-test/r/join_cache_jcl5.result 2011-07-02 13:53:01 +0000
@@ -1449,7 +1449,7 @@ explain
select t1.a, count(t2.p) as count
from t1 left join t2 on t1.a=t2.a and t2.p % 2 = 1 group by t1.a;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 index NULL PRIMARY 4 NULL 8 Using temporary; Using filesort; Using index
+1 SIMPLE t1 index NULL PRIMARY 4 NULL 8 Using index; Using temporary; Using filesort
1 SIMPLE t2 ref i_a i_a 5 test.t1.a 2 Using where; Using join buffer (BKA, regular buffers)
select t1.a, count(t2.p) as count
from t1 left join t2 on t1.a=t2.a and t2.p % 2 = 1 group by t1.a;
@@ -1760,7 +1760,7 @@ explain
select t1.id1, sum(t2.id2) from t1 join t2 on t1.id1=t2.id1
where t1.d=3 group by t1.id1;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref idx1 idx1 5 const 4 Using where; Using temporary; Using filesort; Using index
+1 SIMPLE t1 ref idx1 idx1 5 const 4 Using where; Using index; Using temporary; Using filesort
1 SIMPLE t2 ref idx2 idx2 5 test.t1.id1 2 Using join buffer (BKA, regular buffers)
select t1.id1, sum(t2.id2) from t1 join t2 on t1.id1=t2.id1
where t1.d=3 group by t1.id1;
@@ -1772,7 +1772,7 @@ explain
select t1.id1 from t1 join t2 on t1.id1=t2.id1
where t1.d=3 and t2.id2 > 200 order by t1.id1;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref idx1 idx1 5 const 4 Using where; Using temporary; Using filesort; Using index
+1 SIMPLE t1 ref idx1 idx1 5 const 4 Using where; Using index; Using temporary; Using filesort
1 SIMPLE t2 ref idx2 idx2 5 test.t1.id1 2 Using where; Using join buffer (BKA, regular buffers)
select t1.id1 from t1 join t2 on t1.id1=t2.id1
where t1.d=3 and t2.id2 > 200 order by t1.id1;
=== modified file 'mysql-test/r/join_cache_jcl6.result'
--- a/mysql-test/r/join_cache_jcl6.result 2011-06-16 14:13:47 +0000
+++ b/mysql-test/r/join_cache_jcl6.result 2011-07-02 13:53:01 +0000
@@ -1449,7 +1449,7 @@ explain
select t1.a, count(t2.p) as count
from t1 left join t2 on t1.a=t2.a and t2.p % 2 = 1 group by t1.a;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 index NULL PRIMARY 4 NULL 8 Using temporary; Using filesort; Using index
+1 SIMPLE t1 index NULL PRIMARY 4 NULL 8 Using index; Using temporary; Using filesort
1 SIMPLE t2 ref i_a i_a 5 test.t1.a 2 Using where; Using join buffer (BKA, incremental buffers)
select t1.a, count(t2.p) as count
from t1 left join t2 on t1.a=t2.a and t2.p % 2 = 1 group by t1.a;
@@ -1760,7 +1760,7 @@ explain
select t1.id1, sum(t2.id2) from t1 join t2 on t1.id1=t2.id1
where t1.d=3 group by t1.id1;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref idx1 idx1 5 const 4 Using where; Using temporary; Using filesort; Using index
+1 SIMPLE t1 ref idx1 idx1 5 const 4 Using where; Using index; Using temporary; Using filesort
1 SIMPLE t2 ref idx2 idx2 5 test.t1.id1 2 Using join buffer (BKA, incremental buffers)
select t1.id1, sum(t2.id2) from t1 join t2 on t1.id1=t2.id1
where t1.d=3 group by t1.id1;
@@ -1772,7 +1772,7 @@ explain
select t1.id1 from t1 join t2 on t1.id1=t2.id1
where t1.d=3 and t2.id2 > 200 order by t1.id1;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref idx1 idx1 5 const 4 Using where; Using temporary; Using filesort; Using index
+1 SIMPLE t1 ref idx1 idx1 5 const 4 Using where; Using index; Using temporary; Using filesort
1 SIMPLE t2 ref idx2 idx2 5 test.t1.id1 2 Using where; Using join buffer (BKA, incremental buffers)
select t1.id1 from t1 join t2 on t1.id1=t2.id1
where t1.d=3 and t2.id2 > 200 order by t1.id1;
=== modified file 'mysql-test/r/join_cache_jcl7.result'
--- a/mysql-test/r/join_cache_jcl7.result 2011-06-16 14:13:47 +0000
+++ b/mysql-test/r/join_cache_jcl7.result 2011-07-02 13:53:01 +0000
@@ -1449,7 +1449,7 @@ explain
select t1.a, count(t2.p) as count
from t1 left join t2 on t1.a=t2.a and t2.p % 2 = 1 group by t1.a;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 index NULL PRIMARY 4 NULL 8 Using temporary; Using filesort; Using index
+1 SIMPLE t1 index NULL PRIMARY 4 NULL 8 Using index; Using temporary; Using filesort
1 SIMPLE t2 ref i_a i_a 5 test.t1.a 2 Using where; Using join buffer (BKA_UNIQUE, regular buffers)
select t1.a, count(t2.p) as count
from t1 left join t2 on t1.a=t2.a and t2.p % 2 = 1 group by t1.a;
@@ -1760,7 +1760,7 @@ explain
select t1.id1, sum(t2.id2) from t1 join t2 on t1.id1=t2.id1
where t1.d=3 group by t1.id1;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref idx1 idx1 5 const 4 Using where; Using temporary; Using filesort; Using index
+1 SIMPLE t1 ref idx1 idx1 5 const 4 Using where; Using index; Using temporary; Using filesort
1 SIMPLE t2 ref idx2 idx2 5 test.t1.id1 2 Using join buffer (BKA_UNIQUE, regular buffers)
select t1.id1, sum(t2.id2) from t1 join t2 on t1.id1=t2.id1
where t1.d=3 group by t1.id1;
@@ -1772,7 +1772,7 @@ explain
select t1.id1 from t1 join t2 on t1.id1=t2.id1
where t1.d=3 and t2.id2 > 200 order by t1.id1;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref idx1 idx1 5 const 4 Using where; Using temporary; Using filesort; Using index
+1 SIMPLE t1 ref idx1 idx1 5 const 4 Using where; Using index; Using temporary; Using filesort
1 SIMPLE t2 ref idx2 idx2 5 test.t1.id1 2 Using where; Using join buffer (BKA_UNIQUE, regular buffers)
select t1.id1 from t1 join t2 on t1.id1=t2.id1
where t1.d=3 and t2.id2 > 200 order by t1.id1;
=== modified file 'mysql-test/r/join_cache_jcl8.result'
--- a/mysql-test/r/join_cache_jcl8.result 2011-06-16 14:13:47 +0000
+++ b/mysql-test/r/join_cache_jcl8.result 2011-07-02 13:53:01 +0000
@@ -1449,7 +1449,7 @@ explain
select t1.a, count(t2.p) as count
from t1 left join t2 on t1.a=t2.a and t2.p % 2 = 1 group by t1.a;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 index NULL PRIMARY 4 NULL 8 Using temporary; Using filesort; Using index
+1 SIMPLE t1 index NULL PRIMARY 4 NULL 8 Using index; Using temporary; Using filesort
1 SIMPLE t2 ref i_a i_a 5 test.t1.a 2 Using where; Using join buffer (BKA_UNIQUE, incremental buffers)
select t1.a, count(t2.p) as count
from t1 left join t2 on t1.a=t2.a and t2.p % 2 = 1 group by t1.a;
@@ -1760,7 +1760,7 @@ explain
select t1.id1, sum(t2.id2) from t1 join t2 on t1.id1=t2.id1
where t1.d=3 group by t1.id1;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref idx1 idx1 5 const 4 Using where; Using temporary; Using filesort; Using index
+1 SIMPLE t1 ref idx1 idx1 5 const 4 Using where; Using index; Using temporary; Using filesort
1 SIMPLE t2 ref idx2 idx2 5 test.t1.id1 2 Using join buffer (BKA_UNIQUE, incremental buffers)
select t1.id1, sum(t2.id2) from t1 join t2 on t1.id1=t2.id1
where t1.d=3 group by t1.id1;
@@ -1772,7 +1772,7 @@ explain
select t1.id1 from t1 join t2 on t1.id1=t2.id1
where t1.d=3 and t2.id2 > 200 order by t1.id1;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref idx1 idx1 5 const 4 Using where; Using temporary; Using filesort; Using index
+1 SIMPLE t1 ref idx1 idx1 5 const 4 Using where; Using index; Using temporary; Using filesort
1 SIMPLE t2 ref idx2 idx2 5 test.t1.id1 2 Using where; Using join buffer (BKA_UNIQUE, incremental buffers)
select t1.id1 from t1 join t2 on t1.id1=t2.id1
where t1.d=3 and t2.id2 > 200 order by t1.id1;
=== modified file 'mysql-test/r/myisam_explain_non_select.result'
--- a/mysql-test/r/myisam_explain_non_select.result 2011-06-22 21:48:04 +0000
+++ b/mysql-test/r/myisam_explain_non_select.result 2011-07-02 13:53:01 +0000
@@ -2857,3 +2857,12 @@ id select_type table type possible_keys
INSERT INTO t2 SELECT * FROM t1;
SELECT * FROM t1 LEFT JOIN t2 ON t1.c1 = t2.c1;
DROP TABLE t1, t2;
+#73
+CREATE TABLE t1 (id INT);
+CREATE TABLE t2 (id INT);
+INSERT INTO t1 VALUES (1), (2);
+EXPLAIN SELECT * FROM t1 LEFT JOIN t2 USING(id) GROUP BY t1.id;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 system NULL NULL NULL NULL 0 const row not found
+1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort
+DROP TABLE t1,t2;
=== modified file 'mysql-test/suite/innodb/r/innodb_mysql.result'
--- a/mysql-test/suite/innodb/r/innodb_mysql.result 2011-06-02 13:39:03 +0000
+++ b/mysql-test/suite/innodb/r/innodb_mysql.result 2011-07-02 13:53:01 +0000
@@ -340,10 +340,10 @@ insert into t1 values ( 1,"e"),(2,"a"),(
alter table t1 drop primary key, add primary key (f2, f1);
explain select distinct f1 a, f1 b from t1;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 index NULL PRIMARY 5 NULL 4 Using temporary; Using index
+1 SIMPLE t1 index NULL PRIMARY 5 NULL 4 Using index; Using temporary
explain select distinct f1, f2 from t1;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range NULL PRIMARY 5 NULL 3 Using temporary; Using index for group-by
+1 SIMPLE t1 range NULL PRIMARY 5 NULL 3 Using index for group-by; Using temporary
drop table t1;
CREATE TABLE t1 (id int(11) NOT NULL PRIMARY KEY, name varchar(20),
INDEX (name));
@@ -993,7 +993,7 @@ key bkey
key_len 5
ref const
rows 16
-Extra Using where; Using filesort; Using index
+Extra Using where; Using index; Using filesort
SELECT * FROM t2 WHERE b=1 ORDER BY a;
a b c
1 1 1
@@ -1279,7 +1279,7 @@ key b
key_len 5
ref NULL
rows 3
-Extra Using filesort; Using index
+Extra Using index; Using filesort
SELECT * FROM t1 ORDER BY b ASC, a DESC;
a b
1 1
@@ -1295,7 +1295,7 @@ key b
key_len 5
ref NULL
rows 3
-Extra Using filesort; Using index
+Extra Using index; Using filesort
SELECT * FROM t1 ORDER BY b DESC, a ASC;
a b
2 2
=== modified file 'sql/opt_explain.cc'
--- a/sql/opt_explain.cc 2011-06-17 14:05:57 +0000
+++ b/sql/opt_explain.cc 2011-07-02 13:53:01 +0000
@@ -289,9 +289,10 @@ protected:
const JOIN_TAB *tab,
int quick_type,
uint keyno,
- bool need_tmp_table,
- bool need_sort,
String *str_extra);
+ void explain_tmptable_and_filesort(bool need_tmp_table_arg,
+ bool need_sort_arg,
+ String *str_extra);
};
@@ -302,8 +303,8 @@ protected:
class Explain_join : public Explain_table_base
{
private:
- const bool need_tmp_table; ///< add "Using temporary" to "extra" if true
- const bool need_order; ///< add "Using filesort"" to "extra" if true
+ bool need_tmp_table; ///< add "Using temporary" to "extra" if true
+ bool need_order; ///< add "Using filesort"" to "extra" if true
const bool distinct; ///< add "Distinct" string to "extra" column if true
uint tabnum; ///< current tab number in join->join_tab[]
@@ -713,8 +714,6 @@ void Explain_table_base::explain_extra_c
const JOIN_TAB *tab,
int quick_type,
uint keyno,
- bool need_tmp_table,
- bool need_sort,
String *str_extra)
{
if ((keyno != MAX_KEY && keyno == table->file->pushed_idx_cond_keyno &&
@@ -782,9 +781,15 @@ void Explain_table_base::explain_extra_c
*/
str_extra->append(STRING_WITH_LEN("; Using MRR"));
}
- if (need_tmp_table)
+}
+
+void Explain_table_base::explain_tmptable_and_filesort(bool need_tmp_table_arg,
+ bool need_sort_arg,
+ String *str_extra)
+{
+ if (need_tmp_table_arg)
str_extra->append(STRING_WITH_LEN("; Using temporary"));
- if (need_sort)
+ if (need_sort_arg)
str_extra->append(STRING_WITH_LEN("; Using filesort"));
}
@@ -967,10 +972,7 @@ bool Explain_join::explain_extra()
else if (select && select->quick)
keyno = select->quick->index;
- explain_extra_common(select, tab, quick_type, keyno,
- (tabnum == 0 && need_tmp_table),
- (tabnum == 0 && need_order),
- &str_extra);
+ explain_extra_common(select, tab, quick_type, keyno, &str_extra);
const TABLE_LIST *table_list= table->pos_in_table_list;
if (table_list->schema_table &&
@@ -1008,6 +1010,9 @@ bool Explain_join::explain_extra()
str_extra.append(STRING_WITH_LEN("; Using index"));
}
+ explain_tmptable_and_filesort(need_tmp_table, need_order, &str_extra);
+ need_tmp_table= need_order= false;
+
if (distinct && test_all_bits(used_tables,thd->used_tables))
str_extra.append(STRING_WITH_LEN("; Distinct"));
@@ -1163,8 +1168,8 @@ bool Explain_table::explain_extra()
const uint keyno= (select && select->quick) ? select->quick->index : key;
const int quick_type= (select && select->quick) ? select->quick->get_type()
: -1;
- explain_extra_common(select, NULL, quick_type, keyno, need_tmp_table,
- need_sort, &str_extra);
+ explain_extra_common(select, NULL, quick_type, keyno, &str_extra);
+ explain_tmptable_and_filesort(need_tmp_table, need_sort, &str_extra);
/* Skip initial "; "*/
const char *str= str_extra.ptr();
No bundle (reason: useless for push emails).
| Thread |
|---|
| • bzr push into mysql-trunk branch (gleb.shchepa:3374 to 3375) WL#4897 | Gleb Shchepa | 4 Jul |
