I have two mostly de-normalized tables in Amazon Aurora (MySQL). The first table had mostly dimensions that are used for selecting the proper data. Examples might be station, program name, operator, etc. Each of these rows would be considered the "meta-data" surrounding an electrical test. This first table has only a few million rows. Each row is identified with an executionId.
The second table has the results of the first tables electrical test. Each test may generate 4-40k measurements. This table has fewer columns (measurement value and executionId, measurementId), but is fast approaching 200 million rows with 8 million more rows added each week (at current production volumes).
I am after information on the best partitioning scheme for the larger table especially. Specifically:
- Does the smaller table even need partitioning? The most common filter on this table would be a datetime.
- What's the best way to partition a date, with the most recent data being accessed frequently, data beyond 6 months accessed almost not at all.
- Since the second table is just the ID, should I partition based on the ID?
- What's the best way to partition an ID like this?
- Can MySQL take advantage of 2 partitions? One for filtering the first table and then a second for joining into the second?
Thanks for your help.