I need to add a sequentially-increasing timestamp to a collection of rows in a fine-grained event scheduling system in MySQL 5.5.
For example, if I have 100,000 rows that I want to assign a scheduled time at a given hourly rate, beginning at
2013-06-10 10:00:00 and running at 10,000 per hour, so these 100,000 records would receive timestamps spread across a 10-hour period. I'm happy to be limited to 1-second resolution, so regular timestamps are fine, and it's ok for multiple rows to get the same value. It needs to be done in an update as the records will already exist.
It's trivial to do this naïvely by setting timestamps on individual records, but in this case it would mean 100,000 queries, and I'm sure we can do better than that!
I've seen similar things done for simple numbers using a table of integers, and it's possible I could do something like that; I'd need up to a couple of million events at any given time, and numbers could be turned into timestamps by multiplying fractional seconds (e.g. 10,000 per hour is 0.36 sec per record) and adding an offset for the start time as a unix timestamp.
At the moment I'm drawing a blank on the best way to build a query to do all this. Any ideas?