2

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.

migrated from stackoverflow.com May 31 '16 at 22:21

This question came from our site for professional and enthusiast programmers.

1

Why do you want to use partitions? "Large" is not a reason; I have seen billion-row tables that work just fine without partitioning. PARTITIONing are unlikely to provide any benefit.

If you will be deleting "old" data based on the YEAR(date), that is a good use case for partitioning.

More discussion shows how to DROP the oldest partition, etc.

  • We were thinking about backups, but are you saying the only real reason would be for pruning? – bpeikes Jun 1 '16 at 13:11
  • Pruning, itself, rarely addes efficiency. How would you do the backup? Perhaps with the 4th use case in my blog? – Rick James Jun 1 '16 at 17:04
  • Let me clarify, my question was if fast pruning was the main reason for using partitioning. The closer I look at partitioning, the less it looks useful. – bpeikes Jun 1 '16 at 17:29
  • Pruning is no faster than indexing. So if a suitable composite index can be devised, then partitioning has no performance benefit. – Rick James Jun 1 '16 at 18:11
0

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.

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.