1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
|
drop table if exists t1;
CREATE TABLE t1 (a INT, b INT AUTO_INCREMENT, KEY ka(a), KEY kb(a,b), PRIMARY KEY(b)) ENGINE=rocksdb;
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` int(11) DEFAULT NULL,
`b` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`b`),
KEY `ka` (`a`),
KEY `kb` (`a`,`b`)
) ENGINE=ROCKSDB DEFAULT CHARSET=latin1
INSERT INTO t1 (a) VALUES (1);
INSERT INTO t1 (a) VALUES (3);
INSERT INTO t1 (a) VALUES (5);
ALTER TABLE t1 DROP INDEX ka, ALGORITHM=INPLACE;
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` int(11) DEFAULT NULL,
`b` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`b`),
KEY `kb` (`a`,`b`)
) ENGINE=ROCKSDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1
SELECT * FROM t1 FORCE INDEX(ka) where a > 1;
ERROR 42000: Key 'ka' doesn't exist in table 't1'
SELECT * FROM t1 FORCE INDEX(kb) where a > 1;
a b
3 2
5 3
SELECT * FROM t1 where b > 1;
a b
3 2
5 3
DROP TABLE t1;
CREATE TABLE t1 (a INT AUTO_INCREMENT, b INT, c INT, KEY kb(b), KEY kbc(b,c), KEY kc(c), PRIMARY KEY(a)) ENGINE=rocksdb;
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` int(11) NOT NULL AUTO_INCREMENT,
`b` int(11) DEFAULT NULL,
`c` int(11) DEFAULT NULL,
PRIMARY KEY (`a`),
KEY `kb` (`b`),
KEY `kbc` (`b`,`c`),
KEY `kc` (`c`)
) ENGINE=ROCKSDB DEFAULT CHARSET=latin1
INSERT INTO t1 (b,c) VALUES (1,2);
INSERT INTO t1 (b,c) VALUES (3,4);
INSERT INTO t1 (b,c) VALUES (5,6);
ALTER TABLE t1 DROP INDEX kb, DROP INDEX kbc, ALGORITHM=INPLACE;
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` int(11) NOT NULL AUTO_INCREMENT,
`b` int(11) DEFAULT NULL,
`c` int(11) DEFAULT NULL,
PRIMARY KEY (`a`),
KEY `kc` (`c`)
) ENGINE=ROCKSDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` int(11) NOT NULL AUTO_INCREMENT,
`b` int(11) DEFAULT NULL,
`c` int(11) DEFAULT NULL,
PRIMARY KEY (`a`),
KEY `kc` (`c`)
) ENGINE=ROCKSDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1
INSERT INTO t1 (b,c) VALUES (1,2);
INSERT INTO t1 (b,c) VALUES (3,4);
INSERT INTO t1 (b,c) VALUES (5,6);
SELECT * FROM t1 FORCE INDEX(kc) where c > 3;
a b c
2 3 4
3 5 6
5 3 4
6 5 6
SELECT * FROM t1 where b > 3;
a b c
3 5 6
6 5 6
DROP TABLE t1;
CREATE TABLE t1 (a INT, b INT, c INT, KEY kb(b), KEY kbc(b,c), KEY kc(c), PRIMARY KEY(a)) ENGINE=rocksdb;
SHOW INDEX IN t1;
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment
t1 0 PRIMARY 1 a A 0 NULL NULL LSMTREE
t1 1 kb 1 b A 0 NULL NULL YES LSMTREE
t1 1 kbc 1 b A 0 NULL NULL YES LSMTREE
t1 1 kbc 2 c A 0 NULL NULL YES LSMTREE
t1 1 kc 1 c A 0 NULL NULL YES LSMTREE
ALTER TABLE t1 DROP INDEX kb, DROP INDEX kbc, ALGORITHM=INPLACE;
SHOW INDEX IN t1;
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment
t1 0 PRIMARY 1 a A 0 NULL NULL LSMTREE
t1 1 kc 1 c A 0 NULL NULL YES LSMTREE
ALTER TABLE t1 DROP PRIMARY KEY;
SHOW INDEX IN t1;
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment
t1 1 kc 1 c A 0 NULL NULL YES LSMTREE
ALTER TABLE t1 DROP INDEX kc, ALGORITHM=INPLACE;
SHOW INDEX IN t1;
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment
DROP TABLE t1;
CREATE TABLE t1 (a INT AUTO_INCREMENT, b INT, c INT, PRIMARY KEY(a)) ENGINE=rocksdb;
ALTER TABLE t1 ADD UNIQUE INDEX kb(b);
ALTER TABLE t1 ADD UNIQUE INDEX kbc(b,c);
ALTER TABLE t1 ADD UNIQUE INDEX kc(c);
SHOW INDEX IN t1;
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment
t1 0 PRIMARY 1 a A 0 NULL NULL LSMTREE
t1 0 kb 1 b A 0 NULL NULL YES LSMTREE
t1 0 kbc 1 b A 0 NULL NULL YES LSMTREE
t1 0 kbc 2 c A 0 NULL NULL YES LSMTREE
t1 0 kc 1 c A 0 NULL NULL YES LSMTREE
ALTER TABLE t1 DROP INDEX kb, DROP INDEX kbc;
SHOW INDEX IN t1;
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment
t1 0 PRIMARY 1 a A 0 NULL NULL LSMTREE
t1 0 kc 1 c A 0 NULL NULL YES LSMTREE
INSERT INTO t1 (b,c) VALUES (1,2);
INSERT INTO t1 (b,c) VALUES (3,4);
INSERT INTO t1 (b,c) VALUES (5,6);
SELECT * FROM t1 FORCE INDEX(kc) where c > 3;
a b c
2 3 4
3 5 6
ALTER TABLE t1 DROP INDEX kc, ALGORITHM=INPLACE;
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` int(11) NOT NULL AUTO_INCREMENT,
`b` int(11) DEFAULT NULL,
`c` int(11) DEFAULT NULL,
PRIMARY KEY (`a`)
) ENGINE=ROCKSDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1
DROP TABLE t1;
CREATE TABLE IF NOT EXISTS t1 (col1 INT, col2 INT, col3 INT);
INSERT INTO t1 (col1,col2,col3) VALUES (1,2,3);
ALTER TABLE t1 ADD KEY idx ( col1, col2 );
ANALYZE TABLE t1;
Table Op Msg_type Msg_text
test.t1 analyze status OK
ALTER TABLE t1 DROP COLUMN col2;
ALTER TABLE t1 DROP COLUMN col3;
DROP TABLE t1;
CREATE TABLE IF NOT EXISTS t1 (col1 INT, col2 INT, col3 INT);
INSERT INTO t1 (col1,col2,col3) VALUES (1,2,3);
ALTER TABLE t1 ADD KEY idx ( col1, col2 );
ANALYZE TABLE t1;
Table Op Msg_type Msg_text
test.t1 analyze status OK
ALTER TABLE t1 DROP COLUMN col2;
ALTER TABLE t1 DROP COLUMN col3;
DROP TABLE t1;
|
