EVOLUTION-MANAGER

Edit File: cluster_key_part.result

set default_storage_engine='tokudb'; DROP TABLE IF EXISTS t; CREATE TABLE t (a INT NOT NULL AUTO_INCREMENT, b INT, PRIMARY KEY(a), CLUSTERING KEY b(b)) ENGINE=TokuDB PARTITION BY RANGE(a) (PARTITION p0 VALUES LESS THAN (100) ENGINE = TokuDB, PARTITION p2 VALUES LESS THAN MAXVALUE ENGINE = TokuDB); SHOW CREATE TABLE t; Table Create Table t CREATE TABLE `t` ( `a` int(11) NOT NULL AUTO_INCREMENT, `b` int(11) DEFAULT NULL, PRIMARY KEY (`a`), CLUSTERING KEY `b` (`b`) ) ENGINE=TokuDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (a) (PARTITION p0 VALUES LESS THAN (100) ENGINE = TokuDB, PARTITION p2 VALUES LESS THAN MAXVALUE ENGINE = TokuDB) */ DROP TABLE t; CREATE TABLE t (x INT NOT NULL, y INT NOT NULL, PRIMARY KEY(x)) PARTITION BY HASH(x) PARTITIONS 2; SHOW CREATE TABLE t; Table Create Table t CREATE TABLE `t` ( `x` int(11) NOT NULL, `y` int(11) NOT NULL, PRIMARY KEY (`x`) ) ENGINE=TokuDB DEFAULT CHARSET=latin1 PARTITION BY HASH (x) PARTITIONS 2 ALTER TABLE t ADD CLUSTERING KEY(y); SHOW CREATE TABLE t; Table Create Table t CREATE TABLE `t` ( `x` int(11) NOT NULL, `y` int(11) NOT NULL, PRIMARY KEY (`x`), CLUSTERING KEY `y` (`y`) ) ENGINE=TokuDB DEFAULT CHARSET=latin1 PARTITION BY HASH (x) PARTITIONS 2 DROP TABLE t; CREATE TABLE t1(a INT, b INT, c INT, d INT, PRIMARY KEY(a,b,c), CLUSTERING KEY(b), KEY (c)) ENGINE=TOKUDB PARTITION BY RANGE(a) (PARTITION p0 VALUES LESS THAN (5) ENGINE = TOKUDB, PARTITION p2 VALUES LESS THAN MAXVALUE ENGINE = TOKUDB); insert into t1 values (1,10,100,1000),(2,20,200,2000),(3,30,300,3000),(4,40,400,4000),(5,50,500,5000),(6,60,600,6000),(7,70,700,7000),(8,80,800,8000),(9,90,900,9000); explain select * from t1 where a > 5; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL NULL; Using where select * from t1 where a > 5; a b c d 6 60 600 6000 7 70 700 7000 8 80 800 8000 9 90 900 9000 explain select * from t1 where b > 30; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range b b 4 NULL NULL; Using where select * from t1 where b > 30; a b c d 4 40 400 4000 5 50 500 5000 6 60 600 6000 7 70 700 7000 8 80 800 8000 9 90 900 9000 explain select * from t1 where c > 750; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range c c 4 NULL NULL; Using where select * from t1 where c > 750; a b c d 8 80 800 8000 9 90 900 9000 explain select a from t1 where a > 8; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL NULL; Using where; Using index select a from t1 where a > 8; a 9 explain select a,b from t1 where b > 30; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range b b 4 NULL NULL; Using where; Using index select a,b from t1 where b > 30; a b 4 40 5 50 6 60 7 70 8 80 9 90 explain select a,b from t1 where c > 750; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index c b 4 NULL NULL; Using where; Using index select a,c from t1 where c > 750; a c 8 800 9 900 alter table t1 add clustering index bdca(b,d,c,a); insert into t1 values (10,10,10,10); alter table t1 drop index bdca; alter table t1 drop primary key; explain select * from t1 where a > 5; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL NULL; Using where select * from t1 where a > 5; a b c d 6 60 600 6000 7 70 700 7000 8 80 800 8000 9 90 900 9000 10 10 10 10 explain select * from t1 where b > 30; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range b b 4 NULL NULL; Using where select * from t1 where b > 30; a b c d 4 40 400 4000 5 50 500 5000 6 60 600 6000 7 70 700 7000 8 80 800 8000 9 90 900 9000 explain select * from t1 where c > 750; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range c c 4 NULL NULL; Using where select * from t1 where c > 750; a b c d 8 80 800 8000 9 90 900 9000 explain select b from t1 where b > 30; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range b b 4 NULL NULL; Using where; Using index select b from t1 where b > 30; b 40 50 60 70 80 90 explain select b from t1 where c > 750; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range c c 4 NULL NULL; Using where select c from t1 where c > 750; c 800 900 alter table t1 add e varchar(20); alter table t1 add primary key (a,b,c); explain select * from t1 where a > 5; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL NULL; Using where select * from t1 where a > 5; a b c d e 6 60 600 6000 NULL 7 70 700 7000 NULL 8 80 800 8000 NULL 9 90 900 9000 NULL 10 10 10 10 NULL explain select * from t1 where b > 30; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range b b 4 NULL NULL; Using where select * from t1 where b > 30; a b c d e 4 40 400 4000 NULL 5 50 500 5000 NULL 6 60 600 6000 NULL 7 70 700 7000 NULL 8 80 800 8000 NULL 9 90 900 9000 NULL explain select * from t1 where c > 750; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range c c 4 NULL NULL; Using where select * from t1 where c > 750; a b c d e 8 80 800 8000 NULL 9 90 900 9000 NULL explain select a from t1 where a > 8; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL NULL; Using where; Using index select a from t1 where a > 8; a 9 10 explain select a,b from t1 where b > 30; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range b b 4 NULL NULL; Using where; Using index select a,b from t1 where b > 30; a b 4 40 5 50 6 60 7 70 8 80 9 90 explain select a,b from t1 where c > 750; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range c c 4 NULL NULL; Using where; Using index select a,c from t1 where c > 750; a c 8 800 9 900 alter table t1 drop primary key; explain select * from t1 where a > 5; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL NULL; Using where select * from t1 where a > 5; a b c d e 6 60 600 6000 NULL 7 70 700 7000 NULL 8 80 800 8000 NULL 9 90 900 9000 NULL 10 10 10 10 NULL explain select * from t1 where b > 30; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range b b 4 NULL NULL; Using where select * from t1 where b > 30; a b c d e 4 40 400 4000 NULL 5 50 500 5000 NULL 6 60 600 6000 NULL 7 70 700 7000 NULL 8 80 800 8000 NULL 9 90 900 9000 NULL explain select * from t1 where c > 750; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range c c 4 NULL NULL; Using where select * from t1 where c > 750; a b c d e 8 80 800 8000 NULL 9 90 900 9000 NULL explain select b from t1 where b > 30; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range b b 4 NULL NULL; Using where; Using index select b from t1 where b > 30; b 40 50 60 70 80 90 explain select b from t1 where c > 750; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range c c 4 NULL NULL; Using where select c from t1 where c > 750; c 800 900 DROP TABLE t1; CREATE TABLE t1(a INT, b INT, c INT, d INT, PRIMARY KEY(a,b,c), CLUSTERING KEY(b), KEY (c)) ENGINE=TOKUDB PARTITION BY RANGE(b) (PARTITION p0 VALUES LESS THAN (50) ENGINE = TOKUDB, PARTITION p2 VALUES LESS THAN MAXVALUE ENGINE = TOKUDB); insert into t1 values (1,10,100,1000),(2,20,200,2000),(3,30,300,3000),(4,40,400,4000),(5,50,500,5000),(6,60,600,6000),(7,70,700,7000),(8,80,800,8000),(9,90,900,9000); explain select * from t1 where a > 5; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL NULL; Using where select * from t1 where a > 5; a b c d 6 60 600 6000 7 70 700 7000 8 80 800 8000 9 90 900 9000 explain select * from t1 where b > 30; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range b b 4 NULL NULL; Using where select * from t1 where b > 30; a b c d 4 40 400 4000 5 50 500 5000 6 60 600 6000 7 70 700 7000 8 80 800 8000 9 90 900 9000 explain select * from t1 where c > 750; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range c c 4 NULL NULL; Using where select * from t1 where c > 750; a b c d 8 80 800 8000 9 90 900 9000 explain select a from t1 where a > 8; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL NULL; Using where; Using index select a from t1 where a > 8; a 9 explain select a,b from t1 where b > 30; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range b b 4 NULL NULL; Using where; Using index select a,b from t1 where b > 30; a b 4 40 5 50 6 60 7 70 8 80 9 90 explain select a,b from t1 where c > 750; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index c b 4 NULL NULL; Using where; Using index select a,c from t1 where c > 750; a c 8 800 9 900 alter table t1 add clustering index bdca(b,d,c,a); insert into t1 values (10,10,10,10); alter table t1 drop index bdca; alter table t1 drop primary key; explain select * from t1 where a > 5; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL NULL; Using where select * from t1 where a > 5; a b c d 10 10 10 10 6 60 600 6000 7 70 700 7000 8 80 800 8000 9 90 900 9000 explain select * from t1 where b > 30; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range b b 4 NULL NULL; Using where select * from t1 where b > 30; a b c d 4 40 400 4000 5 50 500 5000 6 60 600 6000 7 70 700 7000 8 80 800 8000 9 90 900 9000 explain select * from t1 where c > 750; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range c c 4 NULL NULL; Using where select * from t1 where c > 750; a b c d 8 80 800 8000 9 90 900 9000 explain select b from t1 where b > 30; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range b b 4 NULL NULL; Using where; Using index select b from t1 where b > 30; b 40 50 60 70 80 90 explain select b from t1 where c > 750; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range c c 4 NULL NULL; Using where select c from t1 where c > 750; c 800 900 alter table t1 add e varchar(20); alter table t1 add primary key (a,b,c); explain select * from t1 where a > 5; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL NULL; Using where select * from t1 where a > 5; a b c d e 10 10 10 10 NULL 6 60 600 6000 NULL 7 70 700 7000 NULL 8 80 800 8000 NULL 9 90 900 9000 NULL explain select * from t1 where b > 30; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range b b 4 NULL NULL; Using where select * from t1 where b > 30; a b c d e 4 40 400 4000 NULL 5 50 500 5000 NULL 6 60 600 6000 NULL 7 70 700 7000 NULL 8 80 800 8000 NULL 9 90 900 9000 NULL explain select * from t1 where c > 750; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range c c 4 NULL NULL; Using where select * from t1 where c > 750; a b c d e 8 80 800 8000 NULL 9 90 900 9000 NULL explain select a from t1 where a > 8; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL NULL; Using where; Using index select a from t1 where a > 8; a 10 9 explain select a,b from t1 where b > 30; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range b b 4 NULL NULL; Using where; Using index select a,b from t1 where b > 30; a b 4 40 5 50 6 60 7 70 8 80 9 90 explain select a,b from t1 where c > 750; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index c b 4 NULL NULL; Using where; Using index select a,c from t1 where c > 750; a c 8 800 9 900 alter table t1 drop primary key; explain select * from t1 where a > 5; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL NULL; Using where select * from t1 where a > 5; a b c d e 10 10 10 10 NULL 6 60 600 6000 NULL 7 70 700 7000 NULL 8 80 800 8000 NULL 9 90 900 9000 NULL explain select * from t1 where b > 30; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range b b 4 NULL NULL; Using where select * from t1 where b > 30; a b c d e 4 40 400 4000 NULL 5 50 500 5000 NULL 6 60 600 6000 NULL 7 70 700 7000 NULL 8 80 800 8000 NULL 9 90 900 9000 NULL explain select * from t1 where c > 750; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range c c 4 NULL NULL; Using where select * from t1 where c > 750; a b c d e 8 80 800 8000 NULL 9 90 900 9000 NULL explain select b from t1 where b > 30; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range b b 4 NULL NULL; Using where; Using index select b from t1 where b > 30; b 40 50 60 70 80 90 explain select b from t1 where c > 750; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range c c 4 NULL NULL; Using where select c from t1 where c > 750; c 800 900 DROP TABLE t1; CREATE TABLE t1(a INT, b INT, c INT, d INT, PRIMARY KEY(a,b,c), CLUSTERING KEY(b), KEY (c)) ENGINE=TOKUDB PARTITION BY RANGE(c) (PARTITION p0 VALUES LESS THAN (500) ENGINE = TOKUDB, PARTITION p2 VALUES LESS THAN MAXVALUE ENGINE = TOKUDB); insert into t1 values (1,10,100,1000),(2,20,200,2000),(3,30,300,3000),(4,40,400,4000),(5,50,500,5000),(6,60,600,6000),(7,70,700,7000),(8,80,800,8000),(9,90,900,9000); explain select * from t1 where a > 5; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL NULL; Using where select * from t1 where a > 5; a b c d 6 60 600 6000 7 70 700 7000 8 80 800 8000 9 90 900 9000 explain select * from t1 where b > 30; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range b b 4 NULL NULL; Using where select * from t1 where b > 30; a b c d 4 40 400 4000 5 50 500 5000 6 60 600 6000 7 70 700 7000 8 80 800 8000 9 90 900 9000 explain select * from t1 where c > 750; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range c c 4 NULL NULL; Using where select * from t1 where c > 750; a b c d 8 80 800 8000 9 90 900 9000 explain select a from t1 where a > 8; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL NULL; Using where; Using index select a from t1 where a > 8; a 9 explain select a,b from t1 where b > 30; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range b b 4 NULL NULL; Using where; Using index select a,b from t1 where b > 30; a b 4 40 5 50 6 60 7 70 8 80 9 90 explain select a,b from t1 where c > 750; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index c b 4 NULL NULL; Using where; Using index select a,c from t1 where c > 750; a c 8 800 9 900 alter table t1 add clustering index bdca(b,d,c,a); insert into t1 values (10,10,10,10); alter table t1 drop index bdca; alter table t1 drop primary key; explain select * from t1 where a > 5; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL NULL; Using where select * from t1 where a > 5; a b c d 10 10 10 10 6 60 600 6000 7 70 700 7000 8 80 800 8000 9 90 900 9000 explain select * from t1 where b > 30; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range b b 4 NULL NULL; Using where select * from t1 where b > 30; a b c d 4 40 400 4000 5 50 500 5000 6 60 600 6000 7 70 700 7000 8 80 800 8000 9 90 900 9000 explain select * from t1 where c > 750; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range c c 4 NULL NULL; Using where select * from t1 where c > 750; a b c d 8 80 800 8000 9 90 900 9000 explain select b from t1 where b > 30; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range b b 4 NULL NULL; Using where; Using index select b from t1 where b > 30; b 40 50 60 70 80 90 explain select b from t1 where c > 750; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range c c 4 NULL NULL; Using where select c from t1 where c > 750; c 800 900 alter table t1 add e varchar(20); alter table t1 add primary key (a,b,c); explain select * from t1 where a > 5; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL NULL; Using where select * from t1 where a > 5; a b c d e 10 10 10 10 NULL 6 60 600 6000 NULL 7 70 700 7000 NULL 8 80 800 8000 NULL 9 90 900 9000 NULL explain select * from t1 where b > 30; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range b b 4 NULL NULL; Using where select * from t1 where b > 30; a b c d e 4 40 400 4000 NULL 5 50 500 5000 NULL 6 60 600 6000 NULL 7 70 700 7000 NULL 8 80 800 8000 NULL 9 90 900 9000 NULL explain select * from t1 where c > 750; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range c c 4 NULL NULL; Using where select * from t1 where c > 750; a b c d e 8 80 800 8000 NULL 9 90 900 9000 NULL explain select a from t1 where a > 8; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL NULL; Using where; Using index select a from t1 where a > 8; a 10 9 explain select a,b from t1 where b > 30; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range b b 4 NULL NULL; Using where; Using index select a,b from t1 where b > 30; a b 4 40 5 50 6 60 7 70 8 80 9 90 explain select a,b from t1 where c > 750; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index c b 4 NULL NULL; Using where; Using index select a,c from t1 where c > 750; a c 8 800 9 900 alter table t1 drop primary key; explain select * from t1 where a > 5; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL NULL; Using where select * from t1 where a > 5; a b c d e 10 10 10 10 NULL 6 60 600 6000 NULL 7 70 700 7000 NULL 8 80 800 8000 NULL 9 90 900 9000 NULL explain select * from t1 where b > 30; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range b b 4 NULL NULL; Using where select * from t1 where b > 30; a b c d e 4 40 400 4000 NULL 5 50 500 5000 NULL 6 60 600 6000 NULL 7 70 700 7000 NULL 8 80 800 8000 NULL 9 90 900 9000 NULL explain select * from t1 where c > 750; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range c c 4 NULL NULL; Using where select * from t1 where c > 750; a b c d e 8 80 800 8000 NULL 9 90 900 9000 NULL explain select b from t1 where b > 30; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range b b 4 NULL NULL; Using where; Using index select b from t1 where b > 30; b 40 50 60 70 80 90 explain select b from t1 where c > 750; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range c c 4 NULL NULL; Using where select c from t1 where c > 750; c 800 900 DROP TABLE t1;