In my application, I am estimating nearly 20 million records being stored EVERY day to a BIG_table.
At the end of each day, I am planning on Aggregating the results from the BIG_table and saving it in SUMMARY_table.
Once aggregation is complete, I would delete all the 20 million records for the previous day.
To improve performance of "delete", I am considering PARTITIONing the table on DATE RANGE.
Question:
Based on my design, the maximum number of PARTITIONs I am envisioning is only "2". In Rick James blog on Partition:
http://mysql.rjweb.org/doc.php/partitionmaint#variants, he has the following statement:
"Under, say, 5 partitions -- you get very little of the benefits. "
In my case, I think deleting ~20 million records every day will be quicker with PARTITIONs, but after reading Rick's statement, I am a little concerned.
Could someone help.
thanks
Jawahar