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)