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
|
SET @ORIG_PAUSE_BACKGROUND_WORK = @@ROCKSDB_PAUSE_BACKGROUND_WORK;
SET GLOBAL ROCKSDB_PAUSE_BACKGROUND_WORK = 1;
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (
a SET('N.America','S.America','Africa','Antarctica','Australia','Europe','Asia'),
b SET('test1','test2','test3','test4','test5'),
c SET('01','22','23','33','34','39','40','44','50','63','64') PRIMARY KEY
) 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 c A 1000 NULL NULL LSMTREE
INSERT INTO t1 (a,b,c) VALUES
('','test2,test3','01,34,44,23'),
('',5,2),
('N.America,Asia','test4,test2',''),
('Africa,Europe,Asia','test2,test3','01'),
('Antarctica','test3','34,44'),
('Asia','test5','50'),
('Europe,S.America','test1,','39');
Warnings:
Warning 1265 Data truncated for column 'b' at row 7
EXPLAIN SELECT c FROM t1 ORDER BY c;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 index NULL PRIMARY 2 NULL # Using index
SELECT c FROM t1 ORDER BY c;
c
01
22
39
34,44
01,23,34,44
50
EXPLAIN SELECT c FROM t1 IGNORE INDEX FOR ORDER BY (PRIMARY) ORDER BY c;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 index NULL PRIMARY 2 NULL # Using index; Using filesort
SELECT c FROM t1 IGNORE INDEX FOR ORDER BY (PRIMARY) ORDER BY c;
c
01
22
39
34,44
01,23,34,44
50
INSERT INTO t1 (a,b,c) VALUES ('Antarctica','test3','02');
ERROR 23000: Duplicate entry '22' for key 'PRIMARY'
INSERT INTO t1 (a,b,c) VALUES ('','test1','34,44');
ERROR 23000: Duplicate entry '34,44' for key 'PRIMARY'
DROP TABLE t1;
CREATE TABLE t1 (
a SET('N.America','S.America','Africa','Antarctica','Australia','Europe','Asia'),
b SET('test1','test2','test3','test4','test5'),
c SET('01','22','23','33','34','39','40','44','50','63','64'),
pk SET('1','2','3','4','5','6','7','8','9') PRIMARY KEY,
INDEX(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 pk A 1000 NULL NULL LSMTREE
t1 1 a 1 a A 500 NULL NULL YES LSMTREE
INSERT INTO t1 (a,b,c,pk) VALUES
('','test2,test3','01,34,44,23',1),
('',5,2,2),
('N.America,Asia','test4,test2','',3),
('Africa,Europe,Asia','test2,test3','01',4),
('Antarctica','test3','34,44',5),
('Asia','test5','50',6),
('Europe,S.America','test1,','39',7);
Warnings:
Warning 1265 Data truncated for column 'b' at row 7
EXPLAIN SELECT a FROM t1 WHERE FIND_IN_SET('Europe',a) > 0;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL # Using where
SELECT a FROM t1 WHERE FIND_IN_SET('Europe',a) > 0;
a
Africa,Europe,Asia
S.America,Europe
DROP TABLE t1;
CREATE TABLE t1 (
a SET('N.America','S.America','Africa','Antarctica','Australia','Europe','Asia'),
b SET('test1','test2','test3','test4','test5'),
c SET('01','22','23','33','34','39','40','44','50','63','64'),
pk SET('1','2','3','4','5','6','7','8','9') PRIMARY KEY,
UNIQUE INDEX b_a (b,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 pk A 1000 NULL NULL LSMTREE
t1 0 b_a 1 b A 500 NULL NULL YES LSMTREE
t1 0 b_a 2 a A 1000 NULL NULL YES LSMTREE
INSERT INTO t1 (a,b,c,pk) VALUES
('','test2,test3','01,34,44,23',1),
('',5,2,2),
('N.America,Asia','test4,test2','',3),
('Africa,Europe,Asia','test2,test3','01',4),
('Antarctica','test3','34,44',5),
('Asia','test5','50',6),
('Europe,S.America','test1,','39',7);
Warnings:
Warning 1265 Data truncated for column 'b' at row 7
EXPLAIN SELECT DISTINCT a, b FROM t1 ORDER BY b DESC, a;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL # Using temporary; Using filesort
SELECT DISTINCT a, b FROM t1 ORDER BY b DESC, a;
a b
test1,test3
test2,test3
Africa,Europe,Asia test2,test3
Antarctica test3
Asia test5
N.America,Asia test2,test4
S.America,Europe test1
DROP TABLE t1;
SET GLOBAL ROCKSDB_PAUSE_BACKGROUND_WORK = @ORIG_PAUSE_BACKGROUND_WORK;
|
