CODEWITHSUNDEEP
mysqldatabasecron
federicot
federicot

Reputation: 12341

Database periodic cleansing

I'm creating a public messaging service and I was wondering, in order to perform a cleanse of the oldest messages, is it okay to delete the oldest message every time a new one is submitted? Or is this approach inefficient for some reason? If so, could you specify why?

I considered create a Cron Job for this, but I'm not sure if it applies in this case.

Upvotes: 1

Views: 769

Answers (3)

Jon Skeet
Jon Skeet

Reputation: 1502825

That sounds unlikely to be appropriate. The appropriateness of removing an old message is unlikely to be dependent on the new message - for example, if you're meant to keep messages for at least a month, then you don't want to start deleting recent messages if you suddenly get a lot of messages in a short period.

I suggest you work out your "garbage" criteria and then schedule a regular job to batch delete old messages (e.g. once a day).

Upvotes: 1

Johan
Johan

Reputation: 76723

You can schedule an event in MySQL:

DELIMITER $$

CREATE EVENT cleanup_messages ON SCHEDULE EVERY day ENABLE 
DO BEGIN
  DELETE FROM messages WHERE ......;
END $$

DELIMITER ;

http://dev.mysql.com/doc/refman/5.1/en/create-event.html

Upvotes: 3

Nimit Dudani
Nimit Dudani

Reputation: 4860

I suggest Cron Job, I believe there is time stamp with each message so you can remove older post with help of time stamp on cron run.

Upvotes: 1

Related Questions