MySQL Partitioning and Pain

The other day I had to do the partitioning. A million records will be added to the table every day and it is expected that these records will be taken to provide various reports and graphs.

I’ll say right away that I did this not for the first time, before I had successfully done the partitioning of the site into the bitrix, something like this:

Step 1. Remove AUTO INCREMENT from the b_iblock_element table.

ALTER TABLE b_iblock_element MODIFY ID INT (11) NOT NULL

Step 2. Remove the PRIMARY key from the table.

ALTER TABLE b_iblock_element DROP PRIMARY KEY

Step 3. Create a new PRIMARY KEY, which will contain the previous key and IBLOCK_ID, which is divided into partitions.

ALTER TABLE b_iblock_element ADD CONSTRAINT id_iblock_id PRIMARY KEY (ID, IBLOCK_ID)

Step 4. Return the AUTO INCREMENT.

ALTER TABLE b_iblock_element MODIFY ID INT (11) NOT NULL AUTO_INCREMENT

Step 5. Finally, we do the splitting into 10 parts.

ALTER TABLE b_iblock_element PARTITION BY HASH (IBLOCK_ID) PARTITIONS 10;

')
It's pretty simple. The function by which the partition goes may contain keys, but all these keys must be in PRIMARY KEY.

Now I had to break another table, and I would like to break it into 2 fields at once: by type and date. And the date wanted to be broken down by months and the data stored no more than a year.

Step 1. The first thing I encountered was that I had to remove all foreign keys. Mysql does not work with them when partitioning.

ALTER TABLE table_name DROP CONSTRAINT fk_key_name

Step 2. I had to change the timestamp field to datetime. At the timestamp MySQL swore

ERROR 1486 (HY000): Constant, random or timezone-dependent expressions

ALTER TABLE table_name CHANGE `date`` date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP;

Step 3. I had to add date and type fields to PRIMARY KEY.

Step 4. I decided to use subpartitions to split into two fields.

ALTER TABLE table_data PARTITION BY LIST (MONTH (`date`))
SUBPARTITION BY HASH (`type_id`) SUBPARTITIONS 10
(
PARTITION p1 VALUES IN (1),
PARTITION p2 VALUES IN (2),
PARTITION p3 VALUES IN (3),
PARTITION p4 VALUES IN (4),
PARTITION p5 VALUES IN (5),
PARTITION p6 VALUES IN (6),
PARTITION p7 VALUES IN (7),
PARTITION p8 VALUES IN (8),
PARTITION p9 VALUES IN (9),
PARTITION p10 VALUES IN (10),
PARTITION p11 VALUES IN (11),
PARTITION p12 VALUES IN (12)
);

But although MySQL supports the MONTH function for partitioning and I use LIST / RANGE along with HASH / KEY, that is, MySQL subpartitions supports only at this level, I did not succeed.

The explain partitions command of the query showed that when I select an interval by date, all partisans are used. Google told me that I cannot use the MONTH function, but only the TO_DAYS, YEAR and TO_SECONDS functions. It was necessary to make all the same partitions static:

ALTER TABLE table_data PARTITION BY RANGE (to_days (`date`))
SUBPARTITION BY HASH (`type_id`) SUBPARTITIONS 10
(
PARTITION p01 VALUES LESS THAN (to_days ('2015-10-01')),
PARTITION p02 VALUES LESS THAN (to_days ('2015-11-01')),
PARTITION p03 VALUES LESS THAN (to_days ('2015-12-01')),
PARTITION p04 VALUES LESS THAN (to_days ('2016-01-01')),
PARTITION p05 VALUES LESS THAN (to_days ('2016-02-01')),
PARTITION p06 VALUES LESS THAN (to_days ('2016-03-01')),
PARTITION p07 VALUES LESS THAN (to_days ('2016-04-01')),
PARTITION p08 VALUES LESS THAN (to_days ('2016-05-01')),
PARTITION p09 VALUES LESS THAN (to_days ('2016-06-01')),
PARTITION p10 VALUES LESS THAN (to_days ('2016-07-01')),
PARTITION p11 VALUES LESS THAN (to_days ('2016-08-01')),
PARTITION p12 VALUES LESS THAN (to_days ('2016-09-01')),
PARTITION p13 VALUES LESS THAN (to_days ('2016-10-01')),
PARTITION p14 VALUES LESS THAN (to_days ('2016-11-01')),
PARTITION p15 VALUES LESS THAN (to_days ('2016-12-01')),
PARTITION p16 VALUES LESS THAN (to_days ('2017-01-01')),
PARTITION p18 VALUES LESS THAN (to_days ('2017-02-01')),
PARTITION p19 VALUES LESS THAN (to_days ('2017-03-01')),
PARTITION p20 VALUES LESS THAN (to_days ('2017-04-01')),
PARTITION pmaxval VALUES LESS THAN MAXVALUE
);

The explain partitions team finally showed that it wouldn't use a bunch of tables. Hopefully this will work, and thanks to MySQL for the pain.