|
Found this while working on MDEV-14433:
Found an interesting mismatch between query plans that depends on the index number:
Upstream, MySQL 5.6
CREATE TABLE t3(
|
pk int primary key,
|
a varchar(10) NOT NULL,
|
e int(11) DEFAULT 0,
|
KEY (a)
|
) ENGINE=ROCKSDB DEFAULT CHARSET=utf8;
|
insert into t3 values (1,1,1),(2,2,2);
|
explain select a from t3 where a <'zzz';
|
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
|
| 1 | SIMPLE | t3 | range | a | a | 32 | NULL | 2 | Using where |
|
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
|
CREATE TABLE t4(
|
pk int,
|
a varchar(10) NOT NULL,
|
e int(11) DEFAULT 0,
|
KEY (a)
|
) ENGINE=ROCKSDB DEFAULT CHARSET=utf8;
|
insert into t4 values (1,1,1),(2,2,2);
|
explain select a from t4 where a <'zzz';
|
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
|
| 1 | SIMPLE | t4 | range | a | a | 32 | NULL | 1 | Using where |
|
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
|
MariaDB:
CREATE TABLE t3(
|
pk int primary key,
|
a varchar(10) NOT NULL,
|
e int(11) DEFAULT 0,
|
KEY (a)
|
) ENGINE=ROCKSDB DEFAULT CHARSET=utf8;
|
insert into t3 values (1,1,1),(2,2,2);
|
explain select a from t3 where a <'zzz';
|
+------+-------------+-------+-------+---------------+------+---------+------+------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+-------+---------------+------+---------+------+------+-------------+
|
| 1 | SIMPLE | t3 | range | a | a | 32 | NULL | 1 | Using where |
|
+------+-------------+-------+-------+---------------+------+---------+------+------+-------------+
|
CREATE TABLE t4(
|
pk int,
|
a varchar(10) NOT NULL,
|
e int(11) DEFAULT 0,
|
KEY (a)
|
) ENGINE=ROCKSDB DEFAULT CHARSET=utf8;
|
insert into t4 values (1,1,1),(2,2,2);
|
explain select a from t4 where a <'zzz';
|
+------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
|
| 1 | SIMPLE | t4 | index | a | a | 32 | NULL | 0 | Using where; Using index |
|
+------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
|
|