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
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.