AUTO_INCREMENT or UUID primary keys in MySQL?

Summary

AUTO_INCREMENT BIGINT keys are compact and insert in order, which suits InnoDB's clustered index. Random UUIDv4 keys are 16+ bytes, insert randomly (page splits, poor cache locality) and enlarge every secondary index. If you need globally unique or non-guessable IDs, use time-ordered IDs (UUIDv7, ULID) in BINARY(16), or keep an integer PK plus a separate public ID.

Longer Answer

Integer keys leak information (counts, growth) and are guessable, which matters for public URLs; but authorization, not obscure IDs, should protect data. AUTO_INCREMENT values have gaps (rollbacks, INSERT IGNORE, bulk inserts) and are allocated per server, which complicates multi-primary setups. UUID_TO_BIN(uuid, 1) swaps time fields of UUIDv1 to make them more sequential.

Store UUIDs as BINARY(16), not CHAR(36) (which is 36+ bytes and slower to compare).

Key Points

• Increasing keys suit the clustered index.
• Random UUIDs: page splits, larger indexes.
• UUIDv7/ULID in BINARY(16) if needed.
• IDs are not authorization.