Hi guys, my last article I shown you “How to generate database oriented Form using Cygnite simple command”. Today I would like to write about MySql Event Scheduler. MySql Event Scheduler very useful concept if you are dealing with large scale application like email system, online shopping, or may be activity history management application. Where every day thousands of data getting stored into the database which create a heavy load into your database and slow down your application. You may no longer required existing activity history or want to archive data, this is where Mysql Event scheduler come to play. Event useful to create backup, delete unnecessary rows from database so on. Let me show you how to start with Mysql scheduler.
Creating a Table:
Let us create a sample table activity_history to store each and every activity of user in the application. You may compare with similar stuffs at facebook where each and every user activity displays at right top corner.
CREATE TABLE activity_history ( id INT(11) AUTO_INCREMENT, user_id INT(11),, description INT(11), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id) );
Enabling Event Scheduler:
Before creating or scheduling event you need enable event by executing below command.
SET GLOBAL event_scheduler = ON; or SET GLOBAL event_scheduler = 1;
Similarly you can also turn off the event as below.
SET GLOBAL event_scheduler = OFF;
After enabling you may confirm your event by
CREATE EVENT delete_activity ON SCHEDULE EVERY 1 DAY DO DELETE FROM activity_history WHERE created_at <= DATE_SUB(NOW(), INTERVAL 15 DAY) ;
Above event will run every day and delete 15 days old activity history from the table.
Suppose you want to alter the old event you can do as below.
ALTER EVENT delete_activity ON SCHEDULE EVERY 1 MONTH STARTS '2014-07-01 01:00:00'
If you would like to rename Event
ALTER EVENT delete_activity RENAME TO delete_activity_event;
Displaying Mysql Events:
Below command will display all the events available into your database.
You may delete unnecessary event by executing.
DROP EVENT delete_activity_event;
Hope this article useful. If so please don’t forget to give a small like or share with friends, also leave your comments below.
Have a nice day. Keep visiting for latest posts.