Partitioning in aurora mysql

Question

Hello All,  
We have not used partitioning in mysql before. Now that we are opting for daily range partitioning on a date column of a table in AWS Aurora mysql. I have few questions around same

1)In other databases (Like oracle) if we want to have an index to be local then any unique or primary key must contain the partition key column in it. Is same holds correct for mysql too?  
   
2)I believe, if a partition doesn't exist for a specific day/date and if someone trying to insert data for that same date, it will fail the insert query. So, we should have the required partitions created beforehand. In that context, for adding new future partitions, we must manually add the partitions to the table. But is there exists some automated feature which will automatically add the new partition (without making the insert query fail) for a day if the data for that same day getting inserted into the table? Something like interval partitioning in oracle,in which it automatically creates new partitions when any data inserts into the table for that future date. and we don’t have to create the partitions manually then. 
  
3)Is there exists local and global indexes in mysql like other databases? In case of local index, separate indexes pointing each of the partitions. But in global cases a single index structure is pointing to all the partition table data.

4)What is the Maximum number of range partitions allowed per table in aurora mysql?

Vitor Castellani · Accepted Answer

Hi!

1) In MySQL, just like Oracle, if you're defining a primary key or unique key on a partitioned table, the partition key column must be part of every unique key. This means, **if you're partitioning by a date column, that column needs to be part of any primary or unique key.**

2) Unlike Oracle's interval partitioning which automatically creates new partitions for new data, MySQL does not have such a feature natively. In MySQL, you'd have to manage and create partitions manually ahead of time. If an appropriate partition does not exist for data, the insert will indeed fail. Automating this process would typically require a custom script or scheduled task to pre-create partitions for future dates.

3) In MySQL, there's no distinction between local and global indexes as there is in Oracle. **When you create an index on a partitioned table in MySQL, it essentially becomes a 'local' index, meaning each partition has its own distinct set of index data**. There's no equivalent to Oracle's global index in MySQL's partitioning model.

4) For MySQL 5.7, the **default limit is 8192 partitions**. However, having thousands of partitions on a single table may lead to its own management and performance challenges, so always consider the specific needs and access patterns of your application.

*I hope this helps! If this solution works for you, please accept the answer. Otherwise, do leave a comment, and I'll try to assist you.*

jamshaeed · Answer

I have created partitions in my MySQL Aurora database and subsequently dropped old partitions. However, despite removing approximately 75% of the data, the table size remains unchanged at 3TB. Can anyone guide me on how to reduce the table size?"

Partitioning in aurora mysql

Relevant content