1--source include/have_rocksdb.inc 2 3--disable_warnings 4DROP TABLE IF EXISTS t1; 5--enable_warnings 6 7# Create the table and insert some keys 8CREATE TABLE t1 ( 9 i INT, 10 a INT, 11 b INT, 12 PRIMARY KEY (i), 13 KEY ka(a), 14 KEY kb(b) comment 'rev:cf1' 15) ENGINE = rocksdb; 16 17--disable_query_log 18let $max = 20000; 19let $i = 1; 20while ($i <= $max) { 21 let $insert = INSERT INTO t1 VALUES ($i, $i, $i); 22 inc $i; 23 eval $insert; 24} 25--enable_query_log 26 27# get results for records_in_range prior to memtable flush 28# normal CF 29explain select * from t1 where a> 500 and a< 750; 30explain select * from t1 where a< 750; 31explain select * from t1 where a> 500; 32explain select * from t1 where a>=0 and a<=1000; 33 34#reverse CF 35explain select * from t1 where b> 500 and b< 750; 36explain select * from t1 where b< 750; 37explain select * from t1 where b> 500; 38explain select * from t1 where b>=0 and b<=1000; 39 40## cost calculation differences between covering vs non-covering (#298) 41set @save_rocksdb_records_in_range = @@session.rocksdb_records_in_range; 42set rocksdb_records_in_range = 15000; 43# covering, range 44explain select a from t1 where a < 750; 45# non-covering, full 46explain select a, b from t1 where a < 750; 47# covering, ref 48explain select a from t1 where a = 700; 49# non-covering, ref 50explain select a,b from t1 where a = 700; 51# covering, full index 52explain select a from t1 where a in (700, 800); 53# non-covering, full 54explain select a,b from t1 where a in (700, 800); 55set rocksdb_records_in_range=8000; 56# covering, range 57explain select a from t1 where a in (700, 800); 58# non-covering, full 59explain select a,b from t1 where a in (700, 800); 60set rocksdb_records_in_range = @save_rocksdb_records_in_range; 61 62# flush memtable and repeat 63set global rocksdb_force_flush_memtable_now = true; 64# normal CF 65explain select * from t1 where a> 500 and a< 750; 66explain select * from t1 where a< 750; 67explain select * from t1 where a> 500; 68explain select * from t1 where a>=0 and a<=1000; 69 70#reverse CF 71explain select * from t1 where b> 500 and b< 750; 72explain select * from t1 where b< 750; 73explain select * from t1 where b> 500; 74explain select * from t1 where b>=0 and b<=1000; 75 76# a set of 1 77explain select * from t1 where a>= 500 and a<= 500; 78explain select * from t1 where b>= 500 and b<= 500; 79 80# two indexes 81explain select * from t1 where a< 750 and b> 500 and b< 750; 82 83# composite index 84drop index ka on t1; 85drop index kb on t1; 86create index kab on t1(a,b); 87set global rocksdb_force_flush_memtable_now = true; 88explain select * from t1 where a< 750 and b> 500 and b< 750; 89 90# override records in range 91set rocksdb_records_in_range=444; 92explain select * from t1 where a< 750 and b> 500 and b< 750; 93set rocksdb_records_in_range=0; 94 95# issue 82 96## forward cf 97CREATE TABLE `linktable` ( 98 `id1` bigint(20) unsigned NOT NULL DEFAULT '0', 99 `id1_type` int(10) unsigned NOT NULL DEFAULT '0', 100 `id2` bigint(20) unsigned NOT NULL DEFAULT '0', 101 `id2_type` int(10) unsigned NOT NULL DEFAULT '0', 102 `link_type` bigint(20) unsigned NOT NULL DEFAULT '0', 103 `visibility` tinyint(3) NOT NULL DEFAULT '0', 104 `data` varchar(255) COLLATE latin1_bin NOT NULL DEFAULT '', 105 `time` bigint(20) unsigned NOT NULL DEFAULT '0', 106 `version` int(11) unsigned NOT NULL DEFAULT '0', 107 PRIMARY KEY (`link_type`,`id1`,`id2`) COMMENT 'cf_link_pk', 108 KEY `id1_type` (`id1`,`link_type`,`visibility`,`time`,`version`,`data`) COMMENT 'cf_link_id1_type' 109) ENGINE=ROCKSDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin; 110 111insert into linktable values (1,1,1,1,1,1,1,1,1); 112insert into linktable values (1,1,2,1,1,1,1,1,1); 113insert into linktable values (1,1,3,1,1,1,1,1,1); 114insert into linktable values (1,1,4,1,1,1,1,1,1); 115set global rocksdb_force_flush_memtable_now = true; 116explain select id1, id2, link_type, visibility, data, time, version from linktable where id1 = 1 and link_type = 1 and id2 in (1, 2); 117drop table linktable; 118 119## rev cf 120CREATE TABLE `linktable` ( 121 `id1` bigint(20) unsigned NOT NULL DEFAULT '0', 122 `id1_type` int(10) unsigned NOT NULL DEFAULT '0', 123 `id2` bigint(20) unsigned NOT NULL DEFAULT '0', 124 `id2_type` int(10) unsigned NOT NULL DEFAULT '0', 125 `link_type` bigint(20) unsigned NOT NULL DEFAULT '0', 126 `visibility` tinyint(3) NOT NULL DEFAULT '0', 127 `data` varchar(255) COLLATE latin1_bin NOT NULL DEFAULT '', 128 `time` bigint(20) unsigned NOT NULL DEFAULT '0', 129 `version` int(11) unsigned NOT NULL DEFAULT '0', 130 PRIMARY KEY (`link_type`,`id1`,`id2`) COMMENT 'cf_link_pk', 131 KEY `id1_type` (`id1`,`link_type`,`visibility`,`time`,`version`,`data`) COMMENT 'rev:cf_link_id1_type' 132) ENGINE=ROCKSDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin; 133 134insert into linktable values (1,1,1,1,1,1,1,1,1); 135insert into linktable values (1,1,2,1,1,1,1,1,1); 136insert into linktable values (1,1,3,1,1,1,1,1,1); 137insert into linktable values (1,1,4,1,1,1,1,1,1); 138set global rocksdb_force_flush_memtable_now = true; 139explain select id1, id2, link_type, visibility, data, time, version from linktable where id1 = 1 and link_type = 1 and id2 in (1, 2); 140drop table linktable; 141 142#cleanup 143DROP TABLE t1; 144