diff options
Diffstat (limited to 'mysql-test/r/innodb.result')
| -rw-r--r-- | mysql-test/r/innodb.result | 61 |
1 files changed, 37 insertions, 24 deletions
diff --git a/mysql-test/r/innodb.result b/mysql-test/r/innodb.result index 69268f1d5c5..8592393fd42 100644 --- a/mysql-test/r/innodb.result +++ b/mysql-test/r/innodb.result @@ -1,4 +1,5 @@ drop table if exists t1,t2,t3; +drop database if exists mysqltest; create table t1 (id int unsigned not null auto_increment, code tinyint unsigned not null, name char(20) not null, primary key (id), key (code), unique (name)) type=innodb; insert into t1 (code, name) values (1, 'Tim'), (1, 'Monty'), (2, 'David'), (2, 'Erik'), (3, 'Sasha'), (3, 'Jeremy'), (4, 'Matt'); select id, code, name from t1 order by id; @@ -138,6 +139,15 @@ id parent_id level 1008 102 2 1010 102 2 1015 102 2 +explain select level from t1 where level=1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref level level 1 const # Using where; Using index +explain select level,id from t1 where level=1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref level level 1 const # Using where; Using index +explain select level,id,parent_id from t1 where level=1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref level level 1 const # Using where select level,id from t1 where level=1; level id 1 1002 @@ -156,7 +166,7 @@ level id parent_id 1 1007 101 optimize table t1; Table Op Msg_type Msg_text -test.t1 optimize error The storage engine for the table doesn't support optimize +test.t1 optimize status OK show keys from t1; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment t1 0 PRIMARY 1 id A # NULL NULL BTREE @@ -180,7 +190,7 @@ create table t1 (a int) type=innodb; insert into t1 values (1), (2); optimize table t1; Table Op Msg_type Msg_text -test.t1 optimize error The storage engine for the table doesn't support optimize +test.t1 optimize status OK delete from t1 where a = 1; select * from t1; a @@ -202,7 +212,7 @@ Table Op Msg_type Msg_text test.t1 analyze status OK show keys from t1; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment -t1 1 skr 1 a A 3 NULL NULL YES BTREE +t1 1 skr 1 a A # NULL NULL YES BTREE drop table t1; create table t1 (a int,b varchar(20),key(a)) type=innodb; insert into t1 values (1,""), (2,"testing"); @@ -345,12 +355,12 @@ CREATE TABLE t1 (a int not null, b int not null,c int not null, key(a),primary key(a,b), unique(c),key(a),unique(b)); show index from t1; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment -t1 0 PRIMARY 1 a A NULL NULL NULL BTREE -t1 0 PRIMARY 2 b A 0 NULL NULL BTREE -t1 0 c 1 c A 0 NULL NULL BTREE -t1 0 b 1 b A 0 NULL NULL BTREE -t1 1 a 1 a A NULL NULL NULL BTREE -t1 1 a_2 1 a A NULL NULL NULL BTREE +t1 0 PRIMARY 1 a A # NULL NULL BTREE +t1 0 PRIMARY 2 b A # NULL NULL BTREE +t1 0 c 1 c A # NULL NULL BTREE +t1 0 b 1 b A # NULL NULL BTREE +t1 1 a 1 a A # NULL NULL BTREE +t1 1 a_2 1 a A # NULL NULL BTREE drop table t1; create table t1 (col1 int not null, col2 char(4) not null, primary key(col1)); alter table t1 type=innodb; @@ -586,6 +596,9 @@ id parent_id level 1009 102 2 1025 102 2 1016 102 2 +explain select level from t1 where level=1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref level level 1 const # Using where; Using index select level,id from t1 where level=1; level id 1 1004 @@ -712,10 +725,10 @@ world 2 hello 1 optimize table t1; Table Op Msg_type Msg_text -test.t1 optimize error The storage engine for the table doesn't support optimize +test.t1 optimize status OK show keys from t1; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment -t1 0 PRIMARY 1 a A 2 NULL NULL BTREE +t1 0 PRIMARY 1 a A # NULL NULL BTREE drop table t1; create table t1 (i int, j int ) TYPE=innodb; insert into t1 values (1,2); @@ -747,7 +760,7 @@ create table t1 (a int primary key,b int, c int, d int, e int, f int, g int, h insert into t1 values (1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1); explain select * from t1 where a > 0 and a < 50; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 1 Using where +1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL # Using where drop table t1; create table t1 (id int NOT NULL,id2 int NOT NULL,id3 int NOT NULL,dummy1 char(30),primary key (id,id2),index index_id3 (id3)) type=innodb; insert into t1 values (0,0,0,'ABCDEFGHIJ'),(2,2,2,'BCDEFGHIJK'),(1,1,1,'CDEFGHIJKL'); @@ -788,13 +801,13 @@ id id3 UNLOCK TABLES; DROP TABLE t1; create table t1 (a char(20), unique (a(5))) type=innodb; -ERROR HY000: Incorrect sub part key. The used key part isn't a string, the used length is longer than the key part or the storage engine doesn't support unique sub keys +drop table t1; create table t1 (a char(20), index (a(5))) type=innodb; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` char(20) default NULL, - KEY `a` (`a`) + KEY `a` (`a`(5)) ) TYPE=InnoDB CHARSET=latin1 drop table t1; create temporary table t1 (a int not null auto_increment, primary key(a)) type=innodb; @@ -881,28 +894,28 @@ create table t1 (a int not null, b int not null, c int not null, primary key (a) insert into t1 values (3,3,3),(1,1,1),(2,2,2),(4,4,4); explain select * from t1 order by a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL PRIMARY 4 NULL 4 +1 SIMPLE t1 index NULL PRIMARY 4 NULL # explain select * from t1 order by b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL b 4 NULL 4 +1 SIMPLE t1 index NULL b 4 NULL # explain select * from t1 order by c; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using filesort +1 SIMPLE t1 ALL NULL NULL NULL NULL # Using filesort explain select a from t1 order by a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL PRIMARY 4 NULL 4 Using index +1 SIMPLE t1 index NULL PRIMARY 4 NULL # Using index explain select b from t1 order by b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL b 4 NULL 4 Using index +1 SIMPLE t1 index NULL b 4 NULL # Using index explain select a,b from t1 order by b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL b 4 NULL 4 Using index +1 SIMPLE t1 index NULL b 4 NULL # Using index explain select a,b from t1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL b 4 NULL 4 Using index +1 SIMPLE t1 index NULL b 4 NULL # Using index explain select a,b,c from t1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 4 +1 SIMPLE t1 ALL NULL NULL NULL NULL # drop table t1; create table t1 (t int not null default 1, key (t)) type=innodb; desc t1; @@ -1249,11 +1262,11 @@ count(*) 29267 explain select * from t1 where c between 1 and 10000; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range c c 5 NULL 1 Using where +1 SIMPLE t1 range c c 5 NULL # Using where update t1 set c=a; explain select * from t1 where c between 1 and 10000; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL c NULL NULL NULL 29537 Using where +1 SIMPLE t1 ALL c NULL NULL NULL # Using where drop table t1,t2; create table t1 (id int primary key auto_increment, fk int, index index_fk (fk)) type=innodb; insert into t1 (id) values (null),(null),(null),(null),(null); |