List:Commits« Previous MessageNext Message »
From:Gleb Shchepa Date:May 27 2011 10:13am
Subject:bzr commit into mysql-trunk branch (gleb.shchepa:3360) WL#4897
View as plain text  
#At file:///mnt/sda7/work/mysql-next-mr-opt-backporting-wl4897/ based on revid:gleb.shchepa@stripped

 3360 Gleb Shchepa	2011-05-27
      WL#4897: Add EXPLAIN INSERT/UPDATE/DELETE
      
      Minor refactoring and test result adjustments
     @ mysql-test/r/compress.result
        Minor changes ("extra" column message reordering) after WL4897.
     @ mysql-test/r/derived.result
        Minor changes ("extra" column message reordering) after WL4897.
     @ mysql-test/r/distinct.result
        Minor changes ("extra" column message reordering) after WL4897.
     @ mysql-test/r/gis.result
        Minor changes ("extra" column message reordering) after WL4897.
     @ mysql-test/r/group_by.result
        Minor changes ("extra" column message reordering) after WL4897.
     @ mysql-test/r/group_min_max.result
        Minor changes ("extra" column message reordering) after WL4897.
     @ mysql-test/r/null_key_all.result
        Minor changes ("extra" column message reordering) after WL4897.
     @ mysql-test/r/null_key_icp.result
        Minor changes ("extra" column message reordering) after WL4897.
     @ mysql-test/r/null_key_none.result
        Minor changes ("extra" column message reordering) after WL4897.
     @ mysql-test/r/order_by_all.result
        Minor changes ("extra" column message reordering) after WL4897.
     @ sql/opt_explain.cc
        Refactoring:
        * part of Explain_join::explain_key_and_len() has been replaced                                      
          with an additional call to explain_key_and_len_index()                                             
        * a few buffer allocations have been replaced with StringBuff                                        
          declarations
     @ sql/sql_string.h
        + The StringBuff template helper has been introduced

    modified:
      mysql-test/r/compress.result
      mysql-test/r/derived.result
      mysql-test/r/distinct.result
      mysql-test/r/gis.result
      mysql-test/r/group_by.result
      mysql-test/r/group_min_max.result
      mysql-test/r/null_key_all.result
      mysql-test/r/null_key_icp.result
      mysql-test/r/null_key_none.result
      mysql-test/r/order_by_all.result
      sql/opt_explain.cc
      sql/sql_string.h
=== modified file 'mysql-test/r/compress.result'
--- a/mysql-test/r/compress.result	2010-11-29 13:30:18 +0000
+++ b/mysql-test/r/compress.result	2011-05-27 10:13:26 +0000
@@ -603,11 +603,11 @@ select distinct fld3 from t2,t3 where t2
 fld3
 explain select t3.t2nr,fld3 from t2,t3 where t2.companynr = 34 and t2.fld1=t3.t2nr order by t3.t2nr,fld3;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t2	ALL	fld1	NULL	NULL	NULL	1199	Using where; Using temporary; Using filesort
+1	SIMPLE	t2	ALL	fld1	NULL	NULL	NULL	1199	Using where; Using filesort; Using temporary
 1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t2.fld1	1	Using where; Using index
 explain select * from t3 as t1,t3 where t1.period=t3.period order by t3.period;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	period	NULL	NULL	NULL	41810	Using temporary; Using filesort
+1	SIMPLE	t1	ALL	period	NULL	NULL	NULL	41810	Using filesort; Using temporary
 1	SIMPLE	t3	ref	period	period	4	test.t1.period	4181	
 explain select * from t3 as t1,t3 where t1.period=t3.period order by t3.period limit 10;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

=== modified file 'mysql-test/r/derived.result'
--- a/mysql-test/r/derived.result	2011-03-08 19:14:42 +0000
+++ b/mysql-test/r/derived.result	2011-05-27 10:13:26 +0000
@@ -164,7 +164,7 @@ insert into t1 values (1),(2),(3);
 describe select * from (select * from t1 group by id) bar;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	3	
-2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	3	Using temporary; Using filesort
+2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	3	Using filesort; Using temporary
 drop table t1;
 create table t1 (mat_id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, matintnum CHAR(6) NOT NULL, test MEDIUMINT UNSIGNED NULL);
 create table t2 (mat_id MEDIUMINT UNSIGNED NOT NULL, pla_id MEDIUMINT UNSIGNED NOT NULL);
