Rely on the AUTO_INCREMENT column to sort rows by order inserted?

[shoz]

Can you?

As mentioned in the thread [url=http://www.phpfreaks.com/forums/index.php/topic,109627.0.html]SQL structure question[/url], I don't know of any reason why you can't. As long as you allow MYSQL to generate the id's it should be in order.

The only thought I have as to why you wouldn't want to only have the id to keep track, is in an instance where you need to combine the data with another table and still keep track of the order. In that case having a DATETIME column would allow you to combine and regenerate the order.

You'd order the data by datetime and id for both tables. The id's will make the sorting somewhat arbitrary between tables, but each tables data should be in the same order.

If the auto_increment column cannot be used then the only way as far as I know to order the data would be to manually make your own counter.
[code]
LOCK table
INSERT based on maximum + 1
UNLOCK TABLE
[/code]

For those wondering why not use the DATETIME column or a more precise time value to keep track. The problem is that even when locking the table it may be possible that two rows get the same time value. ie the LOCK, INSERT and UNLOCK happens fast enough for them to still get the same value

[obsidian]

ugh... i [b]hate[/b] locking tables. as long as you're not truncating tables or resetting the id column, i would agree that it's safe most of the time to just rely on your auto_increment column, but for someone that has THAT much issue with needing to keep strict tabs on the order in which things were inserted, i'd recommend them use postgresql and compare against the oid instead.

[fenway]

That's a bad, bad idea... even if it will work, from a technical standpoint.  When do you ever actually want the inserted order?  If it's related to date/time, then use a datetime column.  What if you edit these records, and you need to now sort again?  "Inserted order" is a DB internal that should never be exposed.

[obsidian]

...that's why i put in the disclaimer [i]for someone that has THAT much issue with needing to keep strict tabs on the order in which things were inserted[/i] ;-) ... i agree that a date/time sort should be sufficient

[shoz]

[quote author=fenway link=topic=109722.msg442567#msg442567 date=1159383110]
That's a bad, bad idea... even if it will work, from a technical standpoint.  When do you ever actually want the inserted order?  If it's related to date/time, then use a datetime column.  What if you edit these records, and you need to now sort again?  "Inserted order" is a DB internal that should never be exposed.
[/quote]

An example would be posts in a forum. Using a common occurence in these forums. Two users Post at the same time. User1 posts saying "answer is x". User2 posts saying "answer is "y". User2 realizing they're late edits their post and says "The post above mine is a better solution". If the order doesn't remain the same then the information will be misleading.

The situation isn't critically important in this instance, but if the system should have the data ordered by the order of the event then you should be able to keep track of it. Using a DATETIME value will leave it up to the internal storage of the rows to determine the order of two rows that have the same datetime value. EDIT: As far as I know.

[fenway]

If you care that much, you can ORDER BY datetime, uid.