Multiple column partitioning

Question

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

• jkavalik: Why do you need partitioning by the first column (ID)? If you think "for performance reasons" then just skip it. Mandatory reading - mysql.rjweb.org/doc.php/ricksrots#partitioning
• Marco: You mean that you want a first partition on an ID column and a second partitioning on the data column? Only one is possible.
• mootmoot: Partition is NOT index. Depends on requirement, sometime it is counter intuitive to remove the partition once you create it. Partition is NOT a subset index of your data, it is the data. Please read this link to get better idea. stackoverflow.com/questions/37959930/…
• mootmoot: please checkout the paritition usage directly here : mysql.rjweb.org/doc.php/partitionmaint
• jkavalik: IIRC there may be partitions and subpartitions, but sure not two separate partitioning schemes. WHY do you require partitioning on types? The date part makes sense for quick drops of old data. But if you are concerned about performance of queries by type, use proper indexes instead.

Answer 1

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.