Description:
As it stands, when a table has a primary key defined, the partition key must be part of that primary key. While this design may have its reasons, it can present challenges in practice.
In modern application development, most tables are designed with a primary key by default. The requirement that the partitioning key must be included in the primary key can force developers to alter otherwise well-structured schemas, and may lead to conflicts with ORM frameworks that expect primary keys to remain stable and minimal.
How to repeat:
CREATE TABLE `user_role` (
`id` bigint NOT NULL,
`user_id` int DEFAULT NULL,
`role_id` int DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uk_user_id` (`user_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
ALTER TABLE user_role PARTITION BY HASH(user_id) PARTITIONS 4;
Suggested fix:
Would it be possible to consider allowing partitioning by a unique key rather than strictly requiring the primary key? Even if insert operations may incur slightly more overhead due to additional uniqueness checks, we believe that in most scenarios where partitioning is used, the primary motivation is to improve query performance on large datasets, not necessarily to optimize inserts.
We hope this perspective can contribute constructively to future improvements in MySQL's partitioning functionality.
