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.