Is there a way to have mysql automatically create partitions as needed?

We have a large table with columns, date, tag, country. We would like to partition the table by YEAR(date), tag, country, but would like to have mysql create the partitions on the fly. i.e. We don't want to set the number of partitions up front when the partitions are setup and then have to remember to add new partitions in the future.

Let me try answering this one to ensure below 3 objectives.

1). Which partition can be used for mentioned use case ?
2). It should help in removing old partition.
3). It should help in effective querying by picking the partition.

1). Which partition can be used for mentioned use case ?

Hash partition to let Mysql to decide the category. But here you can try to have an integer column for Hash.

YEAR(date) = 4Max digits

tag = 4Max digits

country = 2Max digits (Convert this into a country code)

Add a new column called part_token and set a datatype to Integer Unsigned. It can range from 0 to 4294967295. Any way max prediction to keep the value is 4294th yr and we are at 2017 :)

2). It should help in removing old partition.

You may choose the part_token to remove old partition if decided not to have any more. And try to run backup on slave before removing old partition.

3). It should help in effective querying by picking the partition.

From Mysql 5.6 onwards you can explicitly mention the partition to be queried and it can go on further querying the data based index within the partition.

I.E.


SELECT * FROM big_table PARTITION (2017091922) WHERE user_id='12111';

Lets say user_id is indexed. It should be effective according to the extended explain plan. Please verify this before you push the code.

StackExchange Database Administrators Q#140053, answer score: 2