All geek questions in one place

Mysql primary key using auto increment id or sha1 hash?

I can either have the auto increment id field as my primary key, or the sha1 hash file.

Which one to choose?

What would be better in terms of performance?

+4
source share
3 answers

Almost certainly the value of auto incrementing integer. He will be faster to create, faster to search and less. Consider, for example, if you had another table that it referenced. Do you want him to refer to it using the integral primary key or using the sha1 hash? An integer will be more significant (in a sense), and it will be much (much!) More efficient.

+1
source

There are several applications in which you want to use a globally unique identifier (UUID / GUID):

  • You expect (or use) a scaling strategy to scale records. You do not want the fragments nodes to duplicate keys .
  • You want to safely port data from one node to another save keys . This is important if you want to maintain external relationships within the tact.
  • Your application is also used offline ( internal sales , home repair , etc.), where the offline application periodically synchronizes with the "source of truth." You want these offline keys to be unique without making a remote call. Otherwise, you will have to develop a strategy for reorganizing keys and relationships along the way. Using a strategy of automatic growth and depending on the DBMS used, this is most likely a non-trivial task.

If you do not have a precedent from above or something similar, you can use the auto-increment identifier, if it is convenient for you; however you can still consider the UUID / GUID

Trade:

There are many opinions on the speed / size of UUID / GUID keys. After all, this is a compromise, and there are many ways to gain or lose speed using a database. Ideally, you want your indexes stored in RAM to be as fast as possible; however, this is a compromise that you must weigh against other considerations.

Other considerations regarding UUID / GUID:

  • Many RDBMSs can create UUIDs.
  • You can also create UUIDs through your application (you are not tied to an RDBMS for generation).
  • Developers / testers can easily transfer data from environment to environment and work on a project. This is a often ignored precedent; however, this is one of the strongest cases of using the UUID / GUID strategy.
  • There are databases optimized for offline use ( CouchDB ), where the UUID is what you get.
+18
source

Use the auto increment id.

  • The identifier should not be generated only with increase.
  • Hashes are better for storing passwords.
  • You can get duplicate keys using SHA hashes. The chance is small, but real.
  • The identifier is more readable.
  • An identifier is a kind of insert history. You know which record was last inserted (highest ID)
+1
source

Source: https://habr.com/ru/post/1414494/

More articles:


All Articles