Unable to partition in MySQL Sakila database

Question

[CLOSED] I try to write a query in Sakila Database with MySQL Workbench 8.0 that partitioning the customer table into 5 partitions using the hash type on the customer_id. Check the partitioning information from the INFORMATION_SCHEMA.PARTITIONS table. My code like this:

ALTER TABLE rental PARTITION BY HASH(customer_id) PARTITIONS 5;

When I executed, my action output like

Error Code: 1506. Foreign keys are not yet supported in conjunction with partitioning

I have checked that the customer table just has 3 foreign keys but the customer_id isn't a foreign key in this table. So what did I do wrong? Thanks to read my question.

Answer 1

I suppose you are using the default engine InnoDB, which does not support partitions with foreign key existing. Here is the reference from the official MySQL web:

InnoDB foreign keys and MySQL partitioning are not compatible. Partitioned InnoDB tables cannot have foreign key references, nor can they have columns referenced by foreign keys. InnoDB tables which have or which are referenced by foreign keys cannot be partitioned.

However, if it's an FK table that requires partitioning(not the parent table), we can split it into multiple tables. Note: This is like sharding, which splits a table across different DB servers. But here we do it on the same DB instance.

Suppose we have a vendors table and an invoices table. The invoices table has a column called vendor_id which references its counterpart in the vendors table.

create table vendors (vendor_id int primary key, vendor_name varchar(30));

insert vendors values(1,'azy'),
(2,'bmy'),
(3,'cczy'),
(4,'badfas'),
(5,'fdsww')
;


create table invoices (invoice_id int primary key, vendor_id int,foreign key(vendor_id) references vendors(vendor_id));

insert invoices values(1,3),
(2,1),
(3,2),
(4,5),
(5,3),
(6,5),
(7,2),
(8,4),
(9,3),
(10,1);

We would like to split the invoices table into 3 tables:

create table invoices_0 (invoice_id int primary key, vendor_id int, foreign key(vendor_id) references vendors(vendor_id));

create table invoices_1 (invoice_id int primary key, vendor_id int, foreign key(vendor_id) references vendors(vendor_id));

create table invoices_2 (invoice_id int primary key, vendor_id int, foreign key(vendor_id) references vendors(vendor_id));

If we want to populate them based on the vendor_id(splitting key) , we can use a hashing algorithm with the mod operator:

insert invoices_0 select * from invoices where vendor_id mod 3=0;
insert invoices_1 select * from invoices where vendor_id mod 3=1;
insert invoices_2 select * from invoices where vendor_id mod 3=2;

select * from invoices_0;
+------------+-----------+
| invoice_id | vendor_id |
+------------+-----------+
|          1 |         3 |
|          5 |         3 |
|          9 |         3 |
+------------+-----------+

select * from invoices_1;
+------------+-----------+
| invoice_id | vendor_id |
+------------+-----------+
|          2 |         1 |
|         10 |         1 |
|          8 |         4 |
+------------+-----------+

select * from invoices_2;
+------------+-----------+
| invoice_id | vendor_id |
+------------+-----------+
|          3 |         2 |
|          7 |         2 |
|          4 |         5 |
|          6 |         5 |
+------------+-----------+

There are several points to note.

1. The way of using hash is good to query for a constant value of the splitting key (vendor_id), but not ranged values. If the latter is weighted more in querying jobs, consider splitting tables using ranges.
   

2. To know which split table to use when querying for a single value of the splitting key, we can apply an mechanism on the application side by calculating which split table to use, then query the corresponding table. Or use a stored procedure which takes the key column value as an in parameter, and determines which table to use. (Using a procedure is not possible for sharding as a procedure cannot cross DB servers) The similar job applies to INSERT statements.
   

3. To get the complete set from the split tables, UNION is required.
   

4. The parent table (vendors) should not be split, as an FK table can not references multiple parents.

5. Consider making the splitting number bigger than currently required if the number is going to increase in the future, as relocating can be a tedious job which requires fusing all the split tables into one and do the splitting calculation.