Determining data retention period

Question

I have an application that has generating data for the past 15 years. Currently, no retention period is configured so the data size grows day by day. I would like to add a retention period to the data so that we can avoid unnecessary memory & performance overhead.

I was exploring how can I determine the best suitable retention period based on the data usage, but couldn't find anything. Some ideas I had in mind:

*Understand query patterns. *Add application logs for data accessibility.

Wondering if there are any already solved solutions to this problem?

Answer 1

MySQL does not retain any kind of hidden timestamp on each row.

Normally, people include a created_at column (or something similar); this can be used for purging "old" rows.

Caution: A giant DELETE will have a giant impact on the database -- blocking other activity. So, if the dataset is "large", then pursue the suggestions here: http://mysql.rjweb.org/doc.php/deletebig

But is it worth it? How much data do you have? How much disk space do you have? Do you have any queries that scan the entire table? Would an index prevent that slow scan?

Please provide SHOW CREATE TABLE if you wish to discuss further.