5 ms·

> In our MySQL database we use IDs mostly as primary key Clustered index with random data stored as chars as the PK, what a great time! You will surely not reg

> In our MySQL database we use IDs mostly as primary key Clustered index with random data stored as chars as the PK, what a great time! You will surely not regret this decision later.

esafak 3y ago
What do you recommend?
- sgarland 3y ago
  If you can’t model the table with a natural key (or it would be so large as to inhibit performance), then a simple, normal monotonic integer is best. MySQL even lets you use unsigned ints, so if you use a bigint, you can go all the way up to 2^64-1. For those who think this doesn’t work in distributed systems, it absolutely does – PlanetScale uses them internally [0]. If what is likely the largest MySQL (under Vitess) cluster in the world can manage, yours can too. If this is still untenable, then anything k-sortable (like UUIDv7, as the sibling comment mentioned) is a vast improvement over randomness. Don’t cause B+tree page splits, especially in an RDBMS with a clustering index like MySQL. [0]: https://github.com/planetscale/discussion/discussions/366 https://github.com/planetscale/discussion/discussions/366
  - hot_gril 3y ago
    Even if there's a natural key, serial bigint is usually the best. You can use unique indexes beside that without being stuck with them, and joins are faster on integer pkeys.
    - sgarland 3y ago
      For performance, generally yes. Designing a purely relational model is satisfying, but theory often falls apart in prod. It’s still a good idea, IMO, to think about table design starting as though you have a natural key (composite or singular). It helps develop the schema; you can then drop in a serial/identity/autoincrement column, and use the other relationships as FKs.
    - throwaway296273 3y ago
      I agree. It also makes queries simpler. It's easier to handle WHERE id = 123 compared to WHERE site = 'HN' and username = 'hot_grill' The last query is easier to write when you are querying the database manually, but I find the first more easy to handle programatically. It's easier to pass an argument from an URL or a message queue in this case. As systems evolve, you may find that you need a third component to the natural key. If you don't use a simple id, you need to update every query that references the natural key
      - hot_gril 3y ago
        If you're querying manually, you can still do it the second way even if your pkey is the bigserial.
__float 3y ago
This is why UUID v7 is better to start with. The author also compares to Stripe tokens, which is a strange comparison as you can see they also have a time component towards the beginning.
deleted 3y ago
[deleted]

> In our MySQL database we use IDs mostly as primary key Clustered index with random data stored as chars as the PK, what a great time! You will surely not regret this decision later.

esafak 3y ago

What do you recommend?

sgarland 3y ago
If you can’t model the table with a natural key (or it would be so large as to inhibit performance), then a simple, normal monotonic integer is best. MySQL even lets you use unsigned ints, so if you use a bigint, you can go all the way up to 2^64-1. For those who think this doesn’t work in distributed systems, it absolutely does – PlanetScale uses them internally [0]. If what is likely the largest MySQL (under Vitess) cluster in the world can manage, yours can too. If this is still untenable, then anything k-sortable (like UUIDv7, as the sibling comment mentioned) is a vast improvement over randomness. Don’t cause B+tree page splits, especially in an RDBMS with a clustering index like MySQL. [0]: https://github.com/planetscale/discussion/discussions/366 https://github.com/planetscale/discussion/discussions/366
- hot_gril 3y ago
  Even if there's a natural key, serial bigint is usually the best. You can use unique indexes beside that without being stuck with them, and joins are faster on integer pkeys.
  - sgarland 3y ago
    For performance, generally yes. Designing a purely relational model is satisfying, but theory often falls apart in prod. It’s still a good idea, IMO, to think about table design starting as though you have a natural key (composite or singular). It helps develop the schema; you can then drop in a serial/identity/autoincrement column, and use the other relationships as FKs.
  - throwaway296273 3y ago
    I agree. It also makes queries simpler. It's easier to handle WHERE id = 123 compared to WHERE site = 'HN' and username = 'hot_grill' The last query is easier to write when you are querying the database manually, but I find the first more easy to handle programatically. It's easier to pass an argument from an URL or a message queue in this case. As systems evolve, you may find that you need a third component to the natural key. If you don't use a simple id, you need to update every query that references the natural key
    - hot_gril 3y ago
      If you're querying manually, you can still do it the second way even if your pkey is the bigserial.

__float 3y ago

This is why UUID v7 is better to start with. The author also compares to Stripe tokens, which is a strange comparison as you can see they also have a time component towards the beginning.

deleted 3y ago

[deleted]