diff options
Diffstat (limited to '')
| -rw-r--r-- | storage/rocksdb/mysql-test/rocksdb/r/rocksdb_icp.result | 257 |
1 files changed, 257 insertions, 0 deletions
diff --git a/storage/rocksdb/mysql-test/rocksdb/r/rocksdb_icp.result b/storage/rocksdb/mysql-test/rocksdb/r/rocksdb_icp.result new file mode 100644 index 00000000..101e159e --- /dev/null +++ b/storage/rocksdb/mysql-test/rocksdb/r/rocksdb_icp.result @@ -0,0 +1,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 MOD 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; |