Jump to content


Photo

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


  • Please log in to reply
6 replies to this topic

#1 gargoylemusic

gargoylemusic
  • Members
  • PipPip
  • Member
  • 12 posts

Posted 15 July 2006 - 02:59 PM

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?

#2 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 15 July 2006 - 03:46 PM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#3 pixy

pixy
  • Members
  • PipPipPip
  • Advanced Member
  • 295 posts

Posted 15 July 2006 - 07:10 PM

AUTO_INCREMENT is the best, as fenway said, for ids.

This is a .44 Caliber Loveletter straight through my heart.

Tabulas + Threadless + Hire Me!


#4 gargoylemusic

gargoylemusic
  • Members
  • PipPip
  • Member
  • 12 posts

Posted 16 July 2006 - 07:26 AM

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.

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?)

#5 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 16 July 2006 - 10:02 AM

Everyone does use auto-increment... MAX() is meant for other things, and transactions come with a huge overhead.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#6 gargoylemusic

gargoylemusic
  • Members
  • PipPip
  • Member
  • 12 posts

Posted 17 July 2006 - 11:22 AM

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)

#7 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 17 July 2006 - 06:08 PM

That's correct... auto-increment is per table, and is connection & thread safe, as is LAST_INSERT_ID().
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users