MySQL Forums
Forum List  »  Partitioning

MySQL Partitioning based on ID and Week
Posted by: Michael Zatkovetsky
Date: March 16, 2020 11:14AM

Hi! I have a table

CREATE TABLE `acme`.`partitioned_table` (
`id` INT NULL,
`client_id` INT NOT NULL,
`create_datetime` INT NOT NULL,
`some_val` VARCHAR(45) NULL);

I'd like to partition this table in such a way that each client’s data is stored in its own partition based on the client_id AND each partition can only contain data for 1 week based on the create_datetime. This is done so we can drop weekly one week’s worth of data based each client’s own retention policy.

For example, some clients would like to have 3 months of data while others may have longer data retention policies.

I am having a hard time being new to MySQL to come up with a proper partitioning strategy. How can I partition by Week based on the INT column? To throw a curve ball this might be hosted on AWS RDS later.

Many thanks in advance,

M

Options: ReplyQuote


Subject
Views
Written By
Posted
MySQL Partitioning based on ID and Week
195
March 16, 2020 11:14AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.