Description:
This performance improvement added in the past could be further enhanced
https://bugs.mysql.com/bug.php?id=28591
When using a partially matching index then sorting by the natural order of PK requires an unnecessary sort step
How to repeat:
CREATE TABLE `t1` (
`a` int NOT NULL,
`b` int DEFAULT NULL,
`c` int DEFAULT NULL,
`d` int DEFAULT NULL,
PRIMARY KEY (`a`),
KEY `bkey` (`b`, `c`, `d`)
) ENGINE=InnoDB;
Result set of
EXPLAIN SELECT t1.* FROM t1 WHERE t1.b = 1 ORDER BY t1.c, t1.a\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
partitions: NULL
type: ref
possible_keys: bkey
key: bkey
key_len: 5
ref: const
rows: 1
filtered: 100.00
Extra: Using index; Using filesort
is equivalent to that of
EXPLAIN SELECT t1.* FROM t1 WHERE t1.b = 1 ORDER BY t1.c\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
partitions: NULL
type: ref
possible_keys: bkey
key: bkey
key_len: 5
ref: const
rows: 1
filtered: 100.00
Extra: Using index
