Partitioning by range columns unexpected behavior

Question

I have MySQL table partitioned by range columns (c_id and created_at) and I created 2 partitions:

logs_1_2020 (c_id less than 2 and created less than 2021-01-01 00:00:00)
logs_1_2021 (c_id less than 2 and created less than 2022-01-01 00:00:00)

When I run

INSERT INTO example_log_table (c_id, data, created)
    VALUES (1, 'test', '2021-10-24 18:16:08')

I'm supposed to find the result stored in logs_1_2021, but I was shocked when I found her in logs_1_2020.

Does anyone have an explanation for that?

This table SQL generator:

CREATE TABLE example_log_table (
                        id int auto_increment ,
                        c_id int,
                        data TEXT NOT NULL,
                        created DATETIME NOT NULL,
                        primary key (id,c_id,created)
) PARTITION BY RANGE columns (c_id,created)(
    PARTITION logs_1_2020 VALUES LESS THAN  (2,'2021-01-01 00:00:00'),
    PARTITION logs_1_2021 VALUES LESS THAN  (2,'2022-01-01 00:00:00')
);

score 0 · Answer 1 · answered Oct 24 '21 at 16:46

When you use multiple columns as your partitioning key, the placement is based on tuple comparison. You can test if a tuple is less than another tuple this way (MySQL 8.0):

select row(1, '2021-10-24 18:16:08') < row(2, '2021-01-01 00:00:00');
+---------------------------------------------------------------+
| row(1, '2021-10-24 18:16:08') < row(2, '2021-01-01 00:00:00') |
+---------------------------------------------------------------+
|                                                             1 |
+---------------------------------------------------------------+

The rules for tuple inequality comparison are tricky. I suggest you read https://dev.mysql.com/doc/refman/8.0/en/partitioning-columns-range.html and https://dev.mysql.com/doc/refman/8.0/en/comparison-operators.html#operator_less-than carefully.

For row comparisons, (a, b) < (x, y) is equivalent to:
(a < x) OR ((a = x) AND (b < y))

In this case, 1 is less than 2, so the tuple you inserted is less than the tuple that defines the upper bound of partition logs_1_2020.

You can also think about how the data would be sorted if you were to query a set of rows with ORDER BY c_id,created. It would sort by c_id first, and then only in cases of ties on c_id it would sort the ties by created.

WOW it's using tuple comparison ! Is there a way to make it insert data into partition based on column values ? Example: i want "logs_1_2021" partition has all data for c_id less than 2 and created less than "2022-01-01 00:00:00" — es code, Oct 25 '21 at 11:15
In the PostgreSQL when i'm using PARTITION BY RANGE (c_id, created_at) and created partition FOR VALUES FROM (1, '2020-01-01 00:00:00') TO (1, '2021-01-01 00:00:00'') and other one FOR VALUES FROM (1, '2021-01-01 00:00:00') TO (1, '2022-01-01 00:00:00'') when try to insert (1,'2021-10-01 00:00:00') will inserted at second partition not other because this partition created for those values i need to make this behavior at Mysql — es code, Oct 25 '21 at 11:28

score 0 · Answer 2 · answered Oct 25 '21 at 00:16

Unless you have some good reason for that Partitioning, Drop it and change the indexes to

PRIMARY KEY(c_id, created, id),
INDEX(id)

If you expect to have lots of data and wish do delete "old data", the PARTITION BY RANGE over just created; this facilitates periodic DROP PARTITION. And the two indexes above are still valid and useful.

score 0 · Accepted Answer · answered Oct 31 '21 at 11:48

After searching a lot for Is there a way to make Mysql insert data into partition based on column values not tuple comparison I decide to make my partitions like that :

        PARTITION logs_1_2020 VALUES LESS THAN  (1,'2021-01-01'),
        PARTITION logs_2_2020 VALUES LESS THAN  (2,'2021-01-01'),
        PARTITION logs_2_2021 VALUES LESS THAN  (2,'2022-01-01')

And at insertion i'm insert with exact first parameter to make Mysql compare the second parameter is less or not.

So when run :

INSERT INTO example_log_table (c_id, created) VALUES (2, '2021-10-21')

Will be inserted at logs_2_2021 because c_id is matched and created is less than created at second row

Partitioning by range columns unexpected behavior

3 Answers3

Linked