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
|
# restart
set use_stat_tables= 'COMPLEMENTARY';
CREATE TABLE t0 (id int PRIMARY KEY, a int, INDEX ix_a (a)) engine=rocksdb;
insert into t0 values (0, 0),(1, 1),(2, 2),(3, 3),(4, 4),
(5, 4),(6, 4),(7, 4),(8, 4),(9, 4);
SELECT cardinality FROM information_schema.statistics where table_name="t0" and
column_name="id";
cardinality
NULL
SELECT cardinality FROM information_schema.statistics where table_name="t0" and
column_name="a";
cardinality
NULL
ANALYZE TABLE t0;
SELECT table_rows into @N FROM information_schema.tables
WHERE table_name = "t0";
SELECT FLOOR(@N/cardinality) FROM
information_schema.statistics where table_name="t0" and column_name="id";
FLOOR(@N/cardinality)
1
SELECT FLOOR(@N/cardinality) FROM
information_schema.statistics where table_name="t0" and column_name="a";
FLOOR(@N/cardinality)
2
SET GLOBAL rocksdb_force_flush_memtable_now = 1;
ANALYZE TABLE t0;
SELECT table_rows into @N FROM information_schema.tables
WHERE table_name = "t0";
SELECT FLOOR(@N/cardinality) FROM
information_schema.statistics where table_name="t0" and column_name="id";
FLOOR(@N/cardinality)
1
SELECT FLOOR(@N/cardinality) FROM
information_schema.statistics where table_name="t0" and column_name="a";
FLOOR(@N/cardinality)
2
drop table t0;
DROP TABLE IF EXISTS t1,t10,t11;
create table t1(
id bigint not null primary key,
i1 bigint, #unique
i2 bigint, #repeating
c1 varchar(20), #unique
c2 varchar(20), #repeating
index t1_1(id, i1),
index t1_2(i1, i2),
index t1_3(i2, i1),
index t1_4(c1, c2),
index t1_5(c2, c1)
) engine=rocksdb;
optimize table t1;
Table Op Msg_type Msg_text
test.t1 optimize status OK
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 id A 100000 NULL NULL LSMTREE
t1 1 t1_1 1 id A 100000 NULL NULL LSMTREE
t1 1 t1_1 2 i1 A 100000 NULL NULL YES LSMTREE
t1 1 t1_2 1 i1 A 100000 NULL NULL YES LSMTREE
t1 1 t1_2 2 i2 A 100000 NULL NULL YES LSMTREE
t1 1 t1_3 1 i2 A 11111 NULL NULL YES LSMTREE
t1 1 t1_3 2 i1 A 100000 NULL NULL YES LSMTREE
t1 1 t1_4 1 c1 A 100000 NULL NULL YES LSMTREE
t1 1 t1_4 2 c2 A 100000 NULL NULL YES LSMTREE
t1 1 t1_5 1 c2 A 11111 NULL NULL YES LSMTREE
t1 1 t1_5 2 c1 A 100000 NULL NULL YES LSMTREE
SELECT table_name, table_rows FROM information_schema.tables WHERE table_schema = DATABASE();
table_name table_rows
t1 100000
restarting...
# restart
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 id A 100000 NULL NULL LSMTREE
t1 1 t1_1 1 id A 100000 NULL NULL LSMTREE
t1 1 t1_1 2 i1 A 100000 NULL NULL YES LSMTREE
t1 1 t1_2 1 i1 A 100000 NULL NULL YES LSMTREE
t1 1 t1_2 2 i2 A 100000 NULL NULL YES LSMTREE
t1 1 t1_3 1 i2 A 11111 NULL NULL YES LSMTREE
t1 1 t1_3 2 i1 A 100000 NULL NULL YES LSMTREE
t1 1 t1_4 1 c1 A 100000 NULL NULL YES LSMTREE
t1 1 t1_4 2 c2 A 100000 NULL NULL YES LSMTREE
t1 1 t1_5 1 c2 A 11111 NULL NULL YES LSMTREE
t1 1 t1_5 2 c1 A 100000 NULL NULL YES LSMTREE
SELECT table_name, table_rows FROM information_schema.tables WHERE table_schema = DATABASE();
table_name table_rows
t1 100000
CREATE TABLE t2 (a INT, b INT, c INT, d INT, e INT, f INT, g INT,
PRIMARY KEY (a), KEY (c, b, a, d, e, f, g))
ENGINE=ROCKSDB;
SET GLOBAL rocksdb_force_flush_memtable_now = 1;
ANALYZE TABLE t2;
Table Op Msg_type Msg_text
test.t2 analyze status Engine-independent statistics collected
test.t2 analyze status OK
cardinality of the columns after 'a' must be equal to the cardinality of column 'a'
SELECT CARDINALITY INTO @c FROM information_schema.statistics WHERE TABLE_NAME='t2' AND INDEX_NAME='c' AND COLUMN_NAME='a';
SELECT COLUMN_NAME, CARDINALITY = @c FROM information_schema.statistics WHERE TABLE_NAME='t2' AND INDEX_NAME='c' AND SEQ_IN_INDEX > 3;
COLUMN_NAME CARDINALITY = @c
d 1
e 1
f 1
g 1
drop table t1, t2;
|
