1

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 SUBPARTITION.

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.

  • PARTITION BY RANGE ( ( WEEKOFYEAR(DataTime)+54*YEAR(DataTime) ) ) where 54 = max weeks per year (partial week as full). Or PARTITION BY RANGE ( (DATEDIFF(DataTime, someBaseDate) / 7). First variant divides a week overlapped new year start to two different partitions whereas second one do not. – Akina Aug 29 '18 at 4:57
  • is this because of weekofyear('2018-12-31') will result to 1, the same as '2019-01-01'? Then if i use week(date, 5) it will solve this. And then to avoid duplication with next-next year, WEEKOFYEAR()*10000 + YEAR() => which will end up like string stacking but an int (422018, 432018) – nyoto arif Aug 29 '18 at 6:04
  • found out weekofyear is unsable, but yearweek is available from this limitation – nyoto arif Aug 29 '18 at 6:23
0

Note the MariaDB reference manual (not blog), says 'better fix coming in 5.7', the 5.7 manual includes a big note about native partitioning being deprecated in 5.7.17 and therefore native partitioning is available. So really this advice doesn't apply if you have MySQL-5.7.17+.

  • hi @danblack, mind to explain more? i dont understand the implication of this deprecation in generic partitioning handler. I am trying to make-sure the version of mysql we will be using too in the mean time – nyoto arif Aug 29 '18 at 5:49
  • There was a non-native partition storage plugin which was the cause of the maria kb article stating a 50 partition recommended maximum. Since at least MySQL-5.7.17 the native partitioning will mean there isn't a recommended maximum, provided you are using the native mechanism. The 5.7 manual provides details for checking (and where warnings show up of you aren't). So in short, use a recent mysql version and you won't need to worry. – danblack Aug 29 '18 at 8:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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