The essence of the problem: There is a database, it has a table with task statuses. It is necessary for 70 days to change the status.

I made two fields on the table: CREATE_DATE and UPDATE_DATE . When creating a task, the same date is set in both fields. As soon as the value of the UPDATE_DATE field becomes 70 days from the moment of creation, I update the date and transfer it to another state.

As implemented now: the script running in crown pulls out all the tasks of the desired state and checks the value of the UPDATE_DATE field for each task.

Problem: The above implementation is not correct, as if the tasks will be a couple of million a server will just hang.

Question: I want to run the cron script every 10 minutes, which will pull out 10 records. And each subsequent launch of the script did not go over the entire database, but continued to work from the position at which it stopped. Accordingly, when he ran through the database, start again from the beginning.

Criticism is accepted if this approach is not possible in principle or not true.

  • CRON floppy disk. MySQL has its own built-in scheduler, aka Event Sheduler. Using the Event Scheduler , CREATE EVENT Syntax . - Akina
  • one
    But in general - why change something? SELECT ... , CASE WHEN status_date < current_date - INTERVAL 70 DAY THEN 'altered status' ELSE status END AS status, ... , that's all. - Akina
  • @Akina Yes, but I also need to notify about the change by mail. And write logs. - doox911
  • one
    From the procedure launched by EVENT, you can even write logs to the logs table, at least ... And let the mail be sent to the CRON mail, pulling information for sending from the logs table, say, once a day. However, the procedure in EVENT can easily unload the data needed for sending to the file (SELECT .. INTO OUTFILE), so the script or program from the crown will not need to pull MySQL. - Akina
  • @Akina so if you need to work with the base + also work with mail, etc., isn't it easier to use CRON? It seems to me that if you only need to work with the base, then Event is better. Maybe wrong of course ...... - Alexander Semikashev

0