[SOLVED] partition mysql table by hour basis

partition mysql table by hour basis

My Example table looks like this

CREATE TABLE TEST_1 (
  id int(11) NOT NULL AUTO_INCREMENT,
  createdAt datetime NOT NULL,
  name varchar(45) NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

I'm looking to create a partition on an hourly basis since each hour there will be around 4 million records are saved, I would like to drop the partition after some processing. maximum 5 partitions will be available at a time.

As a starting step tried creating partition

alter table TEST_1 partition by range(createdAt)(
    PARTITION pmin values less than ('2021-11-17 08:00:00'),
    PARTITION p2020111709 values less than ('2020-11-17 09:00:00'),
    PARTITION p2020111710 values less than ('2020-11-17 10:00:00'),
    PARTITION p2020111711 values less than ('2020-11-17 11:00:00'),
    PARTITION pmax VALUES LESS THAN (MAXVALUE)
)

but it throws error saying

Error Code: 1697. VALUES value for partition 'pmin' must have type INT

using range columns it throws error as

Error Code: 1493. VALUES LESS THAN value must be strictly increasing for each partition

I'm not sure how to move forward 🙃

Solution

Caution: In less than 2 years, id INT will overflow.

Is there anything else in the table? If so, why even have id?

What do you do with a list of 20M "names"? And there can be duplicates.

There are other syntax options:

⚈  PARTITION BY RANGE COLUMNS(DATETIME) (5.5.0)
⚈  PARTITION BY RANGE(TIMESTAMP) (version 5.5.15 / 5.6.3)
⚈  PARTITION BY RANGE(TO_SECONDS()) (5.6.0)

The first of those is probably closest to what you have. Just insert COLUMNS.

More comments on PARTITIONing (including the syntaxes above): http://mysql.rjweb.org/doc.php/partitionmaint