@@ -190,13 +190,13 @@ explain SELECT STRAIGHT_JOIN d.pla_id, m
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	m2	ALL	NULL	NULL	NULL	NULL	9	
 1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	6	Using where; Using join buffer (BNL, incremental buffers)
-2	DERIVED	mp	ALL	NULL	NULL	NULL	NULL	9	Using temporary; Using filesort
+2	DERIVED	mp	ALL	NULL	NULL	NULL	NULL	9	Using filesort; Using temporary
 2	DERIVED	m1	eq_ref	PRIMARY	PRIMARY	3	test.mp.mat_id	1	
 explain SELECT STRAIGHT_JOIN d.pla_id, m2.test FROM t1 m2  INNER JOIN (SELECT mp.pla_id, MIN(m1.matintnum) AS matintnum FROM t2 mp INNER JOIN t1 m1 ON mp.mat_id=m1.mat_id GROUP BY mp.pla_id) d ON d.matintnum=m2.matintnum;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	m2	ALL	NULL	NULL	NULL	NULL	9	
 1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	6	Using where; Using join buffer (BNL, incremental buffers)
-2	DERIVED	mp	ALL	NULL	NULL	NULL	NULL	9	Using temporary; Using filesort
+2	DERIVED	mp	ALL	NULL	NULL	NULL	NULL	9	Using filesort; Using temporary
 2	DERIVED	m1	eq_ref	PRIMARY	PRIMARY	3	test.mp.mat_id	1	
 drop table t1,t2;
 SELECT a.x FROM (SELECT 1 AS x) AS a HAVING a.x = 1;
@@ -311,8 +311,8 @@ a	7.0000
 b	3.5000
 explain SELECT s.name, AVG(s.val) AS median FROM (SELECT x.name, x.val FROM t1 x, t1 y WHERE x.name=y.name GROUP BY x.name, x.val HAVING SUM(y.val <= x.val) >= COUNT(*)/2 AND SUM(y.val >= x.val) >= COUNT(*)/2) AS s GROUP BY s.name;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	3	Using temporary; Using filesort
-2	DERIVED	x	ALL	NULL	NULL	NULL	NULL	17	Using temporary; Using filesort
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	3	Using filesort; Using temporary
+2	DERIVED	x	ALL	NULL	NULL	NULL	NULL	17	Using filesort; Using temporary
 2	DERIVED	y	ALL	NULL	NULL	NULL	NULL	17	Using where; Using join buffer (BNL, incremental buffers)
 drop table t1;
 create table t2 (a int, b int, primary key (a));

=== modified file 'mysql-test/r/distinct.result'
--- a/mysql-test/r/distinct.result	2010-11-29 13:30:18 +0000
+++ b/mysql-test/r/distinct.result	2011-05-27 10:13:26 +0000
@@ -215,13 +215,13 @@ id	select_type	table	type	possible_keys
 1	SIMPLE	t3	index	NULL	a	5	NULL	40	Using index
 explain SELECT distinct a,b from t3 order by a+1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	204	Using temporary; Using filesort
+1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	204	Using filesort; Using temporary
 explain SELECT distinct a,b from t3 order by a limit 2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t3	index	NULL	a	5	NULL	2	Using temporary
 explain SELECT a,b from t3 group by a,b order by a+1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	204	Using temporary; Using filesort
+1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	204	Using filesort; Using temporary
 drop table t1,t2,t3,t4;
 CREATE TABLE t1 (name varchar(255));
 INSERT INTO t1 VALUES ('aa'),('ab'),('ac'),('ad'),('ae');
@@ -550,7 +550,7 @@ id	select_type	table	type	possible_keys
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	3	
 EXPLAIN SELECT DISTINCT a,b,d FROM t2 GROUP BY c,b,d;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	3	Using temporary; Using filesort
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	3	Using filesort; Using temporary
 CREATE UNIQUE INDEX c_b_unq ON t2 (c,b);
 EXPLAIN SELECT DISTINCT a,b,d FROM t2 GROUP BY c,b,d;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

