Events in MySQL are a very powerful tool for executing scheduled planned tasks you may need.
They are not depenant on you application but on RDMS, therefore it will handle it faster than your application.
Events I find them useful for tasks such as: updating aggregated data, archive old data, etc.
First of all you must make sure the evens scheduler is enabled:
SHOW PROCESSLIST;
And there should appear one process with user "event_scheduler"
If not it can be set up like this, either setting event_scheduler=ON in MySQL configuration file or with command:
SET GLOBAL event_scheduler = ON;
A simple example of event could be like this:
CREATE DEFINER=`root`@`127.0.0.1` EVENT `calculate_summaries_data` ON SCHEDULE EVERY 10 MINUTE STARTS '2016-05-19 13:25:00' ON COMPLETION PRESERVE ENABLE DO BEGIN
-- Example SQL, use the one you might need for your requirements
UPDATE summary_stats s,
(SELECT
country AS c,
region AS r,
COUNT(*) AS transactions
FROM
transactions_done t
WHERE
DATE(transaction_date) = CURDATE()
GROUP BY country,region) AS t
SET
s.amount_transactions = t.transactions_done
WHERE
s.country = t.c AND s.region = t.r
AND date = CURDATE();
END
Once created you can check the events which are scheduled by:
show events;
Will display the events which are set up. And to check the definition of a concrete one:
show create event calculate_summaries_data;
Some good references about this:
https://www.sitepoint.com/how-to-create-mysql-events/
https://dev.mysql.com/doc/refman/5.7/en/events-metadata.html
http://www.mysqltutorial.org/mysql-triggers/working-mysql-scheduled-event/
http://stackoverflow.com/questions/11411921/how-do-i-get-multiple-mysql-statements-in-one-scheduled-event
http://stackoverflow.com/questions/3070277/mysql-event-scheduler-on-a-specific-time-everyday
