I cannot find a good keyword to search for this kind of question, so here you go:
Suppose i have this table:
CREATE TABLE test ( tr_uuid BINARY(16) NOT NULL, Customer VARCHAR(10) NOT NULL, Data BLOB NO NULL, DataTime DATETIME NOT NULL, PRIMARY KEY (tr_uuid, DataTime) ); CREATE INDEX idx_test ON test(Customer);
Are there any bad side if I have this way of partitioning (monthly):
ALTER TABLE test PARTITION BY RANGE ( ( MONTH(DataTime)*10000+YEAR(DataTime) ) ) ( PARTITION p_422018 VALUES LESS THAN (422018), PARTITION p_432018 VALUES LESS THAN (432018), PARTITION p_future VALUES LESS THAN (MAXVALUE) );
or like this weekly:
ALTER TABLE test PARTITION BY RANGE ( YEARWEEK(DataTime) ) ( PARTITION p_test_201834 VALUES LESS THAN (201834), PARTITION p_test_201835 VALUES LESS THAN (201835), PARTITION p_test_201836 VALUES LESS THAN (201836), PARTITION p_test_201837 VALUES LESS THAN (201837), PARTITION p_test_future VALUES LESS THAN (MAXVALUE) );
The idea is to partition weekly/monthly every year but I dont want to use
Another thing, if there is requirement to store the data for 2 years with data growth of ~6mil/week, and the chance to query the data is quite rare. Using above way will end up with 53x2 partitions after 2 years, according to this blog, 50 partitions will make it inefficient. So are there any better solution for partitioning?
The purpose of partition is not only for query performance, but also to delete the old data smoothly.