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.
