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?

  • Can they be out of order? I mean can a time stamp in the first half be after a timestamp in the later half of the events per hour? Also, will there always be more then 3600 events per hour? – R. S. Jun 10 '13 at 17:49
  • Within a particular batch, the order doesn't matter, so long as they are evenly spread out over the desired duration - it's a kind of rate-limiter that has the ability to catch up should it fall behind. Any given batch may contain more or less than 3600 records and the rate may be variable between say 1000 and 2 million/hour. – Synchro Jun 10 '13 at 20:06
  • Do you know the row count and/or is it expensive to calculate? – R. S. Jun 12 '13 at 1:42
  • Yes I have the row count on hand. One optimisation for the naive case would be to group row updates that have the same timestamp - in the 10k/hour scenario that would reduce the query count by about 60%. – Synchro Jun 13 '13 at 7:16
  • Is there a primary key/incrementing id column that increments by one? – R. S. Jun 14 '13 at 17:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.