Schedule queries with MySQL

While researching the most performant way to delete a high volume of rows in MySQL, I happened upon some syntax that I had never heard of before. This syntax allows you to run a query on a schedule, without the need of any external code.

As a rule of thumb, I do avoid doing anything magical inside of the database. In my experience, it tends to get forgotten about and generally speaking, outside of database administrators, most developers aren’t too versed in these kinds of things.

That all being said, it’s always fun to learn about these things, as they may come in handy in the future.

So the state in question is CREATE EVENT which also has a DROP EVENT and SHOW EVENTS statements to help you manage things.

The gist of the statement is:

CREATE EVENT your_event
  ON SCHEDULE
    -- your schedule
  COMMENT 'Optional comment, but IMHO it should be required'
  DO
    -- your query to run

The schedule can take a wide range of values, from a specific time and date, to a fixed interval:

-- One time at a specific time
ON SCHEDULE AT '2023-03-06 00:00:00'

-- Recurring every hour
ON SCHEDULE EVERY 1 HOUR

-- Recurring once a day at a specific time
ON SCHEDULE EVERY 1 DAY STARTS '2023-03-06 00:00:00'

For my use case, of cleaning up old data on a table, I was thinking of running a DELETE every 4 hours, which looks like this:

CREATE EVENT delete_old_rows_on_some_table
  ON SCHEDULE EVERY 4 HOUR
  COMMENT 'Delete rows older than 14 days on some_table'
  DO
    DELETE FROM some_table WHERE created_at < NOW() - INTERVAL 14 DAY;

It would have gotten the job done, but as mentioned, I try to avoid using this kind of stuff in the database. Also, based on the volume of data we’re attempting to clean up, there are more performant approaches, like batching the deletions instead of trying to do them all at once.