Bug #109087 Automatically optimize the insertion order when INSERT

• Submitted: 15 Nov 2022 2:15
• Modified: 15 Nov 2022 7:12
• Reporter: Ye Jinrong
• Status: Verified
• Category: MySQL Server: DML
• Severity: S4 (Feature request)
• Version: 8.0
• OS: Any
• CPU Architecture: Any

[15 Nov 2022 2:15] Ye Jinrong

Description:
table DDL：

```
CREATE TABLE `t` (
  `id` int NOT NULL AUTO_INCREMENT,
  `k` int NOT NULL DEFAULT '0',
  `c` char(120) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `k_1` (`k`)
) ENGINE=InnoDB
```

INSERT SQL like：
```
INSERT INTO t VALUE(100, ...), (99, ...), (98, ...)...(1, ...);
```

It is hoped that the SQL can be automatically optimized to increase according to the primary key ID sequence:
```
INSERT INTO t VALUE(1, ...), (2, ...), (3, ...)...(100, ...);
```

Because it is easy to split the innodb page and expand the table space when INSERT in the former way(in desc). Therefore, we hope to automatic optimization during SQL parsing, automatically optimize INSERT SQL according to the order of primary key columns.

How to repeat:
null

Suggested fix:
null

[15 Nov 2022 7:12] MySQL Verification Team

Hello Ye Jinrong,

Thank you for the feature request!

regards,
Umesh