diff options
Diffstat (limited to 'mysql-test/r')
| -rw-r--r-- | mysql-test/r/distinct.result | 12 | ||||
| -rw-r--r-- | mysql-test/r/group_by.result | 4 | ||||
| -rw-r--r-- | mysql-test/r/heap.result | 2 | ||||
| -rw-r--r-- | mysql-test/r/join.result | 4 | ||||
| -rw-r--r-- | mysql-test/r/key_diff.result | 2 | ||||
| -rw-r--r-- | mysql-test/r/myisam.result | 10 | ||||
| -rw-r--r-- | mysql-test/r/order_by.result | 6 | ||||
| -rw-r--r-- | mysql-test/r/select_safe.result | 23 |
8 files changed, 38 insertions, 25 deletions
diff --git a/mysql-test/r/distinct.result b/mysql-test/r/distinct.result index 5f4f7cced1e..53a20eeea0b 100644 --- a/mysql-test/r/distinct.result +++ b/mysql-test/r/distinct.result @@ -161,7 +161,7 @@ SELECT DISTINCT UserId FROM t1 WHERE UserId=22; UserId drop table t1; CREATE TABLE t1 (a int(10) unsigned not null primary key,b int(10) unsigned); -INSERT INTO t1 VALUES (1,1),(2,1); +INSERT INTO t1 VALUES (1,1),(2,1),(3,1),(4,1); CREATE TABLE t2 (a int(10) unsigned not null, key (A)); INSERT INTO t2 VALUES (1),(2); CREATE TABLE t3 (a int(10) unsigned, key(A), b text); @@ -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; table type possible_keys key key_len ref rows Extra -t1 index PRIMARY PRIMARY 4 NULL 2 Using index; Using temporary +t1 index PRIMARY PRIMARY 4 NULL 4 Using index; Using temporary t3 ref a a 5 t1.a 10 Using where; Using index; Distinct select distinct t1.a from t1,t3 where t1.a=t3.a; a @@ -200,16 +200,16 @@ select distinct 1 from t1,t3 where t1.a=t3.a; 1 explain SELECT distinct t1.a from t1; table type possible_keys key key_len ref rows Extra -t1 index NULL PRIMARY 4 NULL 2 Using index +t1 index NULL PRIMARY 4 NULL 4 Using index explain SELECT distinct t1.a from t1 order by a desc; table type possible_keys key key_len ref rows Extra -t1 index NULL PRIMARY 4 NULL 2 Using index +t1 index NULL PRIMARY 4 NULL 4 Using index explain SELECT t1.a from t1 group by a order by a desc; table type possible_keys key key_len ref rows Extra -t1 index NULL PRIMARY 4 NULL 2 Using index +t1 index NULL PRIMARY 4 NULL 4 Using index explain SELECT distinct t1.a from t1 order by a desc limit 1; table type possible_keys key key_len ref rows Extra -t1 index NULL PRIMARY 4 NULL 2 Using index +t1 index NULL PRIMARY 4 NULL 4 Using index explain SELECT distinct a from t3 order by a desc limit 2; table type possible_keys key key_len ref rows Extra t3 index NULL a 5 NULL 204 Using index diff --git a/mysql-test/r/group_by.result b/mysql-test/r/group_by.result index 94e1ce59585..67e6bec09f6 100644 --- a/mysql-test/r/group_by.result +++ b/mysql-test/r/group_by.result @@ -534,11 +534,11 @@ a b explain select t1.a,t2.b from t1,t2 where t1.a=t2.a group by t1.a,t2.b; table type possible_keys key key_len ref rows Extra t1 ALL NULL NULL NULL NULL 6 Using temporary; Using filesort -t2 ALL a NULL NULL NULL 4 Using where +t2 ALL a NULL NULL NULL 3 Using where explain select t1.a,t2.b from t1,t2 where t1.a=t2.a group by t1.a,t2.b ORDER BY NULL; table type possible_keys key key_len ref rows Extra t1 ALL NULL NULL NULL NULL 6 Using temporary -t2 ALL a NULL NULL NULL 4 Using where +t2 ALL a NULL NULL NULL 3 Using where drop table t1,t2; create table t1 (a int, b int); insert into t1 values (1, 4),(10, 40),(1, 4),(10, 43),(1, 4),(10, 41),(1, 4),(10, 43),(1, 4); diff --git a/mysql-test/r/heap.result b/mysql-test/r/heap.result index d8905085e34..a04ddf3f302 100644 --- a/mysql-test/r/heap.result +++ b/mysql-test/r/heap.result @@ -166,7 +166,7 @@ alter table t1 add column new_col char(1) not null, add key (btn,new_col), drop update t1 set new_col=btn; explain select * from t1 where btn="a"; table type possible_keys key key_len ref rows Extra -t1 ALL btn NULL NULL NULL 14 Using where +t1 ALL btn NULL NULL NULL 11 Using where explain select * from t1 where btn="a" and new_col="a"; table type possible_keys key key_len ref rows Extra t1 ref btn btn 11 const,const 10 Using where diff --git a/mysql-test/r/join.result b/mysql-test/r/join.result index e063b5c3e02..039b6e1cba3 100644 --- a/mysql-test/r/join.result +++ b/mysql-test/r/join.result @@ -104,9 +104,7 @@ KEY category (category,county,state) INSERT INTO t2 VALUES (3,2,11,12,5400,7800); INSERT INTO t2 VALUES (4,2,25,12,6500,11200); INSERT INTO t2 VALUES (5,1,37,6,10000,12000); -select a.id, b.category as catid, b.state as stateid, b.county as -countyid from t1 a, t2 b where (a.token = -'a71250b7ed780f6ef3185bfffe027983') and (a.count = b.id); +select a.id, b.category as catid, b.state as stateid, b.county as countyid from t1 a, t2 b ignore index (primary) where (a.token ='a71250b7ed780f6ef3185bfffe027983') and (a.count = b.id); id catid stateid countyid 27 2 12 11 28 2 12 11 diff --git a/mysql-test/r/key_diff.result b/mysql-test/r/key_diff.result index 4eaccc696f9..2d4bc19474f 100644 --- a/mysql-test/r/key_diff.result +++ b/mysql-test/r/key_diff.result @@ -36,7 +36,7 @@ a a a a explain select t1.*,t2.* from t1,t1 as t2 where t1.A=t2.B; table type possible_keys key key_len ref rows Extra t1 ALL a NULL NULL NULL 5 -t2 ALL b NULL NULL NULL 5 Using where +t2 ALL b NULL NULL NULL 4 Using where select t1.*,t2.* from t1,t1 as t2 where t1.A=t2.B order by binary t1.a,t2.a; a b a b A B a a diff --git a/mysql-test/r/myisam.result b/mysql-test/r/myisam.result index 9a653aff99e..c4368384bf8 100644 --- a/mysql-test/r/myisam.result +++ b/mysql-test/r/myisam.result @@ -335,12 +335,12 @@ t1 1 c_2 1 c A 5 NULL NULL YES BTREE t1 1 c_2 2 a A 5 NULL NULL BTREE explain select * from t1,t2 where t1.a=t2.a; table type possible_keys key key_len ref rows Extra -t1 ALL a NULL NULL NULL 5 -t2 ALL a NULL NULL NULL 2 Using where +t2 ALL a NULL NULL NULL 2 +t1 ALL a NULL NULL NULL 4 Using where explain select * from t1,t2 force index(a) where t1.a=t2.a; table type possible_keys key key_len ref rows Extra t2 ALL a NULL NULL NULL 2 -t1 ALL a NULL NULL NULL 5 Using where +t1 ALL a NULL NULL NULL 4 Using where explain select * from t1 force index(a),t2 force index(a) where t1.a=t2.a; table type possible_keys key key_len ref rows Extra t2 ALL a NULL NULL NULL 2 @@ -351,8 +351,8 @@ t2 ALL b NULL NULL NULL 2 t1 ref b b 5 t2.b 1 Using where explain select * from t1,t2 force index(c) where t1.a=t2.a; table type possible_keys key key_len ref rows Extra -t1 ALL a NULL NULL NULL 5 -t2 ALL NULL NULL NULL NULL 2 Using where +t2 ALL NULL NULL NULL NULL 2 +t1 ALL a NULL NULL NULL 4 Using where explain select * from t1 where a=0 or a=2; table type possible_keys key key_len ref rows Extra t1 ALL a NULL NULL NULL 5 Using where diff --git a/mysql-test/r/order_by.result b/mysql-test/r/order_by.result index ff2dd93311c..64fac8af872 100644 --- a/mysql-test/r/order_by.result +++ b/mysql-test/r/order_by.result @@ -307,17 +307,17 @@ table type possible_keys key key_len ref rows Extra t1 range a a 9 NULL 8 Using where; Using index explain select * from t1 where a = 2 and b >0 order by a desc,b desc; table type possible_keys key key_len ref rows Extra -t1 range a a 9 NULL 5 Using where; Using index +t1 range a a 9 NULL 4 Using where; Using index explain select * from t1 where a = 2 and b is null order by a desc,b desc; table type possible_keys key key_len ref rows Extra t1 ref a a 9 const,const 1 Using where; Using index; Using filesort explain select * from t1 where a = 2 and (b is null or b > 0) order by a desc,b desc; table type possible_keys key key_len ref rows Extra -t1 range a a 9 NULL 6 Using where; Using index +t1 range a a 9 NULL 5 Using where; Using index explain select * from t1 where a = 2 and b > 0 order by a desc,b desc; table type possible_keys key key_len ref rows Extra -t1 range a a 9 NULL 5 Using where; Using index +t1 range a a 9 NULL 4 Using where; Using index explain select * from t1 where a = 2 and b < 2 order by a desc,b desc; table type possible_keys key key_len ref rows Extra t1 range a a 9 NULL 2 Using where; Using index diff --git a/mysql-test/r/select_safe.result b/mysql-test/r/select_safe.result index 3303f19d9c7..830390ebe3c 100644 --- a/mysql-test/r/select_safe.result +++ b/mysql-test/r/select_safe.result @@ -1,6 +1,6 @@ drop table if exists t1; SET SQL_SAFE_UPDATES=1,SQL_SELECT_LIMIT=4, SQL_MAX_JOIN_SIZE=9; -create table t1 (a int auto_increment primary key, b char(20)); +create table t1 (a int auto_increment primary key, b char(20), key(b)); insert into t1 values(1,"test"); SELECT SQL_BUFFER_RESULT * from t1; a b @@ -13,7 +13,7 @@ a b 1 test 2 test2 update t1 set b="a" where a=1; -select 1 from t1,t1 as t2,t1 as t3,t1 as t4; +select 1 from t1,t1 as t2,t1 as t3; 1 1 1 @@ -22,11 +22,9 @@ select 1 from t1,t1 as t2,t1 as t3,t1 as t4; update t1 set b="a"; You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column update t1 set b="a" where b="test"; -You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column delete from t1; You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column delete from t1 where b="test"; -You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column delete from t1 where a+0=1; You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column select 1 from t1,t1 as t2,t1 as t3,t1 as t4,t1 as t5; @@ -59,5 +57,22 @@ a b 2 test2 4 a 5 a +SELECT @@MAX_SEEKS_FOR_KEY; +@@max_seeks_for_key +4294967295 +analyze table t1; +Table Op Msg_type Msg_text +test.t1 analyze status OK +insert into t1 values (null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"); +explain select * from t1,t1 as t2 where t1.b=t2.b; +table type possible_keys key key_len ref rows Extra +t1 ALL b NULL NULL NULL 21 +t2 ALL b NULL NULL NULL 16 Using where +set MAX_SEEKS_FOR_KEY=1; +explain select * from t1,t1 as t2 where t1.b=t2.b; +table type possible_keys key key_len ref rows Extra +t1 ALL b NULL NULL NULL 21 +t2 ref b b 21 t1.b 6 Using where +SET MAX_SEEKS_FOR_KEY=DEFAULT; drop table t1; SET SQL_SAFE_UPDATES=0,SQL_SELECT_LIMIT=DEFAULT, SQL_MAX_JOIN_SIZE=DEFAULT; |