Jump to content

Archived

This topic is now archived and is closed to further replies.

gargoylemusic

New user IDs - use MAX() or auto increment?

Recommended Posts

My question is fairly simple. There seem to be two ways of incrementing the 'ID' field of a new record. One is to find the maximum ID, add one, and use that new value as the ID. Or one can use an auto-incrementing field. Why would someone use one method versus another? When is auto-incrementing not appropriate?

Share this post


Link to post
Share on other sites
UID fields should always be auto-increment.. MAX() is unreliable, because you don't have consistent reads in MySQL with MyISAM table without transactions, so you can't be sure that you're adding one to the "last" record.  Auto-incrementing is always appropriate for uids; sometime count=count+1 makes sense for other fields, but the same warning applies.

Share this post


Link to post
Share on other sites
AUTO_INCREMENT is the best, as fenway said, for ids.

Share this post


Link to post
Share on other sites
[quote author=fenway link=topic=100678.msg397727#msg397727 date=1152978386]
UID fields should always be auto-increment.. MAX() is unreliable, because you don't have consistent reads in MySQL with MyISAM table without transactions, so you can't be sure that you're adding one to the "last" record.  Auto-incrementing is always appropriate for uids; sometime count=count+1 makes sense for other fields, but the same warning applies.
[/quote]
Then should I use an InnoDB table (and then use transactions?) I mean... is there any advantage to that (why does't everybody use auto_increment?)

Share this post


Link to post
Share on other sites
Everyone does use auto-increment... MAX() is meant for other things, and transactions come with a huge overhead.

Share this post


Link to post
Share on other sites
When I do that, the only thing unique about the record would be the auto-incremented ID; does that mean I don't have to wrap it in a transaction? (If two people create accounts at the same time... MySQL won't assign the same auto-id?)

(pardon my ignorance... if you have any decent tutorials on this kind of thing, please point me to the url)

Share this post


Link to post
Share on other sites
That's correct... auto-increment is per table, and is connection & thread safe, as is LAST_INSERT_ID().

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.