Multiple column partitioning

I have a very large table that needs to be partitioned by two columns. One column is an ID and partitioning is simple enough. The second partitioning should be done daily on the date column. I need to keep data of up to 30 days in this table and partitions older than 30 days need to be dropped. What is the best way to do the partitioning for this scenario.

Thanks

The use case of "purge after 30 days" is an excellent use of PARTITIONing. (It is one of only 4 use cases that I know of.)

PRIMARY KEY(id, date)
...
PARTITION BY RANGE (TO_DAYS(date))
(...)

Then every day do

ALTER TABLE .. DROP PARTITION ...
ALTER TABLE .. REORGANIZE PARTITION future INTO
           PARTITION ...,  -- preparing for tomorrow
           PARTITION future ...;  -- this partition should stay empty.

Partition Details -- this is a follow-on to @jkavalik's "Mandatory reading", as @mootmoot provided.

Note: No subpartition. No partition by id. No partition by account_id. The big advantage is making the big nightly DELETE run instantly via DROP PARTITION.

You probably need some kind of index on account_id. Consider a composite index.

If you need to discuss this further, please provide SHOW CREATE TABLE (with or without partitioning) and the main queries.

StackExchange Database Administrators Q#143329, answer score: 4