|
The following test
CREATE TABLE t1 (
|
b BINARY,
|
b20 BINARY(20),
|
v16 VARBINARY(16),
|
v128 VARBINARY(128),
|
INDEX (v16(10))
|
) ENGINE=RocksDB;
|
INSERT INTO t1 (b,b20,v16,v128) VALUES ('a','char1','varchar1a','varchar1b'),('a','char2','varchar2a','varchar2b'),('b','char3','varchar1a','varchar1b'),('c','char4','varchar3a','varchar3b'),('d','char5','varchar4a','varchar3b'),('e','char6','varchar2a','varchar3b');
|
INSERT INTO t1 (b,b20,v16,v128) SELECT b,b20,v16,v128 FROM t1;
|
ANALYZE TABLE t1;
|
EXPLAIN SELECT HEX(SUBSTRING(v16,0,3)) FROM t1 WHERE v16 LIKE 'varchar%';
|
DROP TABLE t1;
|
produces at least two essentially different execution plans:
MariaDB [test]> EXPLAIN SELECT HEX(SUBSTRING(v16,0,3)) FROM t1 WHERE v16 LIKE 'varchar%';
|
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
|
| 1 | SIMPLE | t1 | ALL | v16 | NULL | NULL | NULL | 24 | Using where |
|
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
|
1 row in set (0.00 sec)
|
and
MariaDB [test]> EXPLAIN SELECT HEX(SUBSTRING(v16,0,3)) FROM t1 WHERE v16 LIKE 'varchar%';
|
+------+-------------+-------+-------+---------------+------+---------+------+------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+-------+---------------+------+---------+------+------+-------------+
|
| 1 | SIMPLE | t1 | range | v16 | v16 | 13 | NULL | 10 | Using where |
|
+------+-------------+-------+-------+---------------+------+---------+------+------+-------------+
|
1 row in set (0.00 sec)
|
I think it started happening after the last big merge from upstream, at least now storage_engine-rocksdb.type_binary_indexes test in the storage_engine suite which checks for key in the plan fails regularly, it didn't before.
Maybe it's not important, then indicate so and assign back to me, so I'd disable the test for RocksDB.
|