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.
MySQL partitioning table by date and hour
MySQLpartitioning
Related Question
- SQL Error: ORA-14300: While partitioning and subpartitioning
- Mysql – Multiple column partitioning
- MySQL – Unix timestamp from columns with hour, day, month
- Mysql – Create Range Partition on existing large MySQL table
- Mysql query – Event to delete rows by its value
- Sql-server – Delete rows older than x days without locking table
Best Answer
Use 12 partitions -- 10 days, plus one at each end.
REORGANIZE PARTITIONto 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.