EVOLUTION-MANAGER

Edit File: records_in_range.result

DROP TABLE IF EXISTS t1; CREATE TABLE t1 ( i INT, a INT, b INT, PRIMARY KEY (i), KEY ka(a), KEY kb(b) comment 'rev:cf1' ) ENGINE = rocksdb; explain extended select * from t1 where a> 500 and a< 750; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 range ka ka 5 NULL 1000 100.00 Using index condition Warnings: Note 1003 select `test`.`t1`.`i` AS `i`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where `test`.`t1`.`a` > 500 and `test`.`t1`.`a` < 750 explain extended select * from t1 where a< 750; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 range ka ka 5 NULL 1000 100.00 Using index condition Warnings: Note 1003 select `test`.`t1`.`i` AS `i`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where `test`.`t1`.`a` < 750 explain extended select * from t1 where a> 500; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 range ka ka 5 NULL 1000 100.00 Using index condition Warnings: Note 1003 select `test`.`t1`.`i` AS `i`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where `test`.`t1`.`a` > 500 explain extended select * from t1 where a>=0 and a<=1000; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 range ka ka 5 NULL 1000 100.00 Using index condition Warnings: Note 1003 select `test`.`t1`.`i` AS `i`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where `test`.`t1`.`a` >= 0 and `test`.`t1`.`a` <= 1000 explain extended select * from t1 where b> 500 and b< 750; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 range kb kb 5 NULL 1000 100.00 Using index condition Warnings: Note 1003 select `test`.`t1`.`i` AS `i`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where `test`.`t1`.`b` > 500 and `test`.`t1`.`b` < 750 explain extended select * from t1 where b< 750; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 range kb kb 5 NULL 1000 100.00 Using index condition Warnings: Note 1003 select `test`.`t1`.`i` AS `i`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where `test`.`t1`.`b` < 750 explain extended select * from t1 where b> 500; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 range kb kb 5 NULL 1000 100.00 Using index condition Warnings: Note 1003 select `test`.`t1`.`i` AS `i`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where `test`.`t1`.`b` > 500 explain extended select * from t1 where b>=0 and b<=1000; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 range kb kb 5 NULL 1000 100.00 Using index condition Warnings: Note 1003 select `test`.`t1`.`i` AS `i`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where `test`.`t1`.`b` >= 0 and `test`.`t1`.`b` <= 1000 set @save_rocksdb_records_in_range = @@session.rocksdb_records_in_range; set rocksdb_records_in_range = 15000; explain extended select a from t1 where a < 750; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 range ka ka 5 NULL 15000 100.00 Using where; Using index Warnings: Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` < 750 explain extended select a, b from t1 where a < 750; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL ka NULL NULL NULL 20000 75.00 Using where Warnings: Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where `test`.`t1`.`a` < 750 explain extended select a from t1 where a = 700; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ref ka ka 5 const 15000 100.00 Using index Warnings: Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 700 explain extended select a,b from t1 where a = 700; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ref ka ka 5 const 15000 100.00 Warnings: Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where `test`.`t1`.`a` = 700 explain extended select a from t1 where a in (700, 800); id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 index ka ka 5 NULL 20000 100.00 Using where; Using index Warnings: Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` in (700,800) explain extended select a,b from t1 where a in (700, 800); id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL ka NULL NULL NULL 20000 100.00 Using where Warnings: Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where `test`.`t1`.`a` in (700,800) set rocksdb_records_in_range=8000; explain extended select a from t1 where a in (700, 800); id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 range ka ka 5 NULL 16000 100.00 Using where; Using index Warnings: Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` in (700,800) explain extended select a,b from t1 where a in (700, 800); id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL ka NULL NULL NULL 20000 80.00 Using where Warnings: Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where `test`.`t1`.`a` in (700,800) set rocksdb_records_in_range = @save_rocksdb_records_in_range; set global rocksdb_force_flush_memtable_now = true; explain extended select * from t1 where a> 500 and a< 750; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 range ka ka 5 NULL 1000 100.00 Using index condition Warnings: Note 1003 select `test`.`t1`.`i` AS `i`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where `test`.`t1`.`a` > 500 and `test`.`t1`.`a` < 750 explain extended select * from t1 where a< 750; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 range ka ka 5 NULL 1000 100.00 Using index condition Warnings: Note 1003 select `test`.`t1`.`i` AS `i`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where `test`.`t1`.`a` < 750 explain extended select * from t1 where a> 500; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 range ka ka 5 NULL 1000 100.00 Using index condition Warnings: Note 1003 select `test`.`t1`.`i` AS `i`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where `test`.`t1`.`a` > 500 explain extended select * from t1 where a>=0 and a<=1000; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 range ka ka 5 NULL 1000 100.00 Using index condition Warnings: Note 1003 select `test`.`t1`.`i` AS `i`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where `test`.`t1`.`a` >= 0 and `test`.`t1`.`a` <= 1000 explain extended select * from t1 where b> 500 and b< 750; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 range kb kb 5 NULL 1000 100.00 Using index condition Warnings: Note 1003 select `test`.`t1`.`i` AS `i`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where `test`.`t1`.`b` > 500 and `test`.`t1`.`b` < 750 explain extended select * from t1 where b< 750; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 range kb kb 5 NULL 1000 100.00 Using index condition Warnings: Note 1003 select `test`.`t1`.`i` AS `i`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where `test`.`t1`.`b` < 750 explain extended select * from t1 where b> 500; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 range kb kb 5 NULL 1000 100.00 Using index condition Warnings: Note 1003 select `test`.`t1`.`i` AS `i`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where `test`.`t1`.`b` > 500 explain extended select * from t1 where b>=0 and b<=1000; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 range kb kb 5 NULL 1000 100.00 Using index condition Warnings: Note 1003 select `test`.`t1`.`i` AS `i`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where `test`.`t1`.`b` >= 0 and `test`.`t1`.`b` <= 1000 explain extended select * from t1 where a>= 500 and a<= 500; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 range ka ka 5 NULL 1000 100.00 Using index condition Warnings: Note 1003 select `test`.`t1`.`i` AS `i`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where `test`.`t1`.`a` >= 500 and `test`.`t1`.`a` <= 500 explain extended select * from t1 where b>= 500 and b<= 500; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 range kb kb 5 NULL 1000 100.00 Using index condition Warnings: Note 1003 select `test`.`t1`.`i` AS `i`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where `test`.`t1`.`b` >= 500 and `test`.`t1`.`b` <= 500 explain extended select * from t1 where a< 750 and b> 500 and b< 750; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 range ka,kb ka 5 NULL 1000 100.00 Using index condition; Using where Warnings: Note 1003 select `test`.`t1`.`i` AS `i`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where `test`.`t1`.`a` < 750 and `test`.`t1`.`b` > 500 and `test`.`t1`.`b` < 750 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; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 range kab kab 5 NULL 1000 100.00 Using where; Using index Warnings: Note 1003 select `test`.`t1`.`i` AS `i`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where `test`.`t1`.`a` < 750 and `test`.`t1`.`b` > 500 and `test`.`t1`.`b` < 750 set rocksdb_records_in_range=444; explain extended select * from t1 where a< 750 and b> 500 and b< 750; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 range kab kab 5 NULL 444 100.00 Using where; Using index Warnings: Note 1003 select `test`.`t1`.`i` AS `i`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where `test`.`t1`.`a` < 750 and `test`.`t1`.`b` > 500 and `test`.`t1`.`b` < 750 set rocksdb_records_in_range=0; 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); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE linktable range PRIMARY,id1_type PRIMARY 24 NULL 2 Using where drop table linktable; 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); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE linktable range PRIMARY,id1_type PRIMARY 24 NULL 2 Using where drop table linktable; DROP TABLE t1;