| Bug #109601 | Apply partition pruning if all insert rows have non-null values on autoinc field | ||
|---|---|---|---|
| Submitted: | 12 Jan 2023 9:00 | Modified: | 11 Sep 16:34 |
| Reporter: | Hope Lee (OCA) | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 8.0 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | Contribution | ||
[12 Jan 2023 9:02]
Hope Lee
Support partition pruning when autoinc field is assigned non-null value in INSERT command (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.
Contribution: 0001-Support-partition-pruning-when-autoinc-field-is-assi.patch (application/octet-stream, text), 40.67 KiB.
[12 Jan 2023 9:37]
MySQL Verification Team
Hello Hope Lee, Thank you for the report and contribution. regards, Umesh
[3 Jun 8:48]
MySQL Admin
Posted by developer: Bug status updated to 'Patch pending'
[17 Jun 14:48]
MySQL Admin
Posted by developer: Bug status updated to 'Patch approved'
[18 Jun 2:48]
MySQL Admin
Posted by developer: Bug status updated to 'Documenting'
[11 Sep 16:34]
Edward Gilmore
Posted by developer: Added the following note to the MySQL Server 26.7.0 release notes: INSERT statements into a partitioned table could access and lock all partitions when an AUTO_INCREMENT column participated in the partitioning expression, even when every row supplied an explicit deterministic value. Partition pruning now proceeds when every value is constant, non-NULL, and not an auto-generated zero. Pruning remains disabled for nonconstant values, NULL, or zero unless NO_AUTO_VALUE_ON_ZERO applies. Our thanks to Hope Lee for the contribution.
Description: Currently if an auto increment field is part of the partition expression, the optimizer won't do partition pruning when inserting values. But actually, if the values of auto increment field are explicitly assigned in all insert rows and they are neither NULL nor 0(not with NO_AUTO_VALUE_ON_ZERO sql_mode), we can do partition pruning to improve performance. This is also a TODO thing left by the developer in function partition_info::can_prune_insert(): if (table->found_next_number_field) { /* If the field is used in the partitioning expression, we cannot prune. TODO: If all rows have not null values and is not 0 (with NO_AUTO_VALUE_ON_ZERO sql_mode), then pruning is possible! */ if (bitmap_is_set(&full_part_field_set, table->found_next_number_field->field_index())) return false; How to repeat: CREATE TABLE `sbtest1` ( `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 PARTITION BY RANGE (`id`) (PARTITION p0 VALUES LESS THAN (1001) ENGINE = InnoDB, PARTITION p1 VALUES LESS THAN (2001) ENGINE = InnoDB, PARTITION p2 VALUES LESS THAN (3001) ENGINE = InnoDB, PARTITION p3 VALUES LESS THAN (4001) ENGINE = InnoDB, PARTITION p4 VALUES LESS THAN (5001) ENGINE = InnoDB); mysql-8.0.30 > EXPLAIN INSERT INTO sbtest1 VALUES (5, 10, 'aaz', 'kox'), (100, 92, 'hope', 'carmen'); +----+-------------+---------+----------------+------+---------------+------+---------+------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+----------------+------+---------------+------+---------+------+------+----------+-------+ | 1 | INSERT | sbtest1 | p0,p1,p2,p3,p4 | ALL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | +----+-------------+---------+----------------+------+---------------+------+---------+------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec) We know the above two records will be inserted to partition p0, but the explained result shows this SQL will involve all partitions. Suggested fix: Apply partition pruning when all rows have non-null values on the auto increment field. See the following patch.