> 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.
Clustered index with random data stored as chars as the PK, what a great time! You will surely not regret this decision later.
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.
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
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.
WHERE id = 123
WHERE site = 'HN' and username = 'hot_grill'
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