Mysql Question posted in
The official MySQL documentation can be found here.

Mysql primary key with uuid string or integer

We are building an app from scratch and one developer is using uuid type string for primary key and I used integer with auto increment. I’m wondering what is better for performance and everything else when we use mysql db. Maybe in no sql type of db it doesn’t matter but I am wondering about sql? What are the best practice?

I think it is better way to use integers for primary key because of faster indexing and queries.

Tags:

2

Answers


  1. 0

    In fact, it may differ depending on the scenario set up.

    Since Uuid is unpredictable, it ensures that each data has a separate id, both in public URLs and in the tables in the database.

    For example, it is difficult for a different user to guess the profile detail information of a profile like ‘users/70e82bcf-1de1-422a-81d3-be65a62bdefb’, but ‘users/1’ is easier. This was just a simple example, when I need global uniqueness in distributed systems I definitely use uuid.

    But if I have limited memory usage, performance is very important and I need indexing, I definitely use integer.

    Login or Signup to reply.
  2. 0

    I don’t know what "everything else" is for you, but in point of performance, I’d go with an autoincremented numerical id.

    It uses less space and is faster with comparisons, especially if there is no dedicated uuid type (which will store the uuid as a number) but only raw string.

    Furthermore with autoincrementing ids, it’s the DBMS’s responsibility to generate unique keys. Eventhough the probability of generating two identical uuids is practically 0 (if done correctly) a bad RNG may mess things up.

    And finally, with autoincrementing rowids you have some sort of a stable natural order (not sure if you need that). If you sort your data by rowid, you can be sure, that newly inserted rows won’t show up in the middle previsouly existing data. Sorting by random uuids won’t ensure that.

    One major drawback of autoincremented ids is of course a certain amout of predictability. If they are only used internally within your program, that may not be a problem. But if they are made publicly available, one may just use any number as id and be able to access data, they shouldn’t be allowed to access.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search