[IDEA] Add ability to create partitioned tables in MySQL

[psapozh]

Is your feature request related to a problem? Please describe. We are facing an issue where MySQL host is continuously growing. We have Data Pruner running but its not helping. From discussing with MySQL folks there have been some suggestion to enable partitioning to help address this. There are conflicting reports regarding running OPTIMIZE on tables, some say we should do it and some say we should not. Since MC is the thing that's creating the tables seems that is the best place to address enabling partitioning as described in the link. We could alter table but that is not sustainable especially since tables are created dynamically by MC. partitioning should be an optional flag that can be set by the user if they want tables to be created with that.

Describe your use case We need partitioning enabled in MySQL mirth schema to better address the ever growing storage size issue.

Describe the solution you'd like Add optional flag that will be used to create tables with partitioning enabled or not enabled based on the flag.

Describe alternatives you've considered We are considering running OPTIMIZE but as I stated before it is sometimes not recommended. Apart from this we dont have anything else we can try.

Additional context https://dev.mysql.com/doc/refman/5.7/en/partitioning.html https://github.com/nextgenhealthcare/connect/discussions/4729

[tonygermano]

I'm pretty certain that partition won't help you out at all with the size of your data. What do you propose partitioning on?

The main reason that people recommend against running OPTIMIZE is that it shouldn't be necessary unless you have no intent to reuse the space that was released.

I don't think #4729 that you linked is related. The issue there was poor channel design, and it was running on postgres, not mysql.