If you are running large public web applications like shopping and emails portals, you have handled lots of unwanted data rows, for example, spam emails and unused shopping cart data. Sure it will create 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 the database.
Database
Sample database cart table contains four columns cart_id, user_id, product_id, and created_at.
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)
) ;Enable MySQL Event Scheduler
Start MySQL event scheduler by executing the following query in PhpMyAdmin or MySQL command prompt.
SET GLOBAL event_scheduler = ON;
Or
SET GLOBAL event_scheduler = 1;Create an Event
Here the following event will run every day and clear/delete 10 days old data from cart table based on a timestamp
CREATE EVENT newEvent
ON SCHEDULE EVERY 1 DAY
DO
DELETE FROM cart WHERE created_at <= DATE_SUB(NOW(), INTERVAL 10 DAY) ;Alter Event
If you want to modify the event run time simple you can execute a query in below syntax.
ALTER newEvent
ON SCHEDULE EVERY 5 HOUR
STARTS TIMESTAMP + 3 HOUR View Event
Show all the running events.
SHOW EVENTS;Delete Event
You can drop the event for executing the below query.
DROP EVENT newEvent;Check event schedule status under the process tab in PhpMyAdmin root (it will show only when a scheduled event is running).
