how to create Dynamic number of partitions using key based table partitioning in MYSQL?

Question

I'm trying to create Partitioned table using mysql, but I don't want to specify the number of partitions. for example in the given table i will have over 100k records for each region. I don't know the regions. they will coming later. so the number of partitions should increase based on the regions, but below syntax is creating single partition even when i insert multiple rows with different values.

I didn't mention partitions count.

  CREATE TABLE `members` (
      `firstname` varchar(25) NOT NULL,
      `lastname` varchar(25) NOT NULL,
      `username` varchar(16) NOT NULL,
      `email` varchar(35) DEFAULT NULL,
      `region` varchar(10) DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1
     PARTITION BY KEY (region);

score 0 · Accepted Answer · answered Oct 25 '17 at 06:45

https://dev.mysql.com/doc/refman/5.7/en/partitioning-hash.html says:

If you do not include a PARTITIONS clause, the number of partitions defaults to 1.

The same behavior applies to KEY partitioning.

You seem to think you need one partition for each region. You don't. You may have more than one region stored in each partition. Of course this must be true if you have for example 8 partitions but you store 12 different regions. This is not a problem.

You can use ALTER TABLE to change the number of partitions in the KEY or HASH partitioning method, but it will remain fixed until you use ALTER TABLE again, and that will redistribute the rows over the new set of partitions.

You might instead want to use LIST partitioning, so you can control which partition each of your regions is stored in. Again, you can use ALTER TABLE to change the partition definitions, so you can support new regions.

I will also comment that it is not necessary to use partitioning at all. Many developers overestimate how much partitioning will help. It's more likely that simply using indexes properly will be good enough to give your queries high performance.

score 0 · Answer 2 · answered Oct 27 '17 at 14:03

PARTITION BY KEY() or LIST() or HASH() is probably useless if your goal is added performance. Instead, having suitable index(es) starting with region will do just as well.

No, it is not practical add new partitions when you encounter new "regions".

With only 100K rows per partition, your table barely qualifies for bothering with PARTITIONing.

There are limits on how many PARTITIONs. The hard limit is 8K; the practical limit is 50.

More discussion .

how to create Dynamic number of partitions using key based table partitioning in MYSQL?

2 Answers2