Primary key mysql using auto increment id or a sha1 hash?

Question

I can either have an auto increment id field as my primary key or a sha1 hash.

Which one should I choose?

Which would be better in terms of performance?

Answer 1

There are a few application-driven cases where you'd want to use a globally unique ID (UUID/GUID):

1. You expect to (or are) using a sharding strategy to scale writes. You don't want the shard nodes to duplicate keys.
2. You want to be able to safely port data from one node to another preserving keys. This is critical if you want to keep foreign-key relationships in-tact.
3. Your application is also used offline (in-home sales, in-home repairs, etc.) where the offline application periodically syncs with the "source of truth". You'd want those offline keys to be unique without having to make a remote call. Otherwise, it is up to you to come up with a strategy to reorganize keys and relationships on the way in. With an auto-increment strategy and depending on the RDBMS you are using, this is likely a non-trivial task.

If you don't have a use-case from above or something similar, you may use an auto-increment id if that makes you comfortable; however, you may still want to consider UUID/GUID

The Trade Off:

There are a lot of opinions held about the speed / size of UUID/GUID keys. At the end of the day, it is a trade-off and there are lots of ways to gain or lose speed with a database. Ideally, you want your indexes to be stored in RAM in order to be as fast as possible; however, that is a trade-off you have to weigh against other considerations.

Other Considerations regarding UUID/GUID:

1. Many RDBMS can produce a UUID.
2. You can also produce a UUID via your application (you aren't tied to the RDBMS to generate).
3. Developers / Testers can easily port data from environment to environment and have the application work as expected. This is an often overlooked use-case; however, it is one of the stronger cases for using a UUID/GUID strategy.
4. There are databases that are optimized for use offline (CouchDB) where UUID is what you get.