1SET @ORIG_PAUSE_BACKGROUND_WORK = @@ROCKSDB_PAUSE_BACKGROUND_WORK; 2SET GLOBAL ROCKSDB_PAUSE_BACKGROUND_WORK = 1; 3DROP TABLE IF EXISTS t1; 4CREATE TABLE t1 ( 5b BLOB, 6t TINYBLOB, 7m MEDIUMBLOB, 8l LONGBLOB, 9PRIMARY KEY b (b(32)) 10) ENGINE=rocksdb; 11Warnings: 12Warning 1280 Name 'b' ignored for PRIMARY key. 13SHOW INDEX IN t1; 14Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment 15t1 0 PRIMARY 1 b A 1000 32 NULL LSMTREE 16INSERT INTO t1 (b,t,m,l) VALUES 17('','','',''), 18('a','b','c','d'), 19('b','d','c','b'), 20('test1','test2','test3','test4'), 21(REPEAT('a',128),REPEAT('b',128),REPEAT('c',128),REPEAT('d',128)), 22(HEX('abcd'),HEX('def'),HEX('a'),HEX('abc')), 23('abc','def','ghi','jkl'), 24('test2','test3','test4','test5'), 25('test3','test4','test5','test6'), 26(REPEAT('b',128),REPEAT('f',128),REPEAT('e',128),REPEAT('d',128)), 27(REPEAT('c',128),REPEAT('b',128),REPEAT('c',128),REPEAT('e',128)); 28EXPLAIN SELECT SUBSTRING(b,16) AS f FROM t1 WHERE b IN ('test1','test2') ORDER BY f; 29id select_type table type possible_keys key key_len ref rows Extra 30# # # # # PRIMARY # # # # 31SELECT SUBSTRING(b,16) AS f FROM t1 WHERE b IN ('test1','test2') ORDER BY f; 32f 33 34 35EXPLAIN SELECT SUBSTRING(b,16) AS f FROM t1 USE INDEX () WHERE b IN ('test1','test2') ORDER BY f; 36id select_type table type possible_keys key key_len ref rows Extra 37# # # # # NULL # # # # 38SELECT SUBSTRING(b,16) AS f FROM t1 USE INDEX () WHERE b IN ('test1','test2') ORDER BY f; 39f 40 41 42DROP TABLE t1; 43CREATE TABLE t1 ( 44b BLOB, 45t TINYBLOB, 46m MEDIUMBLOB, 47l LONGBLOB, 48pk INT AUTO_INCREMENT PRIMARY KEY, 49UNIQUE INDEX l_t (l(256),t(64)) 50) ENGINE=rocksdb; 51SHOW INDEX IN t1; 52Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment 53t1 0 PRIMARY 1 pk # # NULL NULL # # 54t1 0 l_t 1 l # # 256 NULL # # 55t1 0 l_t 2 t # # 64 NULL # # 56INSERT INTO t1 (b,t,m,l) VALUES 57('','','',''), 58('a','b','c','d'), 59('b','d','c','b'), 60('test1','test2','test3','test4'), 61(REPEAT('a',128),REPEAT('b',128),REPEAT('c',128),REPEAT('d',128)), 62(HEX('abcd'),HEX('def'),HEX('a'),HEX('abc')), 63('abc','def','ghi','jkl'), 64('test2','test3','test4','test5'), 65('test3','test4','test5','test6'), 66(REPEAT('b',128),REPEAT('f',128),REPEAT('e',128),REPEAT('d',128)), 67(REPEAT('c',128),REPEAT('b',128),REPEAT('c',128),REPEAT('e',128)); 68EXPLAIN SELECT SUBSTRING(t,64), SUBSTRING(l,256) FROM t1 WHERE t!=l AND l NOT IN ('test1') ORDER BY t, l DESC; 69id select_type table type possible_keys key key_len ref rows Extra 701 SIMPLE t1 range l_t l_t 259 NULL # Using where; Using filesort 71SELECT SUBSTRING(t,64), SUBSTRING(l,256) FROM t1 WHERE t!=l AND l NOT IN ('test1') ORDER BY t, l DESC; 72SUBSTRING(t,64) SUBSTRING(l,256) 73 74 75bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb 76bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb 77 78 79fffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff 80 81 82 83EXPLAIN SELECT SUBSTRING(t,64), SUBSTRING(l,256) FROM t1 FORCE INDEX (l_t) WHERE t!=l AND l NOT IN ('test1') ORDER BY t, l DESC; 84id select_type table type possible_keys key key_len ref rows Extra 851 SIMPLE t1 range l_t l_t 259 NULL # Using where; Using filesort 86SELECT SUBSTRING(t,64), SUBSTRING(l,256) FROM t1 FORCE INDEX (l_t) WHERE t!=l AND l NOT IN ('test1') ORDER BY t, l DESC; 87SUBSTRING(t,64) SUBSTRING(l,256) 88 89 90bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb 91bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb 92 93 94fffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff 95 96 97 98DROP TABLE t1; 99CREATE TABLE t1 ( 100b BLOB, 101t TINYBLOB, 102m MEDIUMBLOB, 103l LONGBLOB, 104pk INT AUTO_INCREMENT PRIMARY KEY, 105INDEX (m(128)) 106) ENGINE=rocksdb; 107SHOW INDEX IN t1; 108Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment 109t1 0 PRIMARY 1 pk A 1000 NULL NULL LSMTREE 110t1 1 m 1 m A 500 128 NULL YES LSMTREE 111INSERT INTO t1 (b,t,m,l) VALUES 112('','','',''), 113('a','b','c','d'), 114('b','d','c','b'), 115('test1','test2','test3','test4'), 116(REPEAT('a',128),REPEAT('b',128),REPEAT('c',128),REPEAT('d',128)), 117(HEX('abcd'),HEX('def'),HEX('a'),HEX('abc')), 118('abc','def','ghi','jkl'), 119('test2','test3','test4','test5'), 120('test3','test4','test5','test6'), 121(REPEAT('b',128),REPEAT('f',128),REPEAT('e',128),REPEAT('d',128)), 122(REPEAT('c',128),REPEAT('b',128),REPEAT('c',128),REPEAT('e',128)); 123EXPLAIN SELECT SUBSTRING(m,128) AS f FROM t1 WHERE m = 'test1' ORDER BY f DESC; 124id select_type table type possible_keys key key_len ref rows Extra 1251 SIMPLE t1 ref m m 131 const # Using where; Using filesort 126SELECT SUBSTRING(m,128) AS f FROM t1 WHERE m = 'test1' ORDER BY f DESC; 127f 128EXPLAIN SELECT SUBSTRING(m,128) AS f FROM t1 IGNORE INDEX FOR ORDER BY (m) WHERE m = 'test1' ORDER BY f DESC; 129id select_type table type possible_keys key key_len ref rows Extra 1301 SIMPLE t1 ref m m 131 const # Using where; Using filesort 131SELECT SUBSTRING(m,128) AS f FROM t1 IGNORE INDEX FOR ORDER BY (m) WHERE m = 'test1' ORDER BY f DESC; 132f 133DROP TABLE t1; 134CREATE TABLE t1 ( 135b BLOB, 136PRIMARY KEY b (b(32)) 137) ENGINE=rocksdb; 138Warnings: 139Warning 1280 Name 'b' ignored for PRIMARY key. 140INSERT INTO t1 (b) VALUES 141('00000000000000000000000000000000'), 142('00000000000000000000000000000001'), 143('00000000000000000000000000000002'); 144SELECT b FROM t1; 145b 14600000000000000000000000000000000 14700000000000000000000000000000001 14800000000000000000000000000000002 149DROP TABLE t1; 150CREATE TABLE t1 ( 151b TINYBLOB, 152PRIMARY KEY b (b(32)) 153) ENGINE=rocksdb; 154Warnings: 155Warning 1280 Name 'b' ignored for PRIMARY key. 156INSERT INTO t1 (b) VALUES 157('00000000000000000000000000000000'), 158('00000000000000000000000000000001'), 159('00000000000000000000000000000002'); 160SELECT b FROM t1; 161b 16200000000000000000000000000000000 16300000000000000000000000000000001 16400000000000000000000000000000002 165DROP TABLE t1; 166CREATE TABLE t1 ( 167b MEDIUMBLOB, 168PRIMARY KEY b (b(32)) 169) ENGINE=rocksdb; 170Warnings: 171Warning 1280 Name 'b' ignored for PRIMARY key. 172INSERT INTO t1 (b) VALUES 173('00000000000000000000000000000000'), 174('00000000000000000000000000000001'), 175('00000000000000000000000000000002'); 176SELECT b FROM t1; 177b 17800000000000000000000000000000000 17900000000000000000000000000000001 18000000000000000000000000000000002 181DROP TABLE t1; 182CREATE TABLE t1 ( 183b LONGBLOB, 184PRIMARY KEY b (b(32)) 185) ENGINE=rocksdb; 186Warnings: 187Warning 1280 Name 'b' ignored for PRIMARY key. 188INSERT INTO t1 (b) VALUES 189('00000000000000000000000000000000'), 190('00000000000000000000000000000001'), 191('00000000000000000000000000000002'); 192SELECT b FROM t1; 193b 19400000000000000000000000000000000 19500000000000000000000000000000001 19600000000000000000000000000000002 197DROP TABLE t1; 198SET GLOBAL ROCKSDB_PAUSE_BACKGROUND_WORK = @ORIG_PAUSE_BACKGROUND_WORK; 199