Which is better? I know that autoincrement fields allows you to get the last inserted id which is helpful when dealing with multiple tables, but other than that I'm not sure which I should use to make the database more secure/robust.

7 Years
Discussion Span
Last Post by griswolf

What is this 'better'? You really need to specify more completely what you need before you can decide what will work better for your particular needs.

- size: Auto increment works for up to about 2*10^19 items. UUID works for up to about 10^38 items.

- unique key: auto_increment guarantees unique (and fails if you run out of integers) UUID is intended to be unique (even across non-communicating machines) but I doubt there is an actual guarantee: You would need to work harder to avoid extremely rare possible conflict.

- recent key access: You can easily get the most recent auto-incremented value (per connection) without doing a select; but you need to do something more difficult to get the most recent UUID

- speed: I have no data, but on the face of it, auto increment should be quicker than UUID()

- externally generated key: Allows easy programmatic access to the key (whatever it is) as well as a 'perfect' key size and type, but requires thoughtful coding for multiple threads.

- limitation: only one auto increment column per table, but since UUID() generates text, there is no limit, similar for externally generated key

- efficiency: An index on an int type may have different behavior than an index on a text type

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.