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
|
SET @ORIG_PAUSE_BACKGROUND_WORK = @@ROCKSDB_PAUSE_BACKGROUND_WORK;
SET GLOBAL ROCKSDB_PAUSE_BACKGROUND_WORK = 1;
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (
d1 DECIMAL(10,2) PRIMARY KEY,
d2 DECIMAL(60,10),
n1 NUMERIC,
n2 NUMERIC(65,4)
) 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 d1 A 1000 NULL NULL LSMTREE
INSERT INTO t1 (d1,d2,n1,n2) VALUES
(10.22,60.12345,123456,14.3456),
(10.0,60.12345,123456,14),
(11.14,15,123456,13),
(100,100,1,2),
(0,0,0,0),
(4540424564.23,3343303441.0,12,13),
(15,17,23,100000);
Warnings:
Warning 1264 Out of range value for column 'd1' at row 6
EXPLAIN SELECT d1 FROM t1 ORDER BY d1 DESC;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 index NULL PRIMARY 5 NULL # Using index
SELECT d1 FROM t1 ORDER BY d1 DESC;
d1
99999999.99
100.00
15.00
11.14
10.22
10.00
0.00
EXPLAIN SELECT d1 FROM t1 IGNORE INDEX FOR ORDER BY (PRIMARY) ORDER BY d1 DESC;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 index NULL PRIMARY 5 NULL # Using index; Using filesort
SELECT d1 FROM t1 IGNORE INDEX FOR ORDER BY (PRIMARY) ORDER BY d1 DESC;
d1
99999999.99
100.00
15.00
11.14
10.22
10.00
0.00
DROP TABLE t1;
CREATE TABLE t1 (
d1 DECIMAL(10,2),
d2 DECIMAL(60,10),
n1 NUMERIC,
n2 NUMERIC(65,4),
pk NUMERIC PRIMARY KEY,
UNIQUE INDEX n1_n2 (n1,n2)
) 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 n1_n2 1 n1 A 500 NULL NULL YES LSMTREE
t1 0 n1_n2 2 n2 A 1000 NULL NULL YES LSMTREE
INSERT INTO t1 (d1,d2,n1,n2,pk) VALUES
(10.22,60.12345,123456,14.3456,1),
(10.0,60.12345,123456,14,2),
(11.14,15,123456,13,3),
(100,100,1,2,4),
(0,0,0,0,5),
(4540424564.23,3343303441.0,12,13,6),
(15,17,23,100000,7);
Warnings:
Warning 1264 Out of range value for column 'd1' at row 6
EXPLAIN SELECT DISTINCT n1+n2 FROM t1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 index NULL n1_n2 37 NULL # Using index; Using temporary
SELECT DISTINCT n1+n2 FROM t1;
n1+n2
0.0000
100023.0000
123469.0000
123470.0000
123470.3456
25.0000
3.0000
DROP TABLE t1;
CREATE TABLE t1 (
d1 DECIMAL(10,2),
d2 DECIMAL(60,10),
n1 NUMERIC,
n2 NUMERIC(65,4),
pk DECIMAL(20,10) PRIMARY KEY,
INDEX (d2)
) 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 d2 1 d2 A 500 NULL NULL YES LSMTREE
INSERT INTO t1 (d1,d2,n1,n2,pk) VALUES
(10.22,60.12345,123456,14.3456,1),
(10.0,60.12345,123456,14,2),
(11.14,15,123456,13,3),
(100,100,1,2,4),
(0,0,0,0,5),
(4540424564.23,3343303441.0,12,13,6),
(15,17,23,100000,7);
Warnings:
Warning 1264 Out of range value for column 'd1' at row 6
EXPLAIN SELECT d2, COUNT(*) FROM t1 GROUP BY d2;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 index NULL d2 29 NULL # Using index
SELECT d2, COUNT(*) FROM t1 GROUP BY d2;
d2 COUNT(*)
0.0000000000 1
100.0000000000 1
15.0000000000 1
17.0000000000 1
3343303441.0000000000 1
60.1234500000 2
EXPLAIN SELECT d2, COUNT(*) FROM t1 IGNORE INDEX FOR GROUP BY (d2) GROUP BY d2;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 index NULL d2 29 NULL # Using index; Using temporary; Using filesort
SELECT d2, COUNT(*) FROM t1 IGNORE INDEX FOR GROUP BY (d2) GROUP BY d2;
d2 COUNT(*)
0.0000000000 1
100.0000000000 1
15.0000000000 1
17.0000000000 1
3343303441.0000000000 1
60.1234500000 2
DROP TABLE t1;
SET GLOBAL ROCKSDB_PAUSE_BACKGROUND_WORK = @ORIG_PAUSE_BACKGROUND_WORK;
|
