Video Introduction & Context

• Third attempt at filming UUID video over 2 years
• Previous attempts (couch filming, random stream) weren't satisfactory due to complexity
• PlanetScale sponsored video but provided no input on content
• Using PlanetScale blog post "The problem with using a UUID primary key in MySQL" as starting point

UUID Version History & Complexity

• Currently 5 official versions (V1-V5) and 3 proposed versions (V6-V8)
• V1 (Time-based):
  • Uses Gregorian calendar start date (October 10, 1568) instead of Unix Epoch (January 1, 1970)
  • Components: Time Low, Time Mid, Time High, Clock Sequence, Node
  • Timestamp grows in 100 nanosecond increments
  • Node identifies system generating UUID
• V2:
  • Replaced low time segment with POSIX user ID for traceability
  • Increased collision chances, rarely used
• V3 & V5:
  • Deterministic generation using namespace (itself a UUID) + name
  • V3 uses MD5 hashing, V5 uses SHA-1 hashing
• V4 (Random):
  • Almost entirely random except version identifier
  • Most commonly used version
• V6:
  • Nearly identical to V1 but flips timestamp bits (most significant first)
  • Attempt to make UUIDs sortable - failed
• V7:
  • Time-based using Unix Epoch instead of Gregorian calendar
  • Replaces node with randomness for less trackability
• V8:
  • Permits vendor-specific implementations
  • Barely a standard, more of a "vibe"

UUID Collision Reality

• Universal uniqueness promise has failed multiple times
• Random number generators aren't always truly random
• Collision risk increases with poor randomness

MySQL Performance Issues with UUIDs

Insert Performance Problems

• MySQL uses B+ tree indexes for primary keys
• Sequential values (auto-incrementing integers) allow predictable tree balancing
• Random UUIDs cause frequent page splitting and rebalancing
• High-volume databases experience significant performance degradation

Storage Utilization Issues

• Size comparison:
  • Auto-incrementing integer: 32 bits
  • UUID binary format: 128 bits (4x larger)
  • UUID string format: 288 bits (20x larger than integer)
• Secondary indexes also consume more space (use primary key as pointer)
• Page splitting with random keys reduces page utilization from 94% to as low as 50%

Best Practices for UUID Usage

Binary Storage

• Store UUIDs in BINARY(16) format instead of CHAR(36)
• Reduces storage from 36 characters to 16 bytes
• Still larger than 32-bit integer but significantly better

Ordered UUID Variants

• Use time-based versions (V6, V7) for better sequential ordering
• Avoid V1 due to least significant timestamp portion being first
• Sequential ordering mitigates page splitting issues

MySQL Built-in Functions

• UUID_TO_BIN() function with swap flag
• Reorders timestamp portion for better sequential behavior

Security Considerations

• Sortable/incremental IDs create security vulnerabilities:
  • Unauthorized password resets via guessable IDs
  • Unauthorized access through ID enumeration
• Counter-argument: If system can be compromised through ID knowledge alone, fix the authorization system, not the ID system

Alternative ID Systems

• Snowflake IDs: Twitter's distributed ID system
• ULIDs: Universally Unique Lexicographically Sortable Identifiers
• Nano IDs: Smaller, customizable character sets
• CUID V2: Collision-resistant unique identifiers (presenter's previous preference)

CUID V2 Concerns

• Originally recommended for sortability and security
• CUID V2 maintainers removed sortability feature citing security concerns
• Presenter's stance changed during video after learning about performance implications

Performance vs Security Trade-offs

• Sequential IDs: Better database performance, potential security risks
• Random IDs: Better security, worse database performance
• Need to balance based on specific use case requirements