How to effectively execute this cron job?

Question

I have a table with 200 rows. I'm running a cron job every 10 minutes to perform some kind of insert/update operation on the table. The operation needs to be performed only on 5 rows at a time every time the cron job runs. So in first 10 mins records 1-5 are updated, records 5-10 in the 20th minute and so on.

When the cron job runs for the 20th time, all the records in the table would have been updated exactly once. This is what is to be achieved at least. And the next cron job should repeat the process again.

The problem: is that, every time a cron job runs, the insert/update operation should be performed on N rows (not just 5 rows). So, if N is 100, all records would've been updated by just 2 cron jobs. And the next cron job would repeat the process again.

Here's an example:

This is the table I currently have (200 records). Every time a cron job executes, it needs to pick N records (which I set as a variable in PHP) and update the time_md5 field with the current time's MD5 value.

+---------+-------------------------------------+ 
|      id | time_md5                            | 
+---------+-------------------------------------+ 
|      10 | 971324428e62dd6832a2778582559977    |  
|      72 | 1bd58291594543a8cc239d99843a846c    |  
|       3 | 9300278bc5f114a290f6ed917ee93736    |  
|      40 | 915bf1c5a1f13404add6612ec452e644    |  
|     599 | 799671e31d5350ff405c8016a38c74eb    |  
|      56 | 56302bb119f1d03db3c9093caf98c735    |  
|     798 | 47889aa559636b5512436776afd6ba56    | 
|       8 | 85fdc72d3b51f0b8b356eceac710df14    |     
|      .. | .......                             |     
|      .. | .......                             |     
|      .. | .......                             |     
|      .. | .......                             |     
|     340 | 9217eab5adcc47b365b2e00bbdcc011a    |  <-- 200th record   
+---------+-------------------------------------+ 

So, the first record(id 10) should not be updated more than once, till all 200 records are updated once - the process should start over once all the records are updated once.

I have some idea on how this could be achieved, but I'm sure there are more efficient ways of doing it.

Any suggestions?

Answer 1

You could use a Red/Black system (like for cluster management).

Basically, all your rows start out as black. When you run your cron, it will mark the rows it updated as "Red". Once all the rows are red, you switch, and now start turning all the red rows to be black. You keep this alternation going, and it should allow you to effectively mark rows so that you do not update them twice. (You could store whatever color goal you want in a file or something so that it is shared between crons)

Answer 2

I would just run the PHP script every 10/5 minutes with cron, and then use PHP's time and date functions to perform the rest of the logic. If you cannot time it, you could store a position marking variable in a small file.