Is there any plans to support foreign key on partitioned tables?
Thanks
1 Like
Hi @egel
Thanks for posting in the Percona Community Forum.
Answering your question, no, at the moment foreign keys and partitioned tables keep being incompatible.
I suggest you check this blog post that provides a work around for this situation
I hope you find this useful.
1 Like
@egel,
Can you describe your partition usage? Partitioning rarely has any benefit in MySQL due to its simplistic implementation. How are you using partitioning? How many rows in the table? What’s the table size on disk?
1 Like
Sure,
I have a few tables sized between tens of M to 1B rows, total size is not large though, about 2TB.
The majority of queries are “interested” in data that is sliced by specific value that, obviously, becomes natively partition key.
Considering above I am using LIST partitioning.
But partitioning is not only about size and/or performance of queries, this is the way to manage the data, move it to history, move it between servers, partitioning provides the ability to refer to a subset of data as a holistic entity.
And, therefore, ability to design a life cycle of the data.
So, partitioning, in my opinion, is not related to databases, this is a cool concept that sometimes fits amazingly into DBA needs.
Yes, some producers of database systems make it better than others, but, at least till now I don’t see anything “bad” in the way it is implemented in mySQL.
Although, this is bit annoying to add partition key into each unique index but beside it partitioning seems to work flawlessly