=== modified file 'mysql-test/r/gis.result'
--- a/mysql-test/r/gis.result	2011-03-21 16:24:30 +0000
+++ b/mysql-test/r/gis.result	2011-05-27 10:13:26 +0000
@@ -394,7 +394,7 @@ Equals(g1.g, g2.g) as e, Disjoint(g1.g,
 Intersects(g1.g, g2.g) as i, Crosses(g1.g, g2.g) as r
 FROM gis_geometrycollection g1, gis_geometrycollection g2 ORDER BY first, second;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	SIMPLE	g1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using temporary; Using filesort
+1	SIMPLE	g1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using filesort; Using temporary
 1	SIMPLE	g2	ALL	NULL	NULL	NULL	NULL	2	100.00	Using join buffer (BNL, incremental buffers)
 Warnings:
 Note	1003	select `test`.`g1`.`fid` AS `first`,`test`.`g2`.`fid` AS `second`,mbrwithin(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `w`,mbrcontains(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `c`,mbroverlaps(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `o`,mbrequals(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `e`,mbrdisjoint(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `d`,st_touches(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `t`,mbrintersects(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `i`,st_crosses(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `r` from `test`.`gis_geometrycollection` `g1` join `test`.`gis_geometrycollection` `g2` order by `test`.`g1`.`fid`,`test`.`g2`.`fid`

=== modified file 'mysql-test/r/group_by.result'
--- a/mysql-test/r/group_by.result	2011-03-29 07:30:44 +0000
+++ b/mysql-test/r/group_by.result	2011-05-27 10:13:26 +0000
@@ -254,7 +254,7 @@ key (score)
 INSERT INTO t1 VALUES (1,1,1),(2,2,2),(2,1,1),(3,3,3),(4,3,3),(5,3,3),(6,3,3),(7,3,3);
 explain select userid,count(*) from t1 group by userid desc;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	8	Using temporary; Using filesort
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	8	Using filesort; Using temporary
 explain select userid,count(*) from t1 group by userid desc order by null;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	8	Using temporary
@@ -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 index; Using filesort
+1	SIMPLE	t1	index	NULL	score	3	NULL	8	Using filesort; Using index
 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 index; Using filesort
+1	SIMPLE	t1	index	NULL	score	3	NULL	8	Using filesort; Using index
 select sql_big_result score,count(*) from t1 group by score desc;
 score	count(*)
 3	5
@@ -537,7 +537,7 @@ a	b
 1	1
 explain select t1.a,t2.b from t1,t2 where t1.a=t2.a group by t1.a,t2.b;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	6	Using temporary; Using filesort
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	6	Using filesort; Using temporary
 1	SIMPLE	t2	ALL	a	NULL	NULL	NULL	4	Using where; Using join buffer (BNL, incremental buffers)
 explain select t1.a,t2.b from t1,t2 where t1.a=t2.a group by t1.a,t2.b ORDER BY NULL;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
@@ -614,7 +614,7 @@ userid	count(*)
 1	2
 EXPLAIN SELECT userid,count(*) FROM t1 GROUP BY userid DESC;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	8	Using temporary; Using filesort
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	8	Using filesort; Using temporary
 DROP TABLE t1;
 CREATE TABLE t1 (
 i int(11) default NULL,
@@ -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 index; Using filesort
+1	SIMPLE	t1	index	NULL	b	5	NULL	128	Using filesort; Using index
 SELECT b, sum(1) FROM t1 GROUP BY b;
 b	sum(1)
 0	6
@@ -1552,7 +1552,7 @@ id	select_type	table	type	possible_keys
 1	SIMPLE	t2	index	NULL	a	5	NULL	2	
 EXPLAIN SELECT a, SUM(b) FROM t2 IGNORE INDEX (a) GROUP BY a LIMIT 2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	4	Using temporary; Using filesort
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	4	Using filesort; Using temporary
 EXPLAIN SELECT 1 FROM t2 WHERE a IN
 (SELECT a FROM t1 USE INDEX (i2) IGNORE INDEX (i2));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
@@ -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 index; Using temporary; Using filesort
+1	SIMPLE	t2	index	NULL	a	10	NULL	6	Using filesort; Using index; Using temporary
 SELECT b from t2 GROUP BY b;
 b
 NULL
@@ -1841,7 +1841,7 @@ key	NULL
 key_len	NULL
 ref	NULL
 rows	2
-Extra	Using temporary; Using filesort
+Extra	Using filesort; Using temporary
 SELECT SUBSTRING(a,1,10), LENGTH(a) FROM t1 GROUP BY a;
 SUBSTRING(a,1,10)	LENGTH(a)
 1111111111	1300

=== modified file 'mysql-test/r/group_min_max.result'
--- a/mysql-test/r/group_min_max.result	2011-03-29 07:30:44 +0000
+++ b/mysql-test/r/group_min_max.result	2011-05-27 10:13:26 +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 index for group-by; Using temporary; Using filesort
+1	SIMPLE	t1	range	NULL	idx_t1_1	147	NULL	17	Using where; Using filesort; Using index for group-by; Using temporary
 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 index for group-by; Using temporary; Using filesort
+1	SIMPLE	t2	range	NULL	idx_t2_1	146	NULL	#	Using where; Using filesort; Using index for group-by; Using temporary
 select distinct a1,a2,b from t1;
 a1	a2	b
 a	a	a
@@ -1915,10 +1915,10 @@ concat(ord(min(b)),ord(max(b)))	min(b)	m
 9798	a	b
 explain select a1,a2,b,d,min(c),max(c) from t1 group by a1,a2,b;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	128	Using temporary; Using filesort
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	128	Using filesort; Using temporary
 explain select a1,a2,b,d from t1 group by a1,a2,b;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	128	Using temporary; Using filesort
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	128	Using filesort; Using temporary
 explain extended select a1,a2,min(b),max(b) from t1
 where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') and (c > 'a111') group by a1,a2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
@@ -1928,13 +1928,13 @@ Note	1003	select `test`.`t1`.`a1` AS `a1
 explain extended select a1,a2,b,min(c),max(c) from t1
 where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') and (d > 'xy2') group by a1,a2,b;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	SIMPLE	t1	ALL	idx_t1_0,idx_t1_1,idx_t1_2	NULL	NULL	NULL	128	50.78	Using where; Using temporary; Using filesort
+1	SIMPLE	t1	ALL	idx_t1_0,idx_t1_1,idx_t1_2	NULL	NULL	NULL	128	50.78	Using where; Using filesort; Using temporary
 Warnings:
 Note	1003	select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2`,`test`.`t1`.`b` AS `b`,min(`test`.`t1`.`c`) AS `min(c)`,max(`test`.`t1`.`c`) AS `max(c)` from `test`.`t1` where (((`test`.`t1`.`a1` = 'b') or (`test`.`t1`.`a1` = 'd') or (`test`.`t1`.`a1` = 'a') or (`test`.`t1`.`a1` = 'c')) and (`test`.`t1`.`a2` > 'a') and (`test`.`t1`.`d` > 'xy2')) group by `test`.`t1`.`a1`,`test`.`t1`.`a2`,`test`.`t1`.`b`
 explain extended select a1,a2,b,c from t1
 where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') and (d > 'xy2') group by a1,a2,b,c;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	SIMPLE	t1	ALL	idx_t1_0,idx_t1_1,idx_t1_2	NULL	NULL	NULL	128	50.78	Using where; Using temporary; Using filesort
+1	SIMPLE	t1	ALL	idx_t1_0,idx_t1_1,idx_t1_2	NULL	NULL	NULL	128	50.78	Using where; Using filesort; Using temporary
 Warnings:
 Note	1003	select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` where (((`test`.`t1`.`a1` = 'b') or (`test`.`t1`.`a1` = 'd') or (`test`.`t1`.`a1` = 'a') or (`test`.`t1`.`a1` = 'c')) and (`test`.`t1`.`a2` > 'a') and (`test`.`t1`.`d` > 'xy2')) group by `test`.`t1`.`a1`,`test`.`t1`.`a2`,`test`.`t1`.`b`,`test`.`t1`.`c`
 explain select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b = 'b') or (b < 'b') group by a1;
@@ -1960,7 +1960,7 @@ id	select_type	table	type	possible_keys
 1	SIMPLE	t2	index	NULL	idx_t2_1	163	NULL	164	Using where; Using index
 explain select a1, a2, b, c, min(d), max(d) from t1 group by a1,a2,b,c;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	128	Using temporary; Using filesort
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	128	Using filesort; Using temporary
 explain select a1,a2,count(a2) from t1 group by a1,a2,b;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	index	NULL	idx_t1_2	147	NULL	128	Using index
@@ -2333,7 +2333,7 @@ INSERT INTO t1 SELECT a + 2, b FROM t1;
 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	ALL	NULL	NULL	NULL	NULL	12	Using temporary; Using filesort
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	12	Using filesort; Using temporary
 SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a DESC;
 a	MIN(b)	MAX(b)
 4	1	3
@@ -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 index for group-by; Using temporary; Using filesort
+1	SIMPLE	t1	range	NULL	break_it	10	NULL	7	Using filesort; Using index for group-by; Using temporary
 SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a DESC;
 a	MIN(b)	MAX(b)
 4	1	3
@@ -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 index; Using filesort
+1	SIMPLE	t1	index	NULL	a	10	NULL	16	Using filesort; Using index
 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 index; Using temporary; Using filesort
+1	SIMPLE	t1_1	index	NULL	a	10	NULL	16	Using filesort; Using index; Using temporary
 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/null_key_all.result'
--- a/mysql-test/r/null_key_all.result	2010-11-02 18:47:47 +0000
+++ b/mysql-test/r/null_key_all.result	2011-05-27 10:13:26 +0000
@@ -25,7 +25,7 @@ id	select_type	table	type	possible_keys
 1	SIMPLE	t1	ref_or_null	a,b	a	9	const,const	2	Using where; Using index
 explain select * from t1 where (a is null or a = 7) and b=7 order by a;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ref_or_null	a,b	a	9	const,const	2	Using where; Using index; Using filesort
+1	SIMPLE	t1	ref_or_null	a,b	a	9	const,const	2	Using where; Using filesort; Using index
 explain select * from t1 where (a is null and b>a) or a is null and b=7 limit 2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ref	a,b	a	5	const	3	Using where; Using index

=== modified file 'mysql-test/r/null_key_icp.result'
--- a/mysql-test/r/null_key_icp.result	2010-11-02 18:47:47 +0000
+++ b/mysql-test/r/null_key_icp.result	2011-05-27 10:13:26 +0000
@@ -25,7 +25,7 @@ id	select_type	table	type	possible_keys
 1	SIMPLE	t1	ref_or_null	a,b	a	9	const,const	2	Using where; Using index
 explain select * from t1 where (a is null or a = 7) and b=7 order by a;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ref_or_null	a,b	a	9	const,const	2	Using where; Using index; Using filesort
+1	SIMPLE	t1	ref_or_null	a,b	a	9	const,const	2	Using where; Using filesort; Using index
 explain select * from t1 where (a is null and b>a) or a is null and b=7 limit 2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ref	a,b	a	5	const	3	Using where; Using index

=== modified file 'mysql-test/r/null_key_none.result'
--- a/mysql-test/r/null_key_none.result	2010-08-27 06:36:54 +0000
+++ b/mysql-test/r/null_key_none.result	2011-05-27 10:13:26 +0000
@@ -24,7 +24,7 @@ id	select_type	table	type	possible_keys
 1	SIMPLE	t1	ref_or_null	a,b	a	9	const,const	2	Using where; Using index
 explain select * from t1 where (a is null or a = 7) and b=7 order by a;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ref_or_null	a,b	a	9	const,const	2	Using where; Using index; Using filesort
+1	SIMPLE	t1	ref_or_null	a,b	a	9	const,const	2	Using where; Using filesort; Using index
 explain select * from t1 where (a is null and b>a) or a is null and b=7 limit 2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ref	a,b	a	5	const	3	Using where; Using index

=== modified file 'mysql-test/r/order_by_all.result'
--- a/mysql-test/r/order_by_all.result	2011-02-07 09:46:53 +0000
+++ b/mysql-test/r/order_by_all.result	2011-05-27 10:13:26 +0000
@@ -327,7 +327,7 @@ a	b	c
 1	NULL	NULL
 explain select * from t1 where a = 1 and b is null order by a desc, b desc;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ref	a	a	9	const,const	2	Using where; Using index; Using filesort
+1	SIMPLE	t1	ref	a	a	9	const,const	2	Using where; Using filesort; Using index
 select * from t1 where a = 1 and b is null order by a desc, b desc;
 a	b	c
 1	NULL	NULL
@@ -340,7 +340,7 @@ id	select_type	table	type	possible_keys
 1	SIMPLE	t1	range	a	a	9	NULL	5	Using where; Using index
 explain select * from t1 where a = 2 and b is null order by a desc,b desc;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ref	a	a	9	const,const	1	Using where; Using index; Using filesort
+1	SIMPLE	t1	ref	a	a	9	const,const	1	Using where; Using filesort; Using index
 explain select * from t1 where a = 2 and (b is null or b > 0) order by a
 desc,b desc;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
@@ -496,25 +496,25 @@ gid	sid	uid
 103853	5	250
 EXPLAIN select t1.gid, t2.sid, t3.uid from t3, t2, t1 where t2.gid = t1.gid and t2.uid = t3.uid order by t1.gid, t3.uid;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t2	ALL	PRIMARY,uid	NULL	NULL	NULL	6	Using temporary; Using filesort
+1	SIMPLE	t2	ALL	PRIMARY,uid	NULL	NULL	NULL	6	Using filesort; Using temporary
 1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	2	test.t2.uid	1	Using where; Using index
 1	SIMPLE	t1	eq_ref	PRIMARY	PRIMARY	4	test.t2.gid	1	Using index
 EXPLAIN SELECT t1.gid, t3.uid from t1, t3 where t1.gid = t3.uid order by t1.gid,t3.skr;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t3	ALL	PRIMARY	NULL	NULL	NULL	6	Using temporary; Using filesort
+1	SIMPLE	t3	ALL	PRIMARY	NULL	NULL	NULL	6	Using filesort; Using temporary
 1	SIMPLE	t1	eq_ref	PRIMARY	PRIMARY	4	test.t3.uid	1	Using where; Using index
 EXPLAIN SELECT t1.gid, t2.sid, t3.uid from t2, t1, t3 where t2.gid = t1.gid and t2.uid = t3.uid order by t3.uid, t1.gid;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t2	ALL	PRIMARY,uid	NULL	NULL	NULL	6	Using temporary; Using filesort
+1	SIMPLE	t2	ALL	PRIMARY,uid	NULL	NULL	NULL	6	Using filesort; Using temporary
 1	SIMPLE	t1	eq_ref	PRIMARY	PRIMARY	4	test.t2.gid	1	Using index
 1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	2	test.t2.uid	1	Using where; Using index
 EXPLAIN SELECT t1.gid, t3.uid from t1, t3 where t1.gid = t3.uid order by t3.skr,t1.gid;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t3	ALL	PRIMARY	NULL	NULL	NULL	6	Using temporary; Using filesort
+1	SIMPLE	t3	ALL	PRIMARY	NULL	NULL	NULL	6	Using filesort; Using temporary
 1	SIMPLE	t1	eq_ref	PRIMARY	PRIMARY	4	test.t3.uid	1	Using where; Using index
 EXPLAIN SELECT t1.gid, t3.uid from t1, t3 where t1.skr = t3.uid order by t1.gid,t3.skr;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	6	Using temporary; Using filesort
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	6	Using filesort; Using temporary
 1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	2	test.t1.skr	1	Using index condition
 drop table t1,t2,t3;
 CREATE TABLE t1 (
@@ -1004,7 +1004,7 @@ explain SELECT t1.b as a, t2.b as c FROM
 t1 LEFT JOIN t1 t2 ON (t1.a = t2.a AND t2.a = 2) 
 ORDER BY c;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	Using temporary; Using filesort
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	Using filesort; Using temporary
 1	SIMPLE	t2	const	PRIMARY	PRIMARY	4	const	1	Using where
 SELECT t2.b as c FROM 
 t1 LEFT JOIN t1 t2 ON (t1.a = t2.a AND t2.a = 2) 
@@ -1486,7 +1486,7 @@ SELECT d FROM t1, t2
 WHERE t2.b=14 AND t2.a=t1.a AND 5.1<t2.c AND t1.b='DE'
 ORDER BY t2.c LIMIT 1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ref	a,b	b	4	const	4	Using index condition; Using where; Using temporary; Using filesort
+1	SIMPLE	t1	ref	a,b	b	4	const	4	Using index condition; Using where; Using filesort; Using temporary
 1	SIMPLE	t2	ref	a,b,c	a	40	test.t1.a,const	11	Using index condition
 SELECT d FROM t1, t2
 WHERE t2.b=14 AND t2.a=t1.a AND 5.1<t2.c AND t1.b='DE'
@@ -2408,7 +2408,7 @@ INSERT INTO t1 VALUES (29, 8, 1), (30, 8
 INSERT INTO t1 VALUES (33, 9, 1), (34, 9, 2), (35, 9, 3), (36, 9, 4);
 EXPLAIN SELECT id1 FROM t1 WHERE id2 = 4 ORDER BY id1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ref	id2_j_id1	id2_j_id1	4	const	4	Using where; Using index; Using filesort
+1	SIMPLE	t1	ref	id2_j_id1	id2_j_id1	4	const	4	Using where; Using filesort; Using index
 SELECT id1 FROM t1 WHERE id2 = 4 ORDER BY id1;
 id1
 13
@@ -2488,19 +2488,19 @@ INSERT INTO t2 SELECT a+4, b FROM t2;
 EXPLAIN 
 SELECT * FROM t1 FORCE INDEX FOR ORDER BY (a), t2 WHERE t1.a < 2 ORDER BY t1.a;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	range	a	a	5	NULL	2	Using index condition; Using MRR; Using temporary; Using filesort
+1	SIMPLE	t1	range	a	a	5	NULL	2	Using index condition; Using MRR; Using filesort; Using temporary
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	10	Using join buffer (BNL, incremental buffers)
 # should have "using filesort"
 EXPLAIN 
 SELECT * FROM t1 USE INDEX FOR ORDER BY (a), t2 WHERE t1.a < 2 ORDER BY t1.a;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	range	a	a	5	NULL	2	Using index condition; Using MRR; Using temporary; Using filesort
+1	SIMPLE	t1	range	a	a	5	NULL	2	Using index condition; Using MRR; Using filesort; Using temporary
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	10	Using join buffer (BNL, incremental buffers)
 # should have "using filesort"
 EXPLAIN 
 SELECT * FROM t1 FORCE INDEX FOR JOIN (a), t2 WHERE t1.a < 2 ORDER BY t1.a;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	range	a	a	5	NULL	2	Using index condition; Using MRR; Using temporary; Using filesort
+1	SIMPLE	t1	range	a	a	5	NULL	2	Using index condition; Using MRR; Using filesort; Using temporary
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	10	Using join buffer (BNL, incremental buffers)
 DROP TABLE t1, t2;
 #
@@ -2521,7 +2521,7 @@ GROUP BY t1.a
 ORDER by c
 LIMIT 2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	index	NULL	a	8	NULL	10	Using index; Using temporary; Using filesort
+1	SIMPLE	t1	index	NULL	a	8	NULL	10	Using filesort; Using index; Using temporary
 1	SIMPLE	t2	eq_ref	PRIMARY	PRIMARY	4	test.t1.b	1	Using where
 DROP TABLE t1, t2;
 #
@@ -2536,7 +2536,7 @@ CREATE TABLE t1 (a INT,KEY (a));
 INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
 EXPLAIN SELECT DISTINCT a,1 FROM t1 WHERE a <> 1 ORDER BY a DESC;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	index	a	a	5	NULL	10	Using where; Using index; Using filesort
+1	SIMPLE	t1	index	a	a	5	NULL	10	Using where; Using filesort; Using index
 SELECT DISTINCT a,1 FROM t1 WHERE a <> 1 ORDER BY a DESC;
 a	1
 10	1

=== modified file 'sql/opt_explain.cc'
--- a/sql/opt_explain.cc	2011-05-25 14:16:06 +0000
+++ b/sql/opt_explain.cc	2011-05-27 10:13:26 +0000
@@ -252,13 +252,8 @@ protected:
   {
     DBUG_ASSERT(select && select->quick);
 
-    char buff_key[512];
-    String str_key(buff_key, sizeof(buff_key), cs);
-    str_key.length(0);
-
-    char buff_key_len[512];
-    String str_key_len(buff_key_len, sizeof(buff_key_len), cs);
-    str_key_len.length(0);
+    StringBuff<512> str_key(cs);
+    StringBuff<512> str_key_len(cs);
 
     select->quick->add_keys_and_lengths(&str_key, &str_key_len);
     return col_key.set(thd, str_key) || col_key_len.set(thd, str_key_len);
@@ -266,11 +261,15 @@ protected:
   bool explain_key_and_len_index(int key)
   {
     DBUG_ASSERT(key != MAX_KEY);
+    return explain_key_and_len_index(key, table->key_info[key].key_length);
+  }
+  bool explain_key_and_len_index(int key, uint key_length)
+  {
+    DBUG_ASSERT(key != MAX_KEY);
 
     KEY *key_info= table->key_info + key;
     char buff_key_len[512];
-    int length= longlong2str(key_info->key_length, buff_key_len, 10) -
-                             buff_key_len;
+    int length= longlong2str(key_length, buff_key_len, 10) - buff_key_len;
     return col_key.set(thd, key_info->name) ||
            col_key_len.set(thd, buff_key_len, length);
   }
@@ -721,9 +720,7 @@ bool Explain_table_base::explain_possibl
   if (usable_keys.is_clear_all())
     return false;
 
-  char buff_possible_keys[512];
-  String str_possible_keys(buff_possible_keys, sizeof(buff_possible_keys), cs);
-  str_possible_keys.length(0);
+  StringBuff<512> str_possible_keys(cs);
 
   for (uint j= 0 ; j < table->s->keys ; j++)
   {
@@ -793,16 +790,8 @@ bool Explain_join::explain_join_type()
 
 bool Explain_join::explain_key_and_len()
 {
-  char buff_key_len[512];
   if (tab->ref.key_parts)
-  {
-    KEY *key_info= table->key_info + tab->ref.key;
-    uint length;
-    col_key.set(thd, key_info->name);
-    length= longlong2str(tab->ref.key_length, buff_key_len, 10) - buff_key_len;
-    col_key_len.set(thd, buff_key_len, length);
-    return col_key.nil() || col_key_len.nil();
-  }
+    return explain_key_and_len_index(tab->ref.key, tab->ref.key_length);
   else if (tab->type == JT_NEXT)
     return explain_key_and_len_index(tab->index);
   else if (tab->select && tab->select->quick)
@@ -813,13 +802,8 @@ bool Explain_join::explain_key_and_len()
     if (table_list->schema_table &&
         table_list->schema_table->i_s_requested_object & OPTIMIZE_I_S_TABLE)
     {
-      char buff_key[512];
-      String str_key(buff_key, sizeof(buff_key), cs);
-      str_key.length(0);
-
-      String str_key_len(buff_key_len, sizeof(buff_key_len), cs);
-      str_key_len.length(0);
-
+      StringBuff<512> str_key(cs);
+      StringBuff<512> str_key_len(cs);
       const char *f_name;
       int f_idx;
       if (table_list->has_db_lookup_value)
@@ -848,9 +832,7 @@ bool Explain_join::explain_ref()
 {
   if (tab->ref.key_parts)
   {
-    char buff_ref[512];
-    String str_ref(buff_ref, sizeof(buff_ref), cs);
-    str_ref.length(0);
+    StringBuff<512> str_ref(cs);
     for (store_key **ref= tab->ref.key_copy; *ref; ref++)
     {
       if (str_ref.length())
@@ -904,9 +886,7 @@ bool Explain_join::explain_rows_and_filt
 
 bool Explain_join::explain_extra()
 {
-  char buff_extra[512];
-  String str_extra(buff_extra, sizeof(buff_extra), cs);
-  str_extra.length(0);
+  StringBuff<512> str_extra(cs);
 
   bool key_read= table->key_read;
   if ((tab->type == JT_NEXT || tab->type == JT_CONST) &&
@@ -1132,10 +1112,7 @@ bool Explain_table::explain_rows_and_fil
 
 bool Explain_table::explain_extra()
 {
-  char buff_extra[512];
-  String str_extra(buff_extra, sizeof(buff_extra), cs);
-  str_extra.length(0);
-
+  StringBuff<512> str_extra(cs);
   uint keyno= (select && select->quick) ? select->quick->index : key;
   int quick_type= (select && select->quick) ? select->quick->get_type() : -1;
 

=== modified file 'sql/sql_string.h'
--- a/sql/sql_string.h	2011-03-22 11:44:40 +0000
+++ b/sql/sql_string.h	2011-05-27 10:13:26 +0000
@@ -422,6 +422,32 @@ public:
   }
 };
 
+
+/**
+  String class wrapper with a preallocated buffer of size buff_sz
+
+  This class allows to replace sequences of:
+     char buff[12345];
+     String str(buff, sizeof(buff));
+     str.length(0);
+  with a simple equivalent declaration:
+     StringBuff<12345> str;
+*/
+
+template<size_t buff_sz>
+class StringBuff : public String
+{
+  char buff[buff_sz];
+
+public:
+  StringBuff() : String(buff, buff_sz, &my_charset_bin) { length(0); }
+  explicit StringBuff(const CHARSET_INFO *cs) : String(buff, buff_sz, cs)
+  {
+    length(0);
+  }
+};
+
+
 static inline bool check_if_only_end_space(const CHARSET_INFO *cs, char *str, 
                                            char *end)
 {


Attachment: [text/bzr-bundle] bzr/gleb.shchepa@oracle.com-20110527101326-tsqf966kku49gpp4.bundle
Thread
bzr commit into mysql-trunk branch (gleb.shchepa:3360) WL#4897Gleb Shchepa27 May