I Want To Keep Only 5 Days Data And Delete Rest Of The Data If It Is Less Than The Retention Date. Need To Remember Each Day 2000000 Row Generate
DELIMITER $$ CREATE PROCEDURE sp_delete_data() BEGIN DECLARE i INT DEFAULT 0; DECLARE loop_counter INT DEFAULT 0; DECLARE retain_days datetime; DECLARE delete_days datetime; SET loop_counter=(SELECT ROUND(count(*)/100,0) FROM data2 WHERE datetime<(SELECT DATE_ADD(min(datetime),INTERVAL 1 DAY) FROM data2)); SET retain_days=(SELECT DATE_SUB(now(),INTERVAL 5 DAY)); -- 5 days data will keep SET delete_days =(SELECT DATE_ADD(min(datetime),INTERVAL 1 DAY) FROM data2); -- check old data from table data2 WHILE i <= loop_counter DO IF retain_days>delete_days THEN DELETE FROM data2 where datetime<delete_days LIMIT 1000; END IF; SET i = i + 1; END WHILE; END$$ DELIMITER ;
I want to keep only 5 days data and delete rest of the data if it is less than the retention date. Since each day data generate almost 2000000 rows that is why it is difficult to delete whole data by one shot. That is why I want to delete 100000 data in each loop. Here, loop_counter variable used to find how many loop we should use for this day data. retain_days variable define to find retention date delete_days variable define to find deleted date. based on retain_days and delete_days varoable data will retain and delete. Fnally this procedure will call by event every 1 day.
My code loop is not working as expected. Need expert solution. If is there any performance issue to delete data like this please let me know. Thanks In Advance
Just create an event that runs once a day:
CREATE EVENT purge_old_data ON SCHEDULE EVERY '1' DAY STARTS CURRENT_TIMESTAMP() ON COMPLETION PRESERVE COMMENT 'Delete rows older than 5 days' DO BEGIN DELETE FROM data2 WHERE `datetime` < DATE_SUB(CURRENT_TIMESTAMP(), INTERVAL 5 DAY); END;
You should not use a loop to delete smaller chunks. In SQL databases loops actually make performance worse. If you have to delete 100s of millions of rows in the first run it is really not a problem for MariaDB.
- → I can't do a foreign key, constraint error
- → How to implement DbDongle::convertTimestamps as workaround of invalid timestamps with MySql strict
- → MySQL error "Foreign key constraint is incorrectly formed"
- → Eloquent Multitable query
- → "Laravel 5.1" add user and project with userId
- → Database backup with custom code in laravel 5 and get the data upto 10 rows from per table in database
- → Laravel 5.1 QueryException when trying to delete a project
- → Using Array in '->where()' for Laravel Query Building
- → Chaining "Count of Columns" of a Method to Single Query Builder
- → Laravel Eloquent Joining Strange query
- → convert time using mysql laravel 5
- → How to update a column after an expiration date in MySQL?
- → Foreign key constraint fails on existing key