How do I clear old entries from a MySQL database table?

Question

I have a MySQL database with one big table in it. After a while, it becomes too full and performance degrades. Every Sunday, I want to delete rows whose last update is older than a certain number of days ago.

How do I do that?

to schedule it to run every sunday look into this post look at answer of rkosegi http://stackoverflow.com/questions/9621355/how-to-schedule-a-mysql-query — Sean Ch, Jan 10 '17 at 21:54
I tend to favor keeping all the rows. You might want to consider a partitioning scheme. — Gordon Linoff, Jan 10 '17 at 22:16

score 0 · Answer 1 · edited Jun 20 '20 at 09:12

Self Answer

Make a web server that sends the following SQL to the database every weekend:

DELETE FROM table WHERE timestamp < DATE_SUB(NOW(), INTERVAL 7 DAY);

or

DELETE FROM table

WHERE timestamp < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 7 DAY))

I might need locking to prevent accumulation of jobs, like so:

DELIMITER //
CREATE EVENT testlock_event ON SCHEDULE EVERY 2 SECOND DO
BEGIN
 DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
 BEGIN
   DO RELEASE_LOCK('testlock_event');
 END;
 IF GET_LOCK('testlock_event', 0) THEN
   -- add some business logic here, for example:
   -- insert into test.testlock_event values(NULL, NOW());
  END IF;
  DO RELEASE_LOCK('testlock_event');
END;
//
DELIMITER ;

Final answer:

CREATE EVENT `purge_table` ON SCHEDULE
        EVERY 1 DAY
    ON COMPLETION NOT PRESERVE
    ENABLE
    COMMENT ''
    DO BEGIN
IF GET_LOCK('purge_table', 0) THEN
DELETE FROM table WHERE timestamp < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 7 DAY));
END;

score 0 · Answer 2 · answered Jan 10 '17 at 21:54

0

What is the table design? Do you have a column with a timestamp?

Assuming you do, you could use that timestamp value with a datediff(your_date,CURDATE()) in a delete command.

Delete from table where datediff(date_col, CURDATE ()) > your_num_days.

answered Jan 10 '17 at 21:54

newGuy

354
1
2
11

Timothy Wood · Accepted Answer · 2017-01-11T00:09:30.583

0

Make a Scheduled Event to run your query every night. Check out Event Scheduler as well.

CREATE EVENT `purge_table` ON SCHEDULE
        EVERY 1 DAY
    ON COMPLETION NOT PRESERVE
    ENABLE
    COMMENT ''
    DO BEGIN
DELETE FROM my_table WHERE my_timestamp_field <= now() - INTERVAL 5 DAY
END

edited Jan 11 '17 at 00:09

answered Jan 10 '17 at 21:55

Timothy Wood

487
3
10

score -1 · Answer 4 · edited May 23 '17 at 10:29

Maybe you can provide more information on how you are pushing the data to the DB and how you are working on the DB in general? Therefore we can help you and don't have to struggle with the dark...

I'll provide an easy solution: It's kind of workaround, but works:

Everytime you touch the data you update a time stamp in the updated rows.

Therefore you could easily filter them out every sunday.

UPDATE

The answer, the author provided by himself, was discussed at Stackoverflow and seems not to work in exactly that way, compare the discussion.

How do I clear old entries from a MySQL database table?

4 Answers4

UPDATE