Partitioning in MySql 5.7 with Primary Key and Unique Key

Question

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.

Answer 1

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 .

CREATE TABLE abc
 (
  itemid bigint(20) NOT NULL AUTO_INCREMENT,
  ownerid int(11) NOT NULL,
  product_id int(11) NOT NULL,
  creation_time datetime DEFAULT NULL,
  yearmonth VARCHAR(20) GENERATED ALWAYS AS (CONCAT(YEAR(creation_time),'-',MONTH(creation_time))),
  used_time datetime DEFAULT NULL,
  PRIMARY KEY (itemid,ownerid,product_id),
  UNIQUE KEY owner_product_uidx (itemid,ownerid,product_id),
  KEY prod_indx (product_id)
) ENGINE=INNODB
PARTITION BY KEY(itemid,ownerid,product_id)
 PARTITIONS 5;

✓

CREATE TABLE abc2
 (
  itemid bigint(20) NOT NULL AUTO_INCREMENT,
  ownerid int(11) NOT NULL,
  product_id int(11) NOT NULL,
  creation_time datetime NOT NULL,
  month_ tinyint NOT NULL,
  used_time datetime DEFAULT NULL,
  PRIMARY KEY (itemid,creation_time,month_),
  UNIQUE KEY owner_product_uidx (itemid,creation_time,month_),
  KEY prod_indx (product_id)
) ENGINE=INNODB
PARTITION BY RANGE (YEAR(`creation_time`))
SUBPARTITION BY KEY (month_) 
SUBPARTITIONS 12 (
    PARTITION p2011 VALUES LESS THAN (2012),
    PARTITION p2012 VALUES LESS THAN (2013),
    PARTITION p2013 VALUES LESS THAN (2014),
    PARTITION p2014 VALUES LESS THAN (2015),
    PARTITION p2015 VALUES LESS THAN (2016),
    PARTITION p2016 VALUES LESS THAN (2017),
    PARTITION p2017 VALUES LESS THAN (2018),
    PARTITION p2018 VALUES LESS THAN (2019),
    PARTITION p2019 VALUES LESS THAN (2020),
    PARTITION p2020 VALUES LESS THAN (2021),
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

✓

db<>fiddle here