Automatically delete outdated rows from database every n seconds

Question

I have a database which has a timestamp column and I want outdated data to be deleted.

So my idea is to write a MySQL query to a .php file which deletes every row where timestamp < current_timestamp - const. As there will be a lot of rows where this has to be checked, I am going to set an index to the timestamp column.

So how can I run this script automatically every n seconds? I heard about Linux crontab - can I use this on my webserver (not the db server) to execute the .php file periodically and is this overall a good technique to delete outdated rows from a database?

The database is set on a RDS instance on Amazon Web Services. My webserver is a EC2 instance (also Amazon Web Services).

Answer 1

Doing such a thing requires setting up an event or job. Such efforts keep the database very busy.

I would strongly recommend a different approach. Use a view to access the data you want:

create view v_t as
    select t.*
    from t
    where timestamp > CURRENT_TIMESTAMP - ??;

Then use this view to access the data.

Then, periodically, go in an clean the table to get rid of the rows that you don't don't want. You can do this once a day, once a week, once an hour -- the deletions can occur at times when the database load is lighter, so it doesn't affect users.