PHP/MySQL delay of processing

[Packetrat]

Apparently two of my MySQL INSERT statements run too fast to ensure that the datetimes are at least one second off of each other. Anyone have any suggestions as to the best way to ensure two 'modified' dates are at least 1 second apart so that I may properly order them using ORDER BY.

[Tekime]

Well, if you really want to force them to be one second apart, you can grab the current unix timestamp and increment by one on each insert; then convert to the proper format using PHP's date() function, then run the query.

If you have concurrent INSERT's this will foul everything up of course. Additionally, if you're inserting a lot of records the timestamp will get increasingly less accurate. 2,000 records will introduce almost a 30 minute gap in your data, while the INSERTs should be well under a minute on a reasonable server. The faster the hardware gets, the larger the recordsets, the wider the gap. 100,000 INSERT's on a dedicated quad-Xeon with 4GB of RAM would introduce a nearly 28 hour gap, under ideal conditions ~1:1680 ratio to the actual timestamps.

Okay, that's probably far from what you're trying to accomplish, but it's fun to speculate.

If you can't just depend on an autonumber field, which will always be sequential even on concurrent INSERT's, you could create an index (not a MySQL table index, just a ticker so to speak), and save each incremented index to a new column in your table. As long as you're only concerned with sorting on each batch, that would be sufficient (if not introducing some possibly unnecessary overhead).

Maybe I'm missing your point though -- you said two queries, so the situation could be different if you're comparing just these pairs.

[Packetrat]

Interesting... I never thought about all that. Anywho, I'm just talking about two INSERTs that run back to back, not more. What it is that I'm trying to do is create a discussion link between my forum and my blog such that first a new thread is created, then the first post (the first INSERT I need to worry about) will quote the blog entry and the second post (second INSERT) will be the user's actual discussion text.

However, now that I'm looking at this from another angle - now this morning - I could simply combine the quote and the discussion into one post and not worry about it.

[Packetrat]

I love rhetorical logic...

[Packetrat]

Using 'ORDER BY created DESC, id ASC' works just great of course! Thx.

[Tekime]

Right on!