SET @ORIG_PAUSE_BACKGROUND_WORK = @@ROCKSDB_PAUSE_BACKGROUND_WORK; SET GLOBAL ROCKSDB_PAUSE_BACKGROUND_WORK = 1; DROP TABLE IF EXISTS t1; CREATE TABLE t1 ( b BLOB, t TINYBLOB, m MEDIUMBLOB, l LONGBLOB, PRIMARY KEY b (b(32)) ) ENGINE=rocksdb; SHOW INDEX IN t1; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment t1 0 PRIMARY 1 b A 1000 32 NULL LSMTREE INSERT INTO t1 (b,t,m,l) VALUES ('','','',''), ('a','b','c','d'), ('b','d','c','b'), ('test1','test2','test3','test4'), (REPEAT('a',128),REPEAT('b',128),REPEAT('c',128),REPEAT('d',128)), (HEX('abcd'),HEX('def'),HEX('a'),HEX('abc')), ('abc','def','ghi','jkl'), ('test2','test3','test4','test5'), ('test3','test4','test5','test6'), (REPEAT('b',128),REPEAT('f',128),REPEAT('e',128),REPEAT('d',128)), (REPEAT('c',128),REPEAT('b',128),REPEAT('c',128),REPEAT('e',128)); EXPLAIN SELECT SUBSTRING(b,16) AS f FROM t1 WHERE b IN ('test1','test2') ORDER BY f; id select_type table type possible_keys key key_len ref rows Extra # # # # # PRIMARY # # # # SELECT SUBSTRING(b,16) AS f FROM t1 WHERE b IN ('test1','test2') ORDER BY f; f EXPLAIN SELECT SUBSTRING(b,16) AS f FROM t1 USE INDEX () WHERE b IN ('test1','test2') ORDER BY f; id select_type table type possible_keys key key_len ref rows Extra # # # # # NULL # # # # SELECT SUBSTRING(b,16) AS f FROM t1 USE INDEX () WHERE b IN ('test1','test2') ORDER BY f; f DROP TABLE t1; CREATE TABLE t1 ( b BLOB, t TINYBLOB, m MEDIUMBLOB, l LONGBLOB, pk INT AUTO_INCREMENT PRIMARY KEY, UNIQUE INDEX l_t (l(256),t(64)) ) ENGINE=rocksdb; SHOW INDEX IN t1; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment t1 0 PRIMARY 1 pk # # NULL NULL # # t1 0 l_t 1 l # # 256 NULL # # t1 0 l_t 2 t # # 64 NULL # # INSERT INTO t1 (b,t,m,l) VALUES ('','','',''), ('a','b','c','d'), ('b','d','c','b'), ('test1','test2','test3','test4'), (REPEAT('a',128),REPEAT('b',128),REPEAT('c',128),REPEAT('d',128)), (HEX('abcd'),HEX('def'),HEX('a'),HEX('abc')), ('abc','def','ghi','jkl'), ('test2','test3','test4','test5'), ('test3','test4','test5','test6'), (REPEAT('b',128),REPEAT('f',128),REPEAT('e',128),REPEAT('d',128)), (REPEAT('c',128),REPEAT('b',128),REPEAT('c',128),REPEAT('e',128)); EXPLAIN SELECT SUBSTRING(t,64), SUBSTRING(l,256) FROM t1 WHERE t!=l AND l NOT IN ('test1') ORDER BY t, l DESC; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range l_t l_t 259 NULL # Using where; Using filesort SELECT SUBSTRING(t,64), SUBSTRING(l,256) FROM t1 WHERE t!=l AND l NOT IN ('test1') ORDER BY t, l DESC; SUBSTRING(t,64) SUBSTRING(l,256) bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb fffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff EXPLAIN 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; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range l_t l_t 259 NULL # Using where; Using filesort 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; SUBSTRING(t,64) SUBSTRING(l,256) bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb fffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff DROP TABLE t1; CREATE TABLE t1 ( b BLOB, t TINYBLOB, m MEDIUMBLOB, l LONGBLOB, pk INT AUTO_INCREMENT PRIMARY KEY, INDEX (m(128)) ) ENGINE=rocksdb; SHOW INDEX IN t1; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment t1 0 PRIMARY 1 pk A 1000 NULL NULL LSMTREE t1 1 m 1 m A 500 128 NULL YES LSMTREE INSERT INTO t1 (b,t,m,l) VALUES ('','','',''), ('a','b','c','d'), ('b','d','c','b'), ('test1','test2','test3','test4'), (REPEAT('a',128),REPEAT('b',128),REPEAT('c',128),REPEAT('d',128)), (HEX('abcd'),HEX('def'),HEX('a'),HEX('abc')), ('abc','def','ghi','jkl'), ('test2','test3','test4','test5'), ('test3','test4','test5','test6'), (REPEAT('b',128),REPEAT('f',128),REPEAT('e',128),REPEAT('d',128)), (REPEAT('c',128),REPEAT('b',128),REPEAT('c',128),REPEAT('e',128)); EXPLAIN SELECT SUBSTRING(m,128) AS f FROM t1 WHERE m = 'test1' ORDER BY f DESC; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref m m 131 const # Using where; Using filesort SELECT SUBSTRING(m,128) AS f FROM t1 WHERE m = 'test1' ORDER BY f DESC; f EXPLAIN SELECT SUBSTRING(m,128) AS f FROM t1 IGNORE INDEX FOR ORDER BY (m) WHERE m = 'test1' ORDER BY f DESC; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref m m 131 const # Using where; Using filesort SELECT SUBSTRING(m,128) AS f FROM t1 IGNORE INDEX FOR ORDER BY (m) WHERE m = 'test1' ORDER BY f DESC; f DROP TABLE t1; CREATE TABLE t1 ( b BLOB, PRIMARY KEY b (b(32)) ) ENGINE=rocksdb; INSERT INTO t1 (b) VALUES ('00000000000000000000000000000000'), ('00000000000000000000000000000001'), ('00000000000000000000000000000002'); SELECT b FROM t1; b 00000000000000000000000000000000 00000000000000000000000000000001 00000000000000000000000000000002 DROP TABLE t1; CREATE TABLE t1 ( b TINYBLOB, PRIMARY KEY b (b(32)) ) ENGINE=rocksdb; INSERT INTO t1 (b) VALUES ('00000000000000000000000000000000'), ('00000000000000000000000000000001'), ('00000000000000000000000000000002'); SELECT b FROM t1; b 00000000000000000000000000000000 00000000000000000000000000000001 00000000000000000000000000000002 DROP TABLE t1; CREATE TABLE t1 ( b MEDIUMBLOB, PRIMARY KEY b (b(32)) ) ENGINE=rocksdb; INSERT INTO t1 (b) VALUES ('00000000000000000000000000000000'), ('00000000000000000000000000000001'), ('00000000000000000000000000000002'); SELECT b FROM t1; b 00000000000000000000000000000000 00000000000000000000000000000001 00000000000000000000000000000002 DROP TABLE t1; CREATE TABLE t1 ( b LONGBLOB, PRIMARY KEY b (b(32)) ) ENGINE=rocksdb; INSERT INTO t1 (b) VALUES ('00000000000000000000000000000000'), ('00000000000000000000000000000001'), ('00000000000000000000000000000002'); SELECT b FROM t1; b 00000000000000000000000000000000 00000000000000000000000000000001 00000000000000000000000000000002 DROP TABLE t1; SET GLOBAL ROCKSDB_PAUSE_BACKGROUND_WORK = @ORIG_PAUSE_BACKGROUND_WORK;