MySQL - Uneven Distribution of Data into Partitions When Using Key Partitioning
<ol>
<li><p>I'm using the InnoDB engine on MySQL 5.7.</p></li>
<li><p>I have a table where one of the columns is a (non-unique) three-letter country code (e.g. "SGP" for Singapore, "JPN" for Japan, etc).</p></li>
<li><p>For most of my queries, this country code column is the first WHERE clause I filter by (e.g. <code>WHERE COUNTRY_CODE = 'SGP'</code>)</p></li>
<li><p>Hence, I want to (sub-)partition the table by this column. Since most of my queries will be on a single country code, they will only hit one partition in this way.</p></li>
<li><p>However, due to the large number of different country codes, I do not want to use LIST partitioning where I have to explicitly cater for every single country code.</p></li>
<li><p>So I used KEY partitioning, with 8 partitions. I thought that key partitioning, whereby the value is hashed, would give me a more-or-less even distribution over the 8 partitions (don't have to be perfect).</p></li>
<li><p>However, what I experienced is that out of the 8 partitions, 4 of them are completely untouched.</p></li>
</ol>

<p>This is an abstract of my CREATE TABLE statement:</p>

<pre><code>CREATE TABLE TBL_EATING_PLACES (
    ID INT UNSIGNED AUTO_INCREMENT NOT NULL,
    TYPE_OF_FOOD SMALLINT UNSIGNED NOT NULL,
    SUBTYPE_OF_FOOD SMALLINT UNSIGNED NOT NULL,
    COUNTRY_CODE CHAR(3) NOT NULL,
    ADDRESS VARCHAR(255),
    ...
    OTHER_NON_RELEVANT_COLUMNS ...,
    ...,
    CONSTRAINT PKEY PRIMARY KEY (ID, TYPE_OF_FOOD, SUBTYPE_OF_FOOD, COUNTRY_CODE)
)
ENGINE = InnoDB
PARTITION BY LIST COLUMNS(TYPE_OF_FOOD, SUBTYPE_OF_FOOD) SUBPARTITION BY KEY(COUNTRY_CODE) SUBPARTITIONS 8 (
    PARTITION P_1_1 VALUES IN ((1, 1)),
    PARTITION P_1_2 VALUES IN ((1, 2)),
    PARTITION P_2_1 VALUES IN ((2, 1)),
    PARTITION P_1_2 VALUES IN ((2, 2)),
    PARTITION P_1_3 VALUES IN ((2, 2)),
);
</code></pre>

<p>Is there anything wrong with how I'm doing the KEY partitioning such that it ended up only hitting half of the partitions?</p>