We would like to take the advantage of MySql horizontal partitioning for performance improvement and data separation. We have gone through the MySql documentation and found that there won’t be any foreign key support for the partitioned tables and it’s referring tables. We have a work around with triggers to check the foreign key values manually, but this will be a huge impact if we have to verify more tables and will be having maintenance tasks if we would like to add new tables.
Please let us know the pros-cons and how partitioning can be achieved with foreign key support.
And also is there any alternate solution for partitioning to get performance improvement and data separation.