MySQL partitioning table by date and hour

Question

I have a table with a timestamp field. This table will have millions of rows every day so the idea is to create partitions by date and hour. Every day I will delete partitions older than 10 days and create the partitions for next day. Can you someone tell me how to define partitions for this case? I could not figure it out. Thanks in advance.

Answer 1

Use 12 partitions -- 10 days, plus one at each end. REORGANIZE PARTITION to split the empty, future, partition at the end before the new day; drop the oldest. All the details, including code, are spelled out in http://mysql.rjweb.org/doc.php/partitionmaint .

Hourly partitions would lead to over 240 partitions; that large a number slows things down.