create table t1(a varchar(2) primary key) ENGINE=XENGINE; insert into t1 values(''); create index t1a1 on t1(a(1)); affected rows: 0 info: Records: 0 Duplicates: 0 Warnings: 0 drop table t1; create table t1(a int not null, b int, c char(10) not null, d varchar(20)) ENGINE=XENGINE; insert into t1 values (5,5,'oo','oo'),(4,4,'tr','tr'),(3,4,'ad','ad'),(2,3,'ak','ak'); commit; alter table t1 add index b (b), add index b (b); ERROR 42000: Duplicate key name 'b' alter table t1 add index (b,b); ERROR 42S21: Duplicate column name 'b' alter table t1 add index d2 (d); affected rows: 0 info: Records: 0 Duplicates: 0 Warnings: 0 show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) NOT NULL, `b` int(11) DEFAULT NULL, `c` char(10) COLLATE utf8mb4_general_ci NOT NULL, `d` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL, KEY `d2` (`d`) ) ENGINE=XENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci analyze table t1; explain select * from t1 force index(d2) order by d; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL index NULL d2 83 NULL # 100.00 NULL Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d` from `test`.`t1` FORCE INDEX (`d2`) order by `test`.`t1`.`d` select * from t1 force index (d2) order by d; a b c d 3 4 ad ad 2 3 ak ak 5 5 oo oo 4 4 tr tr alter table t1 add unique index (b); ERROR 23000: Duplicate entry '4' for key 'b' show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) NOT NULL, `b` int(11) DEFAULT NULL, `c` char(10) COLLATE utf8mb4_general_ci NOT NULL, `d` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL, KEY `d2` (`d`) ) ENGINE=XENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci alter table t1 add index (b); affected rows: 0 info: Records: 0 Duplicates: 0 Warnings: 0 show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) NOT NULL, `b` int(11) DEFAULT NULL, `c` char(10) COLLATE utf8mb4_general_ci NOT NULL, `d` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL, KEY `d2` (`d`), KEY `b` (`b`) ) ENGINE=XENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci alter table t1 add unique index (c), add index (d); affected rows: 0 info: Records: 0 Duplicates: 0 Warnings: 1 Warnings: Warning 1831 Duplicate index 'd' defined on the table 'test.t1'. This is deprecated and will be disallowed in a future release. show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) NOT NULL, `b` int(11) DEFAULT NULL, `c` char(10) COLLATE utf8mb4_general_ci NOT NULL, `d` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL, UNIQUE KEY `c` (`c`), KEY `d2` (`d`), KEY `b` (`b`), KEY `d` (`d`) ) ENGINE=XENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci analyze table t1; Table Op Msg_type Msg_text test.t1 analyze status OK explain select * from t1 force index(c) order by c; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL index NULL c 40 NULL # 100.00 NULL Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d` from `test`.`t1` FORCE INDEX (`c`) order by `test`.`t1`.`c` alter table t1 add primary key (a), drop index c; affected rows: 0 info: Records: 0 Duplicates: 0 Warnings: 0 show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) NOT NULL, `b` int(11) DEFAULT NULL, `c` char(10) COLLATE utf8mb4_general_ci NOT NULL, `d` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL, PRIMARY KEY (`a`), KEY `d2` (`d`), KEY `b` (`b`), KEY `d` (`d`) ) ENGINE=XENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci affected rows: 1 alter table t1 add primary key (c); ERROR 42000: Multiple primary key defined alter table t1 drop primary key, add primary key (b); ERROR 23000: Duplicate entry '4' for key 'PRIMARY' create unique index c on t1 (c); affected rows: 0 info: Records: 0 Duplicates: 0 Warnings: 0 show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) NOT NULL, `b` int(11) DEFAULT NULL, `c` char(10) COLLATE utf8mb4_general_ci NOT NULL, `d` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL, PRIMARY KEY (`a`), UNIQUE KEY `c` (`c`), KEY `d2` (`d`), KEY `b` (`b`), KEY `d` (`d`) ) ENGINE=XENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci analyze table t1; Table Op Msg_type Msg_text test.t1 analyze status OK explain select * from t1 force index(c) order by c; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL index NULL c 40 NULL # 100.00 NULL Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d` from `test`.`t1` FORCE INDEX (`c`) order by `test`.`t1`.`c` select * from t1 force index(c) order by c; a b c d 3 4 ad ad 2 3 ak ak 5 5 oo oo 4 4 tr tr alter table t1 drop index b, add index (b); affected rows: 0 info: Records: 0 Duplicates: 0 Warnings: 0 show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) NOT NULL, `b` int(11) DEFAULT NULL, `c` char(10) COLLATE utf8mb4_general_ci NOT NULL, `d` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL, PRIMARY KEY (`a`), UNIQUE KEY `c` (`c`), KEY `d2` (`d`), KEY `b` (`b`), KEY `d` (`d`) ) ENGINE=XENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci insert into t1 values(6,1,'ggg','ggg'); select * from t1; a b c d 2 3 ak ak 3 4 ad ad 4 4 tr tr 5 5 oo oo 6 1 ggg ggg select * from t1 force index(b) order by b; a b c d 6 1 ggg ggg 2 3 ak ak 3 4 ad ad 4 4 tr tr 5 5 oo oo select * from t1 force index(c) order by c; a b c d 3 4 ad ad 2 3 ak ak 6 1 ggg ggg 5 5 oo oo 4 4 tr tr select * from t1 force index(d) order by d; a b c d 3 4 ad ad 2 3 ak ak 6 1 ggg ggg 5 5 oo oo 4 4 tr tr analyze table t1; explain select * from t1 force index(b) order by b; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL index NULL b 5 NULL # 100.00 NULL Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d` from `test`.`t1` FORCE INDEX (`b`) order by `test`.`t1`.`b` explain select * from t1 force index(c) order by c; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL index NULL c 40 NULL # 100.00 NULL Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d` from `test`.`t1` FORCE INDEX (`c`) order by `test`.`t1`.`c` explain select * from t1 force index(d) order by d; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL index NULL d 83 NULL # 100.00 NULL Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d` from `test`.`t1` FORCE INDEX (`d`) order by `test`.`t1`.`d` show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) NOT NULL, `b` int(11) DEFAULT NULL, `c` char(10) COLLATE utf8mb4_general_ci NOT NULL, `d` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL, PRIMARY KEY (`a`), UNIQUE KEY `c` (`c`), KEY `d2` (`d`), KEY `b` (`b`), KEY `d` (`d`) ) ENGINE=XENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci drop table t1; create table t1(a int not null, b int, c char(10), d varchar(20), primary key (a)) ENGINE=XENGINE; insert into t1 values (1,1,'ab','ab'),(2,2,'ac','ac'),(3,3,'ad','ad'),(4,4,'afe','afe'); commit; alter table t1 add index (c(2)); affected rows: 0 info: Records: 0 Duplicates: 0 Warnings: 0 show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) NOT NULL, `b` int(11) DEFAULT NULL, `c` char(10) COLLATE utf8mb4_general_ci DEFAULT NULL, `d` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL, PRIMARY KEY (`a`), KEY `c` (`c`(2)) ) ENGINE=XENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci affected rows: 1 alter table t1 add unique index (d(10)); affected rows: 0 info: Records: 0 Duplicates: 0 Warnings: 0 show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) NOT NULL, `b` int(11) DEFAULT NULL, `c` char(10) COLLATE utf8mb4_general_ci DEFAULT NULL, `d` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL, PRIMARY KEY (`a`), UNIQUE KEY `d` (`d`(10)), KEY `c` (`c`(2)) ) ENGINE=XENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci affected rows: 1 insert into t1 values(5,1,'ggg','ggg'); analyze table t1; select * from t1; a b c d 1 1 ab ab 2 2 ac ac 3 3 ad ad 4 4 afe afe 5 1 ggg ggg select * from t1 force index(c) order by c; a b c d 1 1 ab ab 2 2 ac ac 3 3 ad ad 4 4 afe afe 5 1 ggg ggg select * from t1 force index(d) order by d; a b c d 1 1 ab ab 2 2 ac ac 3 3 ad ad 4 4 afe afe 5 1 ggg ggg explain select * from t1 order by b; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL # 100.00 Using filesort Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d` from `test`.`t1` order by `test`.`t1`.`b` explain select * from t1 force index(c) order by c; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL # 100.00 Using filesort Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d` from `test`.`t1` FORCE INDEX (`c`) order by `test`.`t1`.`c` explain select * from t1 force index(d) order by d; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL # 100.00 Using filesort Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d` from `test`.`t1` FORCE INDEX (`d`) order by `test`.`t1`.`d` show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) NOT NULL, `b` int(11) DEFAULT NULL, `c` char(10) COLLATE utf8mb4_general_ci DEFAULT NULL, `d` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL, PRIMARY KEY (`a`), UNIQUE KEY `d` (`d`(10)), KEY `c` (`c`(2)) ) ENGINE=XENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci alter table t1 drop index d; affected rows: 0 info: Records: 0 Duplicates: 0 Warnings: 0 insert into t1 values(8,9,'fff','fff'); select * from t1; a b c d 1 1 ab ab 2 2 ac ac 3 3 ad ad 4 4 afe afe 5 1 ggg ggg 8 9 fff fff select * from t1 force index(c) order by c; a b c d 1 1 ab ab 2 2 ac ac 3 3 ad ad 4 4 afe afe 8 9 fff fff 5 1 ggg ggg analyze table t1; explain select * from t1 order by b; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL # 100.00 Using filesort Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d` from `test`.`t1` order by `test`.`t1`.`b` explain select * from t1 force index(c) order by c; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL # 100.00 Using filesort Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d` from `test`.`t1` FORCE INDEX (`c`) order by `test`.`t1`.`c` explain select * from t1 order by d; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL # 100.00 Using filesort Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d` from `test`.`t1` order by `test`.`t1`.`d` show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) NOT NULL, `b` int(11) DEFAULT NULL, `c` char(10) COLLATE utf8mb4_general_ci DEFAULT NULL, `d` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL, PRIMARY KEY (`a`), KEY `c` (`c`(2)) ) ENGINE=XENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci drop table t1; create table t1(a int not null, b int, c char(10), d varchar(20), primary key (a)) ENGINE=XENGINE; insert into t1 values (1,1,'ab','ab'),(2,2,'ac','ac'),(3,2,'ad','ad'),(4,4,'afe','afe'); commit; alter table t1 add unique index (b,c); affected rows: 0 info: Records: 0 Duplicates: 0 Warnings: 0 insert into t1 values(8,9,'fff','fff'); select * from t1; a b c d 1 1 ab ab 2 2 ac ac 3 2 ad ad 4 4 afe afe 8 9 fff fff select * from t1 force index(b) order by b; a b c d 1 1 ab ab 2 2 ac ac 3 2 ad ad 4 4 afe afe 8 9 fff fff analyze table t1; explain select * from t1 force index(b) order by b; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL index NULL b 46 NULL # 100.00 NULL Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d` from `test`.`t1` FORCE INDEX (`b`) order by `test`.`t1`.`b` show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) NOT NULL, `b` int(11) DEFAULT NULL, `c` char(10) COLLATE utf8mb4_general_ci DEFAULT NULL, `d` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL, PRIMARY KEY (`a`), UNIQUE KEY `b` (`b`,`c`) ) ENGINE=XENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci alter table t1 add index (b,c); affected rows: 0 info: Records: 0 Duplicates: 0 Warnings: 0 insert into t1 values(11,11,'kkk','kkk'); select * from t1; a b c d 1 1 ab ab 2 2 ac ac 3 2 ad ad 4 4 afe afe 8 9 fff fff 11 11 kkk kkk select * from t1 force index(b) order by b; a b c d 1 1 ab ab 2 2 ac ac 3 2 ad ad 4 4 afe afe 8 9 fff fff 11 11 kkk kkk analyze table t1; explain select * from t1 force index(b) order by b; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL index NULL b 46 NULL # 100.00 NULL Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d` from `test`.`t1` FORCE INDEX (`b`) order by `test`.`t1`.`b` show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) NOT NULL, `b` int(11) DEFAULT NULL, `c` char(10) COLLATE utf8mb4_general_ci DEFAULT NULL, `d` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL, PRIMARY KEY (`a`), UNIQUE KEY `b` (`b`,`c`), KEY `b_2` (`b`,`c`) ) ENGINE=XENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci alter table t1 add unique index (c,d); affected rows: 0 info: Records: 0 Duplicates: 0 Warnings: 0 insert into t1 values(13,13,'yyy','aaa'); select * from t1; a b c d 1 1 ab ab 2 2 ac ac 3 2 ad ad 4 4 afe afe 8 9 fff fff 11 11 kkk kkk 13 13 yyy aaa select * from t1 force index(b) order by b; a b c d 1 1 ab ab 2 2 ac ac 3 2 ad ad 4 4 afe afe 8 9 fff fff 11 11 kkk kkk 13 13 yyy aaa select * from t1 force index(c) order by c; a b c d 1 1 ab ab 2 2 ac ac 3 2 ad ad 4 4 afe afe 8 9 fff fff 11 11 kkk kkk 13 13 yyy aaa analyze table t1; explain select * from t1 force index(b) order by b; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL index NULL b 46 NULL # 100.00 NULL Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d` from `test`.`t1` FORCE INDEX (`b`) order by `test`.`t1`.`b` explain select * from t1 force index(c) order by c; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL index NULL c 124 NULL # 100.00 NULL Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d` from `test`.`t1` FORCE INDEX (`c`) order by `test`.`t1`.`c` show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) NOT NULL, `b` int(11) DEFAULT NULL, `c` char(10) COLLATE utf8mb4_general_ci DEFAULT NULL, `d` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL, PRIMARY KEY (`a`), UNIQUE KEY `b` (`b`,`c`), UNIQUE KEY `c` (`c`,`d`), KEY `b_2` (`b`,`c`) ) ENGINE=XENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci drop table t1; create table t1(a int not null, b int, c char(10), d varchar(20), primary key (a))ENGINE=XENGINE default charset=utf8; Warnings: Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. insert into t1 values (1,1,'ab','ab'),(2,2,'ac','ac'),(3,2,'ad','ad'),(4,4,'afe','afe'); commit; alter table t1 add unique index (b); ERROR 23000: Duplicate entry '2' for key 'b' insert into t1 values(8,9,'fff','fff'); select * from t1; a b c d 1 1 ab ab 2 2 ac ac 3 2 ad ad 4 4 afe afe 8 9 fff fff show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) NOT NULL, `b` int(11) DEFAULT NULL, `c` char(10) DEFAULT NULL, `d` varchar(20) DEFAULT NULL, PRIMARY KEY (`a`) ) ENGINE=XENGINE DEFAULT CHARSET=utf8 alter table t1 add index (b); insert into t1 values(10,10,'kkk','iii'); select * from t1; a b c d 1 1 ab ab 2 2 ac ac 3 2 ad ad 4 4 afe afe 8 9 fff fff 10 10 kkk iii select * from t1 force index(b) order by b; a b c d 1 1 ab ab 2 2 ac ac 3 2 ad ad 4 4 afe afe 8 9 fff fff 10 10 kkk iii explain select * from t1 force index(b) order by b; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL index NULL b 5 NULL # 100.00 NULL Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d` from `test`.`t1` FORCE INDEX (`b`) order by `test`.`t1`.`b` show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) NOT NULL, `b` int(11) DEFAULT NULL, `c` char(10) DEFAULT NULL, `d` varchar(20) DEFAULT NULL, PRIMARY KEY (`a`), KEY `b` (`b`) ) ENGINE=XENGINE DEFAULT CHARSET=utf8 alter table t1 add unique index (c), add index (d); insert into t1 values(11,11,'aaa','mmm'); select * from t1; a b c d 1 1 ab ab 2 2 ac ac 3 2 ad ad 4 4 afe afe 8 9 fff fff 10 10 kkk iii 11 11 aaa mmm select * from t1 force index(b) order by b; a b c d 1 1 ab ab 2 2 ac ac 3 2 ad ad 4 4 afe afe 8 9 fff fff 10 10 kkk iii 11 11 aaa mmm select * from t1 force index(c) order by c; a b c d 11 11 aaa mmm 1 1 ab ab 2 2 ac ac 3 2 ad ad 4 4 afe afe 8 9 fff fff 10 10 kkk iii select * from t1 force index(d) order by d; a b c d 1 1 ab ab 2 2 ac ac 3 2 ad ad 4 4 afe afe 8 9 fff fff 10 10 kkk iii 11 11 aaa mmm explain select * from t1 force index(b) order by b; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL index NULL b 5 NULL # 100.00 NULL Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d` from `test`.`t1` FORCE INDEX (`b`) order by `test`.`t1`.`b` explain select * from t1 force index(c) order by c; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL index NULL c 31 NULL # 100.00 NULL Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d` from `test`.`t1` FORCE INDEX (`c`) order by `test`.`t1`.`c` explain select * from t1 force index(d) order by d; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL index NULL d 63 NULL # 100.00 NULL Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d` from `test`.`t1` FORCE INDEX (`d`) order by `test`.`t1`.`d` show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) NOT NULL, `b` int(11) DEFAULT NULL, `c` char(10) DEFAULT NULL, `d` varchar(20) DEFAULT NULL, PRIMARY KEY (`a`), UNIQUE KEY `c` (`c`), KEY `b` (`b`), KEY `d` (`d`) ) ENGINE=XENGINE DEFAULT CHARSET=utf8 check table t1; Table Op Msg_type Msg_text test.t1 check status OK drop table t1; create table t1(a int not null, b int) ENGINE=XENGINE; insert into t1 values (1,1),(1,1),(1,1),(1,1); alter table t1 add unique index (a); ERROR 23000: Duplicate entry '1' for key 'a' alter table t1 add unique index (b); ERROR 23000: Duplicate entry '1' for key 'b' alter table t1 add unique index (a), add unique index(b); ERROR 23000: Duplicate entry '1' for key 'a' show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) NOT NULL, `b` int(11) DEFAULT NULL ) ENGINE=XENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci drop table t1; create table t1(a int not null, c int not null,b int, primary key(a), unique key(c), key(b)) ENGINE=XENGINE; alter table t1 drop index c, drop index b; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) NOT NULL, `c` int(11) NOT NULL, `b` int(11) DEFAULT NULL, PRIMARY KEY (`a`) ) ENGINE=XENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci drop table t1; create table t1(a int not null, b int, primary key(a)) ENGINE=XENGINE; alter table t1 add index (b); show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) NOT NULL, `b` int(11) DEFAULT NULL, PRIMARY KEY (`a`), KEY `b` (`b`) ) ENGINE=XENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci drop table t1; create table t1(a int not null, b int, c char(10), d varchar(20), primary key (a)) ENGINE=XENGINE; insert into t1 values (1,1,'ab','ab'),(2,2,'ac','ac'),(3,3,'ac','ac'),(4,4,'afe','afe'),(5,4,'affe','affe'); alter table t1 add unique index (b), add unique index (c), add unique index (d); ERROR 23000: Duplicate entry 'ac' for key 'd' alter table t1 add unique index (c), add unique index (b), add index (d); ERROR 23000: Duplicate entry '4' for key 'b' show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) NOT NULL, `b` int(11) DEFAULT NULL, `c` char(10) COLLATE utf8mb4_general_ci DEFAULT NULL, `d` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL, PRIMARY KEY (`a`) ) ENGINE=XENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci drop table t1; create table t1(a int not null, b int not null, c int, primary key (a), key(c)) ENGINE=XENGINE; insert into t1 values (5,1,5),(4,2,4),(3,3,3),(2,4,2),(1,5,1); alter table t1 add unique index (b); affected rows: 0 info: Records: 0 Duplicates: 0 Warnings: 0 insert into t1 values (10,20,20),(11,19,19),(12,18,18),(13,17,17); show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) NOT NULL, `b` int(11) NOT NULL, `c` int(11) DEFAULT NULL, PRIMARY KEY (`a`), UNIQUE KEY `b` (`b`), KEY `c` (`c`) ) ENGINE=XENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci check table t1; Table Op Msg_type Msg_text test.t1 check status OK analyze table t1; explain select * from t1 force index(c) order by c; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL index NULL c 5 NULL # 100.00 NULL Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` FORCE INDEX (`c`) order by `test`.`t1`.`c` explain select * from t1 order by a; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL index NULL PRIMARY 4 NULL # 100.00 NULL Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` order by `test`.`t1`.`a` explain select * from t1 force index(b) order by b; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL index NULL b 4 NULL # 100.00 NULL Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` FORCE INDEX (`b`) order by `test`.`t1`.`b` select * from t1 order by a; a b c 1 5 1 2 4 2 3 3 3 4 2 4 5 1 5 10 20 20 11 19 19 12 18 18 13 17 17 select * from t1 force index(b) order by b; a b c 5 1 5 4 2 4 3 3 3 2 4 2 1 5 1 13 17 17 12 18 18 11 19 19 10 20 20 select * from t1 force index(c) order by c; a b c 1 5 1 2 4 2 3 3 3 4 2 4 5 1 5 13 17 17 12 18 18 11 19 19 10 20 20 drop table t1; create table t1(a int not null, b int not null) ENGINE=XENGINE; insert into t1 values (1,1); alter table t1 add primary key(b); affected rows: 0 info: Records: 0 Duplicates: 0 Warnings: 0 insert into t1 values (2,2); show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) NOT NULL, `b` int(11) NOT NULL, PRIMARY KEY (`b`) ) ENGINE=XENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci check table t1; Table Op Msg_type Msg_text test.t1 check status OK select * from t1; a b 1 1 2 2 analyze table t1; Table Op Msg_type Msg_text test.t1 analyze status OK explain select * from t1; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL # 100.00 NULL Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` explain select * from t1 order by a; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL # 100.00 Using filesort Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` order by `test`.`t1`.`a` explain select * from t1 order by b; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL index NULL PRIMARY 4 NULL # 100.00 NULL Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` order by `test`.`t1`.`b` checksum table t1; Table Checksum test.t1 582702641 drop table t1; create table t1(a int not null) ENGINE=XENGINE; insert into t1 values (1); alter table t1 add primary key(a); affected rows: 0 info: Records: 0 Duplicates: 0 Warnings: 0 insert into t1 values (2); show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) NOT NULL, PRIMARY KEY (`a`) ) ENGINE=XENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci check table t1; Table Op Msg_type Msg_text test.t1 check status OK commit; select * from t1; a 1 2 analyze table t1; Table Op Msg_type Msg_text test.t1 analyze status OK explain select * from t1; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL index NULL PRIMARY 4 NULL # 100.00 Using index Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` explain select * from t1 order by a; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL index NULL PRIMARY 4 NULL # 100.00 Using index Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` order by `test`.`t1`.`a` drop table t1; create table t480(a serial)engine=xengine; insert into t480 values(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(), (),(),(),(),(),(),(),(); insert into t480 select 0 from t480; insert into t480 select 0 from t480; insert into t480 select 0 from t480; insert into t480 select 0 from t480; # Bug#19163915 INNODB: DUPLICATE RECORDS COULD EXIST # WHEN SKIPPING SORT FOR CLUSTER INDEX SELECT @@innodb_sort_buffer_size; @@innodb_sort_buffer_size 1048576 create table t1(f1 int auto_increment not null, f2 char(200) not null, f3 char(200) not null, f4 char(200) not null,primary key(f1,f2,f3,f4)) charset latin1; insert into t1 select NULL,'aaa','bbb','ccc' from t480; insert into t1 values(106, 'aaa','bbb','cccc'); select count(*) from t1; count(*) 481 # Skip sort # Change PK from (f1,f2,f3,f4) to (f1,f2,f3) alter table t1 drop primary key, add primary key(f1,f2,f3); ERROR 23000: Duplicate entry '106-aaa-bbb' for key 'PRIMARY' select count(*) from t1; count(*) 481 drop table t1; create table t1(f1 int auto_increment not null, f2 char(200) not null, f3 char(200) not null, f4 char(200) not null,primary key(f1,f2,f3,f4)) charset latin1; insert into t1 select NULL,'aaa','bbb','ccc' from t480; insert into t1 values(108,'aaa','bbb','cccc'); select count(*) from t1; count(*) 481 alter table t1 drop primary key, add primary key(f1,f2,f3); ERROR 23000: Duplicate entry '108-aaa-bbb' for key 'PRIMARY' select count(*) from t1; count(*) 481 drop table t1, t480; # # Bug #19896922 SORTING SKIPPED WHEN PREFIX LENGTH OF THE PK # FIELD IS CHANGED # create table t1(a int not null, b varchar(30) not null, primary key (b(10), a)) ENGINE=XENGINE; insert into t1 values(0,'khdHps6UxW8Lwaoxa604oK6zkb'),(1,'khdHps6UxW8L'); select * from t1; a b 0 khdHps6UxW8Lwaoxa604oK6zkb 1 khdHps6UxW8L alter table t1 drop primary key, add primary key (b(18),a); select * from t1; a b 1 khdHps6UxW8L 0 khdHps6UxW8Lwaoxa604oK6zkb drop table t1; create table t1(a int not null, b varchar(30) not null, primary key (b(10), a)) ENGINE=XENGINE; insert into t1 values(0,'khdHps6UxW8Lwaoxa604oK6zkb'),(1,'khdHps6UtW8L'); select * from t1; a b 1 khdHps6UtW8L 0 khdHps6UxW8Lwaoxa604oK6zkb alter table t1 drop primary key, add primary key (b(8),a); select * from t1; a b 0 khdHps6UxW8Lwaoxa604oK6zkb 1 khdHps6UtW8L drop table t1; # # Bug #21103101 SORTING SKIPPED WHEN DROPPING THE SINGLE # COLUMN PRIMARY KEY # create table t1(f1 int not null, f2 int not null, primary key (f1), unique key(f1, f2))ENGINE=XENGINE; insert into t1 values(1,3), (2,2); alter table t1 drop column f1; drop table t1; create table t1(f1 int not null, f2 int not null, primary key (f1), unique key(f1, f2))ENGINE=XENGINE; insert into t1 values(1,3), (2,2); alter table t1 drop primary key, lock=none; ERROR 0A000: LOCK=NONE is not supported. Reason: With INPLACE DDL, XEngine only allows that DROP PRIMARY KEY is combined with ADD PRIMARY KEY. Try LOCK=SHARED. drop table t1; # # BUG#21612714 ALTER TABLE SORTING SKIPPED WHEN CHANGE PK AND DROP # LAST COLUMN OF OLD PK # create table t1(o1 int, o2 int, o3 int not null, primary key(o1,o2)) ENGINE=XENGINE; insert into t1 values(1,1,2),(1,2,1); alter table t1 drop primary key, add primary key(o1,o3), drop o2, lock=none; drop table t1; create table t1(o1 int, o2 int, o3 int not null, primary key(o1,o2)) ENGINE=XENGINE; insert into t1 values(1,1,2),(1,2,1); alter table t1 drop o1, drop o2, add primary key(o3), lock=none; drop table t1; create table t1(o1 int, o2 int, o3 int not null, primary key(o1,o2)) ENGINE=XENGINE; insert into t1 values(1,1,2),(1,2,1); alter table t1 drop primary key, add primary key(o1,o3), lock=none; drop table t1; create table t1(o1 int, o2 int, o3 int not null, primary key(o1,o2)) ENGINE=XENGINE; insert into t1 values(1,1,2),(1,2,1); alter table t1 drop primary key, add primary key(o3), lock=none; drop table t1; create table t1(o1 int, o2 int, o3 int not null, primary key(o1,o2)) ENGINE=XENGINE; insert into t1 values(1,1,2),(1,2,1); alter table t1 add column n1 int not null, drop primary key, add primary key(n1,o3), lock=none; drop table t1; create table t1(o1 int, o2 int, o3 int not null, primary key(o1,o2)) ENGINE=XENGINE; insert into t1 values(1,1,2),(2,2,1); alter table t1 add column n1 int not null, drop primary key, add primary key(o3,n1), lock=none; drop table t1; create table t1(o1 int, o2 int, o3 int not null, primary key(o1,o2)) ENGINE=XENGINE; insert into t1 values(1,2,2),(2,1,1); alter table t1 drop primary key, add primary key(o2, o1), lock=none; drop table t1; create table t1(o1 int, o2 int, o3 int not null, primary key(o1,o2)) ENGINE=XENGINE; insert into t1 values(1,2,2),(2,1,1); alter table t1 drop primary key, add primary key(o2), lock=none; drop table t1; create table t1(o1 int, o2 int, o3 int not null, primary key(o1,o2)) ENGINE=XENGINE; insert into t1 values(1,2,2),(2,1,1); alter table t1 drop primary key, add primary key(o2,o3), lock=none; drop table t1; create table t1(o1 int, o2 int, o3 int not null, primary key(o2,o1)) ENGINE=XENGINE; insert into t1 values(1,1,2),(2,1,1); alter table t1 drop primary key, add primary key(o2,o3), lock=none; drop table t1; create table t1(o1 int, o2 int, o3 int not null, primary key(o1,o2)) ENGINE=XENGINE; insert into t1 values(1,1,2),(1,2,1); alter table t1 drop primary key, add primary key(o1,o3,o2), lock=none; drop table t1; create table t1(o1 int, o2 int, o3 int not null, primary key(o1,o2)) ENGINE=XENGINE; insert into t1 values(1,2,2),(2,1,1); alter table t1 drop primary key, add primary key(o3,o1,o2), lock=none; drop table t1; create table t1(o1 int, o2 int, o3 int, primary key(o1,o2,o3)) ENGINE=XENGINE; insert into t1 values(1,1,2),(1,2,1); alter table t1 drop primary key, add primary key(o1,o3), lock=none; drop table t1; create table t1(o1 int, o2 int, o3 int, primary key(o1,o2,o3)) ENGINE=XENGINE; insert into t1 values(1,2,2),(2,1,1); alter table t1 drop o1, lock=none; drop table t1; create table t1(o1 int, o2 int, o3 int, primary key(o1,o2,o3)) ENGINE=XENGINE; insert into t1 values(1,1,2),(1,2,1); alter table t1 drop o2, lock=none; drop table t1; create table t1(o1 int, o2 int, o3 int, primary key(o1,o2,o3)) ENGINE=XENGINE; insert into t1 values(1,2,2),(2,1,1); alter table t1 drop o1, drop o2, lock=none; drop table t1; create table t1(o1 varchar(10), o2 int, o3 int not null, primary key(o1(2), o2)) ENGINE=XENGINE; insert into t1 values('abd', 1, 1), ('abc', 2, 2); alter table t1 drop primary key, add primary key(o1(3), o2), lock=none; drop table t1; create table t1(o1 varchar(10), o2 int, o3 int not null, primary key(o1(2), o2)) ENGINE=XENGINE; insert into t1 values('abd', 1, 1), ('abc', 2, 2); alter table t1 drop primary key, add primary key(o1, o2), lock=none; drop table t1; create table t1(o1 varchar(10), o2 int, o3 int not null, primary key(o1(2), o2)) ENGINE=XENGINE; insert into t1 values('abd', 1, 1), ('abc', 2, 2); alter table t1 drop primary key, add primary key(o1(3), o3), lock=none; drop table t1; create table t1(o1 varchar(10), o2 int, o3 int not null, primary key(o1(2), o2)) ENGINE=XENGINE; insert into t1 values('abd', 1, 1), ('abc', 2, 2); alter table t1 drop primary key, add primary key(o1, o3), lock=none; drop table t1; create table t1(o1 varchar(10), o2 int, o3 int not null, primary key(o1(3), o2)) ENGINE=XENGINE; insert into t1 values('abc', 2, 1), ('abd', 1, 2); alter table t1 drop primary key, add primary key(o1(2), o2), lock=none; drop table t1; create table t1(o1 varchar(10), o2 int, o3 int not null, primary key(o1, o2)) ENGINE=XENGINE; insert into t1 values('abc', 2, 1), ('abd', 1, 2); alter table t1 drop primary key, add primary key(o1(2), o2), lock=none; drop table t1; create table t1(o1 varchar(10), o2 int, o3 int not null, primary key(o1(3), o2)) ENGINE=XENGINE; insert into t1 values('abc', 2, 2), ('abd', 1, 1); alter table t1 drop primary key, add primary key(o1(2), o3), lock=none; drop table t1; create table t1(o1 varchar(10), o2 int, o3 int not null, primary key(o1, o2)) ENGINE=XENGINE; insert into t1 values('abc', 2, 2), ('abd', 1, 1); alter table t1 drop primary key, add primary key(o1(2), o3), lock=none; drop table t1; create table t1(o1 int, o2 varchar(10), o3 int, primary key(o1,o2(2),o3)) ENGINE=XENGINE; insert into t1 values(1, 'abd', 1), (1, 'abc', 2); alter table t1 drop primary key, add primary key(o1,o2(3)), lock=none; drop table t1; create table t1(o1 int, o2 varchar(10), o3 int, primary key(o1,o2(2),o3)) ENGINE=XENGINE; insert into t1 values(1, 'abd', 1), (1, 'abc', 2); alter table t1 drop primary key, add primary key(o1,o2), lock=none; drop table t1; create table t1(o1 varchar(10), o2 varchar(10), primary key(o1(3),o2(3))) ENGINE=XENGINE; insert into t1 values('abc', 'acd'), ('abd', 'abd'); alter table t1 drop primary key, add primary key(o1(2),o2(3)), lock=none; drop table t1; create table t1(o1 varchar(10), o2 varchar(10), primary key(o1,o2)) ENGINE=XENGINE; insert into t1 values('abc', 'acd'), ('abd', 'abd'); alter table t1 drop primary key, add primary key(o1(2),o2), lock=none; drop table t1; create table t1(o1 varchar(10), o2 varchar(10), primary key(o1(3),o2(3))) ENGINE=XENGINE; insert into t1 values('abd', 'acd'), ('acd', 'abd'); alter table t1 drop primary key, add primary key(o2(3),o1(3)), lock=none; drop table t1; create table t1(o1 varchar(10), o2 varchar(10), primary key(o1,o2)) ENGINE=XENGINE; insert into t1 values('abd', 'acd'), ('acd', 'abd'); alter table t1 drop primary key, add primary key(o2,o1), lock=none; drop table t1; CREATE TABLE t1(a INT NOT NULL, b INT, PRIMARY KEY (a ASC)) ENGINE=XENGINE; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) NOT NULL, `b` int(11) DEFAULT NULL, PRIMARY KEY (`a`) ) ENGINE=XENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci INSERT INTO t1 VALUES(5,5), (4,4), (3,4), (2,3); ALTER TABLE t1 DROP PRIMARY KEY, ADD PRIMARY KEY (a DESC); ERROR 42000: The storage engine for the table doesn't support descending indexes ALTER TABLE t1 DROP PRIMARY KEY, ADD PRIMARY KEY (a ASC); CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SELECT * FROM t1; a b 2 3 3 4 4 4 5 5 DROP TABLE t1; create table t1(o1 varchar(10), primary key(o1(2))) ENGINE=XENGINE; insert into t1 values('abd'), ('acd'); alter table t1 drop primary key, add primary key(o1(3)), lock=none; drop table t1; create table t1(o1 varchar(10), primary key(o1(2))) ENGINE=XENGINE; insert into t1 values('abd'), ('acd'); alter table t1 drop primary key, add primary key(o1), lock=none; drop table t1; create table t1(o1 varchar(10), primary key(o1(2))) ENGINE=XENGINE; insert into t1 values('abd'), ('acd'); alter table t1 add n1 int not null, drop primary key, add primary key(o1(3), n1), lock=none; drop table t1; create table t1(o1 varchar(10), primary key(o1(2))) ENGINE=XENGINE; insert into t1 values('abd'), ('acd'); alter table t1 add n1 int not null, drop primary key, add primary key(o1, n1), lock=none; drop table t1; create table t1(o1 varchar(10), o2 int not null, primary key(o1(2))) ENGINE=XENGINE; insert into t1 values('abd', 1), ('acd', 2); alter table t1 add n1 int not null, drop primary key, add primary key(o1(3), o2), lock=none; drop table t1; create table t1(o1 varchar(10), o2 int not null, primary key(o1(2))) ENGINE=XENGINE; insert into t1 values('abd', 1), ('acd', 2); alter table t1 add n1 int not null, drop primary key, add primary key(o1, o2), lock=none; drop table t1; create table t1(o1 varchar(10), primary key(o1(3))) ENGINE=XENGINE; insert into t1 values('abd'), ('acd'); alter table t1 drop primary key, add primary key(o1(2)), lock=none; drop table t1; create table t1(o1 varchar(10), primary key(o1)) ENGINE=XENGINE; insert into t1 values('abd'), ('acd'); alter table t1 drop primary key, add primary key(o1(2)), lock=none; drop table t1; create table t1(o1 varchar(10), o2 int, primary key(o1(3), o2)) ENGINE=XENGINE; insert into t1 values('abd', 1), ('acd', 2); alter table t1 drop primary key, add primary key(o1(2)), lock=none; drop table t1; create table t1(o1 varchar(10), o2 int, primary key(o1, o2)) ENGINE=XENGINE; insert into t1 values('abd', 1), ('acd', 2); alter table t1 drop primary key, add primary key(o1(2)), lock=none; drop table t1; create table t1(o1 varchar(10), o2 int, primary key(o1(3), o2)) ENGINE=XENGINE; insert into t1 values('abd', 1), ('acd', 2); alter table t1 add n1 int not null, drop primary key, add primary key(o1(2),n1), lock=none; drop table t1; create table t1(o1 varchar(10), o2 int, primary key(o1, o2)) ENGINE=XENGINE; insert into t1 values('abd', 1), ('acd', 2); alter table t1 add n1 int not null, drop primary key, add primary key(o1(2),n1), lock=none; drop table t1; create table t1(o1 varchar(10), o2 int, primary key(o1(3), o2)) ENGINE=XENGINE; insert into t1 values('abd', 1), ('acd', 2); alter table t1 add n1 int not null, drop primary key, add primary key(o1(3),n1), lock=none; drop table t1; create table t1(o1 varchar(10), o2 int, primary key(o1, o2)) ENGINE=XENGINE; insert into t1 values('abd', 1), ('acd', 2); alter table t1 add n1 int not null, drop primary key, add primary key(o1,n1), lock=none; drop table t1; create table t1(o1 int, o2 varchar(10), primary key(o1,o2(3))) ENGINE=XENGINE; insert into t1 values(1,'abd'), (2,'acd'); alter table t1 drop primary key, add primary key(o1,o2(2)), lock=none; drop table t1; create table t1(o1 int, o2 varchar(10), primary key(o1,o2)) ENGINE=XENGINE; insert into t1 values(1,'abd'), (2,'acd'); alter table t1 drop primary key, add primary key(o1,o2(2)), lock=none; drop table t1; create table t1(o1 int, o2 varchar(10), primary key(o1,o2(2))) ENGINE=XENGINE; insert into t1 values(1, 'abd'), (2, 'acd'); alter table t1 drop primary key, add primary key(o1,o2(3)), lock=none; drop table t1; create table t1(o1 int, o2 varchar(10), primary key(o1,o2(2))) ENGINE=XENGINE; insert into t1 values(1, 'abd'), (2, 'acd'); alter table t1 drop primary key, add primary key(o1,o2), lock=none; drop table t1; create table t1(o1 int, o2 varchar(10), o3 int, primary key(o1,o2(3),o3)) ENGINE=XENGINE; insert into t1 values(1, 'abd', 1), (2, 'acd', 2); alter table t1 drop primary key, add primary key(o1,o2(2)), lock=none; drop table t1; create table t1(o1 int, o2 varchar(10), o3 int, primary key(o1,o2,o3)) ENGINE=XENGINE; insert into t1 values(1, 'abd', 1), (2, 'acd', 2); alter table t1 drop primary key, add primary key(o1,o2(2)), lock=none; drop table t1; create table t1(o1 varchar(10), o2 varchar(10), primary key(o1(3),o2(3))) ENGINE=XENGINE; insert into t1 values('abd', 'acd'), ('acd', 'abd'); alter table t1 drop primary key, add primary key(o1(3),o2(2)), lock=none; drop table t1; create table t1(o1 varchar(10), o2 varchar(10), primary key(o1,o2)) ENGINE=XENGINE; insert into t1 values('abd', 'acd'), ('acd', 'abd'); alter table t1 drop primary key, add primary key(o1,o2(2)), lock=none; drop table t1; create table t1(o1 varchar(10), o2 varchar(10), primary key(o1(3),o2(2))) ENGINE=XENGINE; insert into t1 values('abd', 'acd'), ('acd', 'abd'); alter table t1 drop primary key, add primary key(o1(3),o2(3)), lock=none; drop table t1; create table t1(o1 varchar(10), o2 varchar(10), primary key(o1,o2(2))) ENGINE=XENGINE; insert into t1 values('abd', 'acd'), ('acd', 'abd'); alter table t1 drop primary key, add primary key(o1,o2), lock=none; drop table t1; create table t1(o1 varchar(10), o2 int, o3 varchar(10), primary key(o1(3),o2,o3(2))) ENGINE=XENGINE; insert into t1 values('abd', 1, 'acd'), ('acd', 2, 'abd'); alter table t1 drop primary key, add primary key(o1(3),o2,o3(3)), lock=none; drop table t1; create table t1(o1 varchar(10), o2 int, o3 varchar(10), primary key(o1,o2,o3(2))) ENGINE=XENGINE; insert into t1 values('abd', 1, 'acd'), ('acd', 2, 'abd'); alter table t1 drop primary key, add primary key(o1,o2,o3), lock=none; drop table t1; create table t1(o1 varchar(10), o2 int, o3 varchar(10), primary key(o1(3),o2,o3(3))) ENGINE=XENGINE; insert into t1 values('abd', 1, 'acd'), ('acd', 2, 'abd'); alter table t1 drop primary key, add primary key(o1(3),o2,o3(2)), lock=none; drop table t1; create table t1(o1 varchar(10), o2 int, o3 varchar(10), primary key(o1,o2,o3(3))) ENGINE=XENGINE; insert into t1 values('abd', 1, 'acd'), ('acd', 2, 'abd'); alter table t1 drop primary key, add primary key(o1,o2,o3(2)), lock=none; drop table t1; create table t1(o1 int, o2 int not null, primary key(o1)) ENGINE=XENGINE; insert into t1 values(1,1),(2,2); alter table t1 drop primary key, add primary key(o1,o2), lock=none; drop table t1; create table t1(o1 int, o2 int not null, primary key(o1)) ENGINE=XENGINE; insert into t1 values(1,1),(2,2); alter table t1 add n1 int not null, drop primary key, add primary key(o1,n1), lock=none; drop table t1; create table t1(o1 int, o2 int not null, primary key(o1)) ENGINE=XENGINE; insert into t1 values(1,1),(2,2); alter table t1 add n1 int not null, drop primary key, add primary key(n1,o1), lock=none; drop table t1; create table t1(o1 int, o2 int not null, primary key(o1)) ENGINE=XENGINE; insert into t1 values(1,1),(2,2); alter table t1 add n1 int not null, add n2 int not null, drop primary key, add primary key(n1,o1,n2), lock=none; drop table t1; create table t1(o1 int, o2 int not null, primary key(o1)) ENGINE=XENGINE; insert into t1 values(1,1),(2,2); alter table t1 add n1 int not null, add n2 int not null, drop primary key, add primary key(n1,n2,o1), lock=none; drop table t1; create table t1(o1 int, o2 int not null, primary key(o1)) ENGINE=XENGINE; insert into t1 values(1,1),(2,2); alter table t1 add n1 int not null, add n2 int not null, drop primary key, add primary key(o1,n1,n2), lock=none; drop table t1; create table t1(o1 int, o2 int not null, primary key(o1)) ENGINE=XENGINE; insert into t1 values(1,1),(2,2); alter table t1 add n1 int not null, drop primary key, add primary key(o1,o2,n1), lock=none; drop table t1; create table t1(o1 int, o2 int not null, primary key(o1)) ENGINE=XENGINE; insert into t1 values(1,1),(2,2); alter table t1 add n1 int not null, drop primary key, add primary key(o1,n1,o2), lock=none; drop table t1; create table t1(o1 int, o2 int not null, primary key(o1)) ENGINE=XENGINE; insert into t1 values(1,1),(2,2); alter table t1 add n1 int not null, drop primary key, add primary key(n1,o1,o2), lock=none; drop table t1; create table t1(o1 int, o2 int not null, o3 int not null, primary key(o1)) ENGINE=XENGINE; insert into t1 values(1,1,2),(2,2,1); alter table t1 drop primary key, add primary key(o1,o2,o3), lock=none; drop table t1; create table t1(o1 int, o2 int not null, o3 int not null, primary key(o1)) ENGINE=XENGINE; insert into t1 values(1,1,2),(2,2,1); alter table t1 drop primary key, add primary key(o1,o3,o2), lock=none; drop table t1; create table t1(o1 int, o2 int, o3 int, primary key(o1,o2,o3)) ENGINE=XENGINE; insert into t1 values(1,1,2),(2,2,1); alter table t1 drop primary key, add primary key(o1,o2), lock=none; drop table t1; create table t1(o1 int, o2 int, o3 int, o4 int not null, primary key(o1,o2,o3)) ENGINE=XENGINE; insert into t1 values(1,1,2,2),(2,2,1,1); alter table t1 add n1 int not null, drop primary key, add primary key(o1,o2,o3,o4), lock=none; drop table t1; create table t1(o1 int, o2 int, o3 int, primary key(o1,o2,o3)) ENGINE=XENGINE; insert into t1 values(1,1,2),(2,2,1); alter table t1 add n1 int not null, drop primary key, add primary key(o1,o2,n1), lock=none; drop table t1; create table t1(o1 int, o2 int, o3 int, primary key(o1,o2,o3)) ENGINE=XENGINE; insert into t1 values(1,1,2),(2,2,1); alter table t1 add n1 int not null, drop primary key, add primary key(o1,n1,o2), lock=none; drop table t1; create table t1(o1 int, o2 int, o3 int, primary key(o1,o2,o3)) ENGINE=XENGINE; insert into t1 values(1,1,2),(2,2,1); alter table t1 drop primary key, add primary key(o1), lock=none; drop table t1; CREATE TABLE t1(o1 int, o2 int, o3 int, PRIMARY KEY(o1), KEY(o2)) ENGINE=XENGINE; INSERT INTO t1 VALUES(1,1,2),(2,2,1); EXPLAIN SELECT * FROM t1 ORDER BY o2; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL # 100.00 Using filesort Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`o1` AS `o1`,`test`.`t1`.`o2` AS `o2`,`test`.`t1`.`o3` AS `o3` from `test`.`t1` order by `test`.`t1`.`o2` SELECT * FROM t1 ORDER BY o2; o1 o2 o3 1 1 2 2 2 1 CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SELECT * FROM t1; o1 o2 o3 1 1 2 2 2 1 EXPLAIN SELECT * FROM t1 ORDER BY o2; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL # 100.00 Using filesort Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`o1` AS `o1`,`test`.`t1`.`o2` AS `o2`,`test`.`t1`.`o3` AS `o3` from `test`.`t1` order by `test`.`t1`.`o2` SELECT * FROM t1 ORDER BY o2; o1 o2 o3 1 1 2 2 2 1 ALTER TABLE t1 DROP INDEX o2, ADD INDEX (o2 ASC); CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SELECT * FROM t1; o1 o2 o3 1 1 2 2 2 1 EXPLAIN SELECT * FROM t1 ORDER BY o2; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL # 100.00 Using filesort Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`o1` AS `o1`,`test`.`t1`.`o2` AS `o2`,`test`.`t1`.`o3` AS `o3` from `test`.`t1` order by `test`.`t1`.`o2` SELECT * FROM t1 ORDER BY o2; o1 o2 o3 1 1 2 2 2 1 DROP TABLE t1;