The choice between an auto-increment integer and a UUID for a primary key looks like a matter of preference. It is not. Each solves problems the other cannot, and the wrong choice creates issues that only surface once the table is large enough to be inconvenient to change.
What UUIDs genuinely solve
Generating IDs before you reach the database. With auto-increment you must insert a row to learn its identifier. With a UUID the client can generate one, use it immediately, and persist it later. This is what makes offline-first and optimistic-UI applications workable — the record has an identity from the moment the user creates it, before any network round trip.
Merging data from separate systems. Two databases with auto-increment keys both contain a row with ID 1. Combining them means renumbering one side and rewriting every foreign key that pointed at it. With UUIDs, the two sets simply do not collide.
Not leaking business information. An auto-increment key in a public URL tells the world your row count and your growth rate. If /orders/1852 is today's order and /orders/1203 was last month's, anyone can calculate your monthly volume. Competitors do this. It is called an enumeration attack when it is used to walk through your records, and a business intelligence gift when it is not.
Idempotency. A client-generated UUID attached to a payment request lets the server recognise a retry as a duplicate rather than charging twice.
The problem nobody warns you about
Random UUIDs make poor clustered primary keys, and MySQL's InnoDB storage engine always clusters on the primary key. That combination causes real trouble.
InnoDB stores rows physically ordered by primary key. With an auto-increment key, every insert appends to the end of the index — the last page stays in memory, it fills, a new one is allocated, and the pattern repeats. It is close to optimal.
With a random UUID, each insert lands at an arbitrary position. The relevant page probably is not in memory, so it must be read from disk. It is probably already full, so it splits into two half-empty pages. Do this a few million times and the index is fragmented, roughly half-empty, and much larger than it needs to be — so less of it fits in memory, so more reads hit disk, and the whole thing compounds.
Storage matters too. Stored as a 36-character string, a UUID takes 36 bytes against 4 or 8 for an integer. That cost is paid in the clustered index and again in every secondary index, because InnoDB appends the primary key to each one.
Four ways out
Store it as binary. A UUID is 128 bits, so BINARY(16) holds it in 16 bytes rather than 36. MySQL 8 provides UUID_TO_BIN() and BIN_TO_UUID() for the conversion. This solves the size problem and nothing else.
Use a hybrid. Keep an auto-increment integer as the primary key for internal joins, and add the UUID as a separate indexed column for external use. You get sequential insert performance internally and non-enumerable identifiers in your URLs. This is the pragmatic answer for most applications, at the cost of two identifiers per row.
Use a time-ordered identifier. UUID version 7 puts a millisecond timestamp in the leading bits, so values increase over time and inserts append rather than scatter. ULID does the same thing with a different encoding. Both keep the uniqueness properties of a random UUID while behaving like a sequential key in the index. If you are choosing today for a new system, this is usually the right answer.
Use PostgreSQL instead. Worth knowing, if only for accuracy: Postgres does not cluster on the primary key by default, so random UUID keys cause far less trouble there. The severity of this problem is a MySQL characteristic, not a universal one.
Which UUID version?
Version 4 is purely random. The right default when you need a unique value and nothing else.
Version 1 encodes a timestamp and the machine's MAC address. It sorts well but leaks information about where and when it was generated, which is why it fell out of favour.
Versions 3 and 5 are hashes of a name in a namespace. The same input always produces the same output, which is occasionally exactly what you want for deterministic identifiers.
Version 7 is time-ordered and increasingly the recommended choice for database keys.
On collisions
The worry comes up constantly and does not deserve much attention. A version 4 UUID has 122 random bits. You would need to generate roughly 2.7 quintillion of them before reaching a 50% probability of a single collision anywhere. The chance of undetected disk corruption silently changing a row is far higher.
What does deserve attention is the source of randomness. A UUID generated with a weak pseudo-random function is predictable, and a predictable identifier in a password reset link or a shareable URL is a security hole. Use a cryptographic random source — our UUID Generator uses the browser's, and every major language provides one.
A decision guide
- Small table, internal use only, no merging — auto-increment is fine and simpler
- Identifiers appear in public URLs — do not expose auto-increment values
- Clients need to create records offline — you need client-generated IDs
- New system on MySQL, large tables expected — UUID v7 or ULID
- Existing system, cannot change the key type — store as
BINARY(16)and add the hybrid column
