I am trying to create a hash partition in MySQL 5.7 AWS RDS, cause the table is growing much rapidly approximately with 200 Million Rows in a month.
The table structure is like this and tried to create the partitions like below
itemid bigint(20) NOT NULL AUTO_INCREMENT,
ownerid int(11) NOT NULL,
product_id int(11) NOT NULL,
creation_time datetime DEFAULT NULL,
used_time datetime DEFAULT NULL,
......
......
......
......
PRIMARY KEY (itemid),
UNIQUE KEY owner_product_uidx (ownerid,product_id),
KEY prod_indx (product_id)
)
PARTITION BY HASH(itemid,ownerid,product_id)
PARTITIONS 5;
ENGINE=InnoDB DEFAULT CHARSET=utf8 ;
throws error
Error Code: 1503. A PRIMARY KEY must include all columns in the table's partitioning function.
Tried Partition KEY instead of HASH with
PARTITION BY key(item_id,owner_id,product_id,creation_time;
Still with no luck. Did anyone succeed creating the partition,
Would also be fine i can do it with year(creation_time)-month(creation_time) as partition key.
like (2021-01,2021-02….etc)
Ref: https://dev.mysql.com/doc/refman/5.7/en/partitioning-limitations-partitioning-keys-unique-keys.html
Can someone help.
Best Answer
Partitions have a lot of restrictions You can'_t use HASH with multiple columns
If you want multiple columns they have to be in the primary key, like shown in the example. For your time you see in the second example. you have to make even more sacrifices and if that is what you seek, you have to test it .
db<>fiddle here