I’m working with a large transactions table that contains 10 years of historical records plus new entries added every day. The table currently has over 22 million rows.
The table structure includes columns like record_id, description, category (values 1-4), and created_date. I need to split this into 6 partitions with specific logic:
Partitions 0-3: Based on category column (p0 for category=1, p1 for category=2, etc)
Partitions 4-5: Based on created_date comparison with current time (p4 for past dates, p5 for future dates)
I attempted using RANGE partitioning with multiple columns but discovered it uses OR logic instead of the conditional logic I need. Also, it doesn’t allow date functions in the partition definition.
Is there a way to achieve this mixed partitioning strategy in MySQL? What would be the best approach for this scenario?
Honestly, you’re fighting MySQL here - it doesn’t handle this natively. Try a computed column instead: create a partition_key that combines your logic (something like category*10 + date_flag), then partition on that single column. It’s a workaround, but way easier than wrestling with the partitioning engine.
MySQL partitioning struggles with complex conditions like yours. I faced a similar challenge with a billing system and found a workaround. Consider adding a ‘partition_flag’ column and use BEFORE INSERT/UPDATE triggers to set this flag according to your rules. For example, the trigger can assign values 0-3 for categories 1-4, while 4-5 can be used based on whether the created_date is before or after the current date. This way, you can implement RANGE partitioning on the ‘partition_flag’ column, which simplifies your setup significantly. Just ensure you update the existing records before going live with this approach.
You’ve hit MySQL’s partitioning wall. I ran into the same thing and ended up ditching MySQL partitioning entirely. Instead, I split everything into separate tables - transactions_cat1, transactions_cat2, etc., plus transactions_past and transactions_future. Then I created a view that unions them all for queries needing the full dataset. Performance got way better since queries only hit the tables they actually need. Downside? You’ll need to update your app logic to route inserts to the right table. But honestly, it’s worth it - you get full control without wrestling with MySQL’s weird partitioning rules.