If you are running a large public web applications like shopping and emails portals, you have handle lots of unwanted data rows for example spam emails and unused shopping cart data. Sure it will created problem in database overload. So that I want to explain a simple tip called how to use MySQL event scheduler for deleting unwanted data rows from database.

Arun Kumar Sekar



Engineer, Plugin Expert

Chennai, INDIA

CREATE TABLE cart

(

cart_id INT AUTO_INCREMENT ,

user_id INT ,

product_id INT ,

created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

PRIMARY KEY (cart_id)

) ;

SET GLOBAL event_scheduler = ON ;

Or

SET GLOBAL event_scheduler = 1 ;

CREATE EVENT newEvent

ON SCHEDULE EVERY 1 DAY

DO

DELETE FROM cart WHERE created_at <= DATE_SUB(NOW(), INTERVAL 10 DAY) ;

ALTER newEvent

ON SCHEDULE EVERY 5 HOUR

STARTS TIMESTAMP + 3 HOUR

SHOW EVENTS;

DROP EVENT newEvent ;

Sample databasetable contains four columns cart_id, user_id, product_id and created_at.Start MySQL event scheduler by executing following query in PhpMyAdmin or MySQL command prompt.Here the following event will run everyday and clear/delete 10 days old data fromtable based on time stampIf you want to modify the event run time simple you can execute a query in below syntax.Show all the running events.You can drop the event for executing below query.Check event schedule status under process tab inroot (it will show only when scheduled event is running).