-
Notifications
You must be signed in to change notification settings - Fork 1.7k
/
bloomfilter3.test
136 lines (114 loc) · 7.43 KB
/
bloomfilter3.test
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
--source include/have_rocksdb.inc
--source include/restart_mysqld.inc
CREATE TABLE `linktable` (
`id1` bigint(20) unsigned NOT NULL DEFAULT '0',
`id1_type` int(10) unsigned NOT NULL DEFAULT '0',
`id2` bigint(20) unsigned NOT NULL DEFAULT '0',
`id2_type` int(10) unsigned NOT NULL DEFAULT '0',
`link_type` bigint(20) unsigned NOT NULL DEFAULT '0',
`visibility` tinyint(3) NOT NULL DEFAULT '0',
`data` varchar(255) NOT NULL DEFAULT '',
`time` bigint(20) unsigned NOT NULL DEFAULT '0',
`version` int(11) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (link_type, `id1`,`id2`) COMMENT 'cf_link_pk',
KEY `id1_type` (`id1`,`link_type`,`visibility`,`time`,`version`,`data`) COMMENT 'rev:cf_link_id1_type',
KEY `id1_type2` (`id1`,`link_type`,`time`,`version`,`data`,`visibility`) COMMENT 'rev:cf_link_id1_type2',
KEY `id1_type3` (`id1`,`visibility`,`time`,`version`,`data`,`link_type`) COMMENT 'rev:cf_link_id1_type3'
) ENGINE=RocksDB DEFAULT COLLATE=latin1_bin;
--disable_query_log
call mtr.add_suppression("LibRocksDB");
let $i = 1;
while ($i <= 10000) {
let $insert = INSERT INTO linktable VALUES($i, $i, $i, $i, 1, 1, $i, $i, $i);
eval $insert;
inc $i;
}
--enable_query_log
## HA_READ_PREFIX_LAST_OR_PREV
# BF len 21
select variable_value into @c from information_schema.global_status where variable_name='rocksdb_bloom_filter_prefix_checked';
select id1, id2, link_type, visibility, data, time, version from linktable FORCE INDEX(`id1_type`) where id1 = 100 and link_type = 1 and time >= 0 and time <= 9223372036854775807 and visibility = 1 order by time desc;
select case when variable_value-@c > 0 then 'true' else 'false' end from information_schema.global_status where variable_name='rocksdb_bloom_filter_prefix_checked';
# BF len 20
--echo # MariaDB: we don't have optimizer_force_index_for_range, but we can use EITS
--echo # to get the query plan we want.
set @tmp_use_stat_tables= @@use_stat_tables;
set use_stat_tables='preferably';
analyze table linktable persistent for all;
flush tables;
explain select * from linktable;
--echo # This must use range(id1_type2), key_len=24
explain
select id1, id2, link_type, visibility, data, time, version from linktable
FORCE INDEX(`id1_type2`) where id1 = 100 and link_type = 1 and time >= 0 and time <= 9223372036854775807 order by time desc;
select variable_value into @c from information_schema.global_status where variable_name='rocksdb_bloom_filter_prefix_checked';
# MariaDB: no support for optimizer_force_index_for_range:
#set @tmp_force_index_for_range=@@optimizer_force_index_for_range;
#set optimizer_force_index_for_range=on;
select id1, id2, link_type, visibility, data, time, version from linktable FORCE INDEX(`id1_type2`) where id1 = 100 and link_type = 1 and time >= 0 and time <= 9223372036854775807 order by time desc;
select case when variable_value-@c > 0 then 'true' else 'false' end from information_schema.global_status where variable_name='rocksdb_bloom_filter_prefix_checked';
#set global optimizer_force_index_for_range=@tmp_force_index_for_range;
# BF len 13
select variable_value into @c from information_schema.global_status where variable_name='rocksdb_bloom_filter_prefix_checked';
select id1, id2, link_type, visibility, data, time, version from linktable FORCE INDEX(`id1_type3`) where id1 = 100 and time >= 0 and time <= 9223372036854775807 and visibility = 1 order by time desc;
select case when variable_value-@c = 0 then 'true' else 'false' end from information_schema.global_status where variable_name='rocksdb_bloom_filter_prefix_checked';
## HA_READ_PREFIX_LAST_OR_PREV (no end range)
# BF len 20
select variable_value into @c from information_schema.global_status where variable_name='rocksdb_bloom_filter_prefix_checked';
select id1, id2, link_type, visibility, data, time, version from linktable FORCE INDEX(`id1_type`) where id1 = 100 and link_type = 1 and visibility = 1 and time >= 0 order by time desc;
select case when variable_value-@c > 0 then 'true' else 'false' end from information_schema.global_status where variable_name='rocksdb_bloom_filter_prefix_checked';
# BF len 19
select variable_value into @c from information_schema.global_status where variable_name='rocksdb_bloom_filter_prefix_checked';
select id1, id2, link_type, visibility, data, time, version from linktable FORCE INDEX(`id1_type2`) where id1 = 100 and link_type = 1 and time >= 0 order by time desc;
select case when variable_value-@c = 0 then 'true' else 'false' end from information_schema.global_status where variable_name='rocksdb_bloom_filter_prefix_checked';
--echo ## HA_READ_PREFIX_LAST
--echo # BF len 20
select variable_value into @c from information_schema.global_status where variable_name='rocksdb_bloom_filter_prefix_checked';
select id1, id2, link_type, visibility, data, time, version from linktable FORCE INDEX(`id1_type`) where id1 = 100 and link_type = 1 and visibility = 1 order by time desc;
select case when variable_value-@c > 0 then 'true' else 'false' end from information_schema.global_status where variable_name='rocksdb_bloom_filter_prefix_checked';
--echo # BF len 19
select variable_value into @c from information_schema.global_status where variable_name='rocksdb_bloom_filter_prefix_checked';
select id1, id2, link_type, visibility, data, time, version from linktable FORCE INDEX(`id1_type2`) where id1 = 100 and link_type = 1 order by time desc;
select case when variable_value-@c = 0 then 'true' else 'false' end from information_schema.global_status where variable_name='rocksdb_bloom_filter_prefix_checked';
--echo # BF len 12
select variable_value into @c from information_schema.global_status where variable_name='rocksdb_bloom_filter_prefix_checked';
select id1, id2, link_type, visibility, data, time, version from linktable FORCE INDEX(`id1_type3`) where id1 = 100 and visibility = 1 order by time desc;
select case when variable_value-@c = 0 then 'true' else 'false' end from information_schema.global_status where variable_name='rocksdb_bloom_filter_prefix_checked';
DROP TABLE linktable;
--source include/restart_mysqld.inc
--echo #
--echo # bloom filter prefix is 20 byte
--echo # Create a key which is longer than that, so that we see that
--echo # eq_cond_len= slice.size() - 1;
--echo # doesnt work.
--echo #
--echo # indexnr 4
--echo # kp0 + 4 = 8
--echo # kp1 + 8 = 16
--echo # kp2 + 8 = 24 24>20 byte length prefix
--echo # kp3 + 8 = 28
create table t1 (
pk int primary key,
kp0 int not null,
kp1 bigint not null,
kp2 bigint not null,
kp3 bigint not null,
key kp12(kp0, kp1, kp2, kp3) comment 'rev:x1'
) engine=rocksdb;
insert into t1 values (1, 1,1, 1,1);
insert into t1 values (10,1,1,0x12FFFFFFFFFF,1);
insert into t1 values (11,1,1,0x12FFFFFFFFFF,1);
insert into t1 values (20,2,2,0x12FFFFFFFFFF,1);
insert into t1 values (21,2,2,0x12FFFFFFFFFF,1);
--source include/restart_mysqld.inc
--replace_column 9 #
explain
select * from t1 where kp0=1 and kp1=1 and kp2=0x12FFFFFFFFFF order by kp3 desc;
show status like '%rocksdb_bloom_filter_prefix%';
select variable_value into @c from information_schema.global_status where variable_name='rocksdb_bloom_filter_prefix_checked';
select * from t1 where kp0=1 and kp1=1 and kp2=0x12FFFFFFFFFF order by kp3 desc;
show status like '%rocksdb_bloom_filter_prefix%';
--echo # The following MUST show TRUE:
select case when variable_value-@c = 0 then 'true' else 'false' end from information_schema.global_status where variable_name='rocksdb_bloom_filter_prefix_checked';
drop table t1;
# Key length is 4 + 8 + 8 = 20