SET @ORIG_PAUSE_BACKGROUND_WORK = @@ROCKSDB_PAUSE_BACKGROUND_WORK;
SET GLOBAL ROCKSDB_PAUSE_BACKGROUND_WORK = 1;
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (
i INT PRIMARY KEY,
t TINYINT,
s SMALLINT,
m MEDIUMINT,
b BIGINT
) ENGINE=rocksdb;
INSERT INTO t1 (i,t,s,m,b) VALUES (1,2,3,4,5),(1000,100,10000,1000000,1000000000000000000),(5,100,10000,1000000,100000000000000000),(2,3,4,5,6),(3,4,5,6,7),(101,102,103,104,105),(10001,103,10002,10003,10004),(10,11,12,13,14),(11,12,13,14,15),(12,13,14,15,16);
EXPLAIN SELECT i FROM t1 ORDER BY i;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	index	NULL	PRIMARY	4	NULL	#	Using index
SELECT i FROM t1 ORDER BY i;
i
1
2
3
5
10
11
12
101
1000
10001
DROP TABLE t1;
CREATE TABLE t1 (
i INT,
t TINYINT,
s SMALLINT,
m MEDIUMINT,
b BIGINT,
pk SMALLINT AUTO_INCREMENT PRIMARY KEY,
INDEX s_m (s,m)
) ENGINE=rocksdb;
INSERT INTO t1 (i,t,s,m,b) VALUES (1,2,3,4,5),(1000,100,10000,1000000,1000000000000000000),(5,100,10000,1000000,100000000000000000),(2,3,4,5,6),(3,4,5,6,7),(101,102,103,104,105),(10001,103,10002,10003,10004),(10,11,12,13,14),(11,12,13,14,15),(12,13,14,15,16);
EXPLAIN SELECT s, m FROM t1 WHERE s != 10 AND m != 1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	index	s_m	s_m	7	NULL	#	Using where; Using index
SELECT s, m FROM t1 WHERE s != 10 AND m != 1;
s	m
10000	1000000
10000	1000000
10002	10003
103	104
12	13
13	14
14	15
3	4
4	5
5	6
DROP TABLE t1;
# RocksDB: unique indexes allowed
CREATE TABLE t1 (
i INT,
t TINYINT,
s SMALLINT,
m MEDIUMINT,
b BIGINT,
pk MEDIUMINT AUTO_INCREMENT PRIMARY KEY,
UNIQUE KEY b_t (b,t)
) ENGINE=rocksdb;
INSERT INTO t1 (i,t,s,m,b) VALUES (1,2,3,4,5),(1000,100,10000,1000000,1000000000000000000),(5,100,10000,1000000,100000000000000000),(2,3,4,5,6),(3,4,5,6,7),(101,102,103,104,105),(10001,103,10002,10003,10004),(10,11,12,13,14),(11,12,13,14,15),(12,13,14,15,16);
SELECT b+t FROM t1 WHERE (b,t) IN ( SELECT b, t FROM t1 WHERE i>1 ) ORDER BY b+t;
b+t
9
11
25
27
29
207
10107
100000000000000100
1000000000000000100
SELECT b+t FROM t1 FORCE INDEX (b_t) WHERE (b,t) IN ( SELECT b, t FROM t1 WHERE i>1 ) ORDER BY b+t;
b+t
9
11
25
27
29
207
10107
100000000000000100
1000000000000000100
SELECT b+t FROM t1 IGNORE INDEX (b_t) WHERE (b,t) IN ( SELECT b, t FROM t1 WHERE i>1 ) ORDER BY b+t;
b+t
9
11
25
27
29
207
10107
100000000000000100
1000000000000000100
DROP TABLE t1;
SET GLOBAL ROCKSDB_PAUSE_BACKGROUND_WORK = @ORIG_PAUSE_BACKGROUND_WORK;