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
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
|
select * from information_schema.engines where engine = 'rocksdb';
ENGINE SUPPORT COMMENT TRANSACTIONS XA SAVEPOINTS
ROCKSDB DEFAULT RocksDB storage engine YES YES YES
drop table if exists t0,t1,t2,t3;
create table t0 (a int) engine=myisam;
insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
create table t1(a int) engine=myisam;
insert into t1 select A.a + B.a* 10 + C.a * 100 from t0 A, t0 B, t0 C;
create table t2 (
pk int primary key,
kp1 int,
kp2 int,
col1 int,
key (kp1,kp2) comment 'cf1'
) engine=rocksdb;
insert into t2 select a,a,a,a from t1;
# Try a basic case:
explain
select * from t2 where kp1 between 1 and 10 and mod(kp2,2)=0;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t2 range kp1 kp1 5 NULL # Using index condition
select * from t2 where kp1 between 1 and 10 and mod(kp2,2)=0;
pk kp1 kp2 col1
2 2 2 2
4 4 4 4
6 6 6 6
8 8 8 8
10 10 10 10
# Check that ICP doesnt work for columns where column value
# cant be restored from mem-comparable form:
create table t3 (
pk int primary key,
kp1 int,
kp2 varchar(10) collate utf8_general_ci,
col1 int,
key (kp1,kp2) comment 'cf1'
) engine=rocksdb;
insert into t3 select a,a/10,a,a from t1;
# This must not use ICP:
explain
select * from t3 where kp1=3 and kp2 like '%foo%';
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t3 ref kp1 kp1 5 const # Using where
explain format=json
select * from t3 where kp1 between 2 and 4 and mod(kp1,3)=0 and kp2 like '%foo%';
EXPLAIN
{
"query_block": {
"select_id": 1,
"table": {
"table_name": "t3",
"access_type": "range",
"possible_keys": ["kp1"],
"key": "kp1",
"key_length": "5",
"used_key_parts": ["kp1"],
"rows": 1000,
"filtered": 100,
"index_condition": "t3.kp1 between 2 and 4 and t3.kp1 % 3 = 0",
"attached_condition": "t3.kp2 like '%foo%'"
}
}
}
# Check that we handle the case where out-of-range is encountered sooner
# than matched index condition
explain
select * from t2 where kp1< 3 and kp2+1>50000;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t2 range kp1 kp1 5 NULL # Using index condition
select * from t2 where kp1< 3 and kp2+1>50000;
pk kp1 kp2 col1
explain
select * from t2 where kp1< 3 and kp2+1>50000;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t2 range kp1 kp1 5 NULL # Using index condition
select * from t2 where kp1< 3 and kp2+1>50000;
pk kp1 kp2 col1
# Try doing backwards scans
# MariaDB: ICP is not supported for reverse scans.
explain
select * from t2 where kp1 between 1 and 10 and mod(kp2,2)=0 order by kp1 desc;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t2 range kp1 kp1 5 NULL # Using where
select * from t2 where kp1 between 1 and 10 and mod(kp2,2)=0 order by kp1 desc;
pk kp1 kp2 col1
10 10 10 10
8 8 8 8
6 6 6 6
4 4 4 4
2 2 2 2
explain
select * from t2 where kp1 >990 and mod(kp2,2)=0 order by kp1 desc;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t2 range kp1 kp1 5 NULL # Using where
select * from t2 where kp1 >990 and mod(kp2,2)=0 order by kp1 desc;
pk kp1 kp2 col1
998 998 998 998
996 996 996 996
994 994 994 994
992 992 992 992
explain
select * from t2 where kp1< 3 and kp2+1>50000 order by kp1 desc;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t2 range kp1 kp1 5 NULL # Using where
select * from t2 where kp1< 3 and kp2+1>50000 order by kp1 desc;
pk kp1 kp2 col1
drop table t0,t1,t2,t3;
#
# Check how ICP affects counters
#
# First, some preparations
#
# in facebook/mysql-5.6, it was:
# select ROWS_READ, ROWS_REQUESTED, ROWS_INDEX_FIRST, ROWS_INDEX_NEXT
#
# In MariaDB, we do:
create procedure save_read_stats()
begin
set @rr=(select ROWS_READ
from information_schema.table_statistics
where table_name='t4' and table_schema=database());
set @rif= (select VARIABLE_VALUE
from information_schema.session_status
where VARIABLE_NAME='Handler_read_first');
set @rin=(select VARIABLE_VALUE
from information_schema.session_status
where VARIABLE_NAME='Handler_read_next');
set @icp_attempts=(select VARIABLE_VALUE
from information_schema.session_status
where VARIABLE_NAME='Handler_icp_attempts');
set @icp_matches=(select VARIABLE_VALUE
from information_schema.session_status
where VARIABLE_NAME='Handler_icp_match');
end|
create procedure get_read_stats()
begin
select
(select ROWS_READ
from information_schema.table_statistics
where table_name='t4' and table_schema=database()
) - @rr as ROWS_READ_DIFF,
(select VARIABLE_VALUE - @rif
from information_schema.session_status
where VARIABLE_NAME='Handler_read_first') as ROWS_INDEX_FIRST,
(select VARIABLE_VALUE - @rin
from information_schema.session_status
where VARIABLE_NAME='Handler_read_next') as ROWS_INDEX_NEXT,
(select VARIABLE_VALUE - @icp_attempts
from information_schema.session_status
where VARIABLE_NAME='Handler_icp_attempts') as ICP_ATTEMPTS,
(select VARIABLE_VALUE - @icp_matches
from information_schema.session_status
where VARIABLE_NAME='Handler_icp_match') as ICP_MATCHES;
end|
create table t4 (
id int,
id1 int,
id2 int,
value int,
value2 varchar(100),
primary key (id),
key id1_id2 (id1, id2) comment 'cf1'
) engine=rocksdb charset=latin1 collate latin1_bin;
insert into t4 values
(1,1,1,1,1), (2,1,2,2,2), (3,1,3,3,3),(4,1,4,4,4),(5,1,5,5,5),
(6,1,6,6,6), (7,1,7,7,7), (8,1,8,8,8),(9,1,9,9,9),(10,1,10,10,10);
#
# Now, the test itself
#
call save_read_stats();
call get_read_stats();
ROWS_READ_DIFF ROWS_INDEX_FIRST ROWS_INDEX_NEXT ICP_ATTEMPTS ICP_MATCHES
0 0 0 0 0
# ============== index-only query ==============
explain
select id1,id2 from t4 force index (id1_id2) where id1=1 and id2 % 10 = 1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t4 ref id1_id2 id1_id2 5 const # Using where; Using index
call save_read_stats();
select id1,id2 from t4 force index (id1_id2) where id1=1 and id2 % 10 = 1;
id1 id2
1 1
call get_read_stats();
ROWS_READ_DIFF 10
ROWS_INDEX_FIRST 0
ROWS_INDEX_NEXT 10
ICP_ATTEMPTS 0
ICP_MATCHES 0
# ============== Query without ICP ==============
set optimizer_switch='index_condition_pushdown=off';
explain
select * from t4 force index (id1_id2) where id1=1 and id2 % 10 = 1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t4 ref id1_id2 id1_id2 5 const # Using where
call save_read_stats();
select * from t4 force index (id1_id2) where id1=1 and id2 % 10 = 1;
id id1 id2 value value2
1 1 1 1 1
call get_read_stats();
ROWS_READ_DIFF 10
ROWS_INDEX_FIRST 0
ROWS_INDEX_NEXT 10
ICP_ATTEMPTS 0
ICP_MATCHES 0
# ============== Query with ICP ==============
set optimizer_switch='index_condition_pushdown=on';
explain
select * from t4 force index (id1_id2) where id1=1 and id2 % 10 = 1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t4 ref id1_id2 id1_id2 5 const # Using index condition
call save_read_stats();
select * from t4 force index (id1_id2) where id1=1 and id2 % 10 = 1;
id id1 id2 value value2
1 1 1 1 1
call get_read_stats();
ROWS_READ_DIFF 1
ROWS_INDEX_FIRST 0
ROWS_INDEX_NEXT 1
ICP_ATTEMPTS 10
ICP_MATCHES 1
drop table t4;
drop procedure save_read_stats;
drop procedure get_read_stats;
#
# Issue #67: Inefficient index condition pushdown
#
create table t0 (a int) engine=myisam;
insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
create table t1 (
pk int not null primary key,
key1 bigint(20) unsigned,
col1 int,
key (key1)
) engine=rocksdb;
insert into t1
select
A.a+10*B.a+100*C.a,
A.a+10*B.a+100*C.a,
1234
from t0 A, t0 B, t0 C;
set @count=0;
explain
select * from t1 force index(key1) where key1=1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ref key1 key1 9 const #
set @count_diff =(select (value - @count) from information_schema.rocksdb_perf_context
where table_schema=database() and table_name='t1' and stat_type='INTERNAL_KEY_SKIPPED_COUNT');
select * from t1 force index(key1) where key1=1;
pk key1 col1
1 1 1234
set @count_diff =(select (value - @count) from information_schema.rocksdb_perf_context
where table_schema=database() and table_name='t1' and stat_type='INTERNAL_KEY_SKIPPED_COUNT');
# The following must be =1, or in any case not 999:
select @count_diff as "INTERNAL_KEY_SKIPPED_COUNT increment";
INTERNAL_KEY_SKIPPED_COUNT increment
1
drop table t0,t1;
|
