0

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.

2
Contributors
1
Reply
3
Views
7 Years
Discussion Span
Last Post by griswolf
1

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.