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.