diff options
Diffstat (limited to 'storage/rocksdb/mysql-test/rocksdb/t/records_in_range.test')
| -rw-r--r-- | storage/rocksdb/mysql-test/rocksdb/t/records_in_range.test | 144 |
1 files changed, 144 insertions, 0 deletions
diff --git a/storage/rocksdb/mysql-test/rocksdb/t/records_in_range.test b/storage/rocksdb/mysql-test/rocksdb/t/records_in_range.test new file mode 100644 index 00000000000..9c939ef06e4 --- /dev/null +++ b/storage/rocksdb/mysql-test/rocksdb/t/records_in_range.test @@ -0,0 +1,144 @@ +--source include/have_rocksdb.inc + +--disable_warnings +DROP TABLE IF EXISTS t1; +--enable_warnings + +# Create the table and insert some keys +CREATE TABLE t1 ( + i INT, + a INT, + b INT, + PRIMARY KEY (i), + KEY ka(a), + KEY kb(b) comment 'rev:cf1' +) ENGINE = rocksdb; + +--disable_query_log +let $max = 20000; +let $i = 1; +while ($i <= $max) { + let $insert = INSERT INTO t1 VALUES ($i, $i, $i); + inc $i; + eval $insert; +} +--enable_query_log + +# get results for records_in_range prior to memtable flush +# normal CF +explain extended select * from t1 where a> 500 and a< 750; +explain extended select * from t1 where a< 750; +explain extended select * from t1 where a> 500; +explain extended select * from t1 where a>=0 and a<=1000; + +#reverse CF +explain extended select * from t1 where b> 500 and b< 750; +explain extended select * from t1 where b< 750; +explain extended select * from t1 where b> 500; +explain extended select * from t1 where b>=0 and b<=1000; + +## cost calculation differences between covering vs non-covering (#298) +set @save_rocksdb_records_in_range = @@session.rocksdb_records_in_range; +set rocksdb_records_in_range = 15000; +# covering, range +explain extended select a from t1 where a < 750; +# non-covering, full +explain extended select a, b from t1 where a < 750; +# covering, ref +explain extended select a from t1 where a = 700; +# non-covering, ref +explain extended select a,b from t1 where a = 700; +# covering, full index +explain extended select a from t1 where a in (700, 800); +# non-covering, full +explain extended select a,b from t1 where a in (700, 800); +set rocksdb_records_in_range=8000; +# covering, range +explain extended select a from t1 where a in (700, 800); +# non-covering, full +explain extended select a,b from t1 where a in (700, 800); +set rocksdb_records_in_range = @save_rocksdb_records_in_range; + +# flush memtable and repeat +set global rocksdb_force_flush_memtable_now = true; +# normal CF +explain extended select * from t1 where a> 500 and a< 750; +explain extended select * from t1 where a< 750; +explain extended select * from t1 where a> 500; +explain extended select * from t1 where a>=0 and a<=1000; + +#reverse CF +explain extended select * from t1 where b> 500 and b< 750; +explain extended select * from t1 where b< 750; +explain extended select * from t1 where b> 500; +explain extended select * from t1 where b>=0 and b<=1000; + +# a set of 1 +explain extended select * from t1 where a>= 500 and a<= 500; +explain extended select * from t1 where b>= 500 and b<= 500; + +# two indexes +explain extended select * from t1 where a< 750 and b> 500 and b< 750; + +# composite index +drop index ka on t1; +drop index kb on t1; +create index kab on t1(a,b); +set global rocksdb_force_flush_memtable_now = true; +explain extended select * from t1 where a< 750 and b> 500 and b< 750; + +# override records in range +set rocksdb_records_in_range=444; +explain extended select * from t1 where a< 750 and b> 500 and b< 750; +set rocksdb_records_in_range=0; + +# issue 82 +## forward cf +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) COLLATE latin1_bin 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 'cf_link_id1_type' +) ENGINE=ROCKSDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin; + +insert into linktable values (1,1,1,1,1,1,1,1,1); +insert into linktable values (1,1,2,1,1,1,1,1,1); +insert into linktable values (1,1,3,1,1,1,1,1,1); +insert into linktable values (1,1,4,1,1,1,1,1,1); +set global rocksdb_force_flush_memtable_now = true; +explain select id1, id2, link_type, visibility, data, time, version from linktable where id1 = 1 and link_type = 1 and id2 in (1, 2); +drop table linktable; + +## rev cf +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) COLLATE latin1_bin 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' +) ENGINE=ROCKSDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin; + +insert into linktable values (1,1,1,1,1,1,1,1,1); +insert into linktable values (1,1,2,1,1,1,1,1,1); +insert into linktable values (1,1,3,1,1,1,1,1,1); +insert into linktable values (1,1,4,1,1,1,1,1,1); +set global rocksdb_force_flush_memtable_now = true; +explain select id1, id2, link_type, visibility, data, time, version from linktable where id1 = 1 and link_type = 1 and id2 in (1, 2); +drop table linktable; + +#cleanup +DROP TABLE t1; + |