Bug #115969 MySQL need not sort with ORDER BY primary_key on InnoDB on partial index match
Submitted: 30 Aug 2024 13:59 Modified: 2 Sep 2024 10:34
Reporter: Tanel K Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S5 (Performance)
Version:8.0.39 OS:Any
Assigned to: CPU Architecture:Any

[30 Aug 2024 13:59] Tanel K
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
[2 Sep 2024 10:34] MySQL Verification Team
Hello Tanel K,

Thank you for the report and feedback.

regards,
Umesh