1

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.

1 Answer 1

0

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

1
  • Thanks , i need to see if we can change the keys to get this partition implemented, as we have some references to this table
    – Chandu
    Feb 16, 2021 at 9:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.