Description:
If ORDER BY specifies unique key followed by any other column, optimizer should recognize that anything following unique key /PK in ORDER BY is a noop and not fall back into a filesort, that on a table that has PK of (a), a query like:
SELECT * FROM t OREDR BY a, b
should be rewritten internally to
SELECT * FROM t ORDER BY a
How to repeat:
mysql> select * from t1 order by a, b limit 1;
+---+------+------+------+
| a | b | c | d |
+---+------+------+------+
| 1 | 0 | 1 | NULL |
+---+------+------+------+
1 row in set (0.38 sec)
mysql> explain select * from t1 order by a, b limit 1;
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------+
| 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 522614 | 100.00 | Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------+
1 row in set, 1 warning (0.01 sec)
mysql> show create table t1;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1 | CREATE TABLE `t1` (
`a` int(11) NOT NULL AUTO_INCREMENT,
`b` int(11) DEFAULT NULL,
`c` int(11) DEFAULT NULL,
`d` varchar(255) DEFAULT NULL,
PRIMARY KEY (`a`),
KEY `b_c` (`b`,`c`)
) ENGINE=InnoDB AUTO_INCREMENT=786406 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
Suggested fix:
optimize away useless ORDER BY specifications
