MySQL: Should You Use UUID or AUTO_INCREMENT? Optimization Strategies with UUID v7

A 5-Minute Real-World Test

To clearly see the difference, try creating three tables in MySQL. We will compare storage between traditional auto-incrementing BIGINT and UUID in two forms: string and binary.

-- Traditional approach: Uses the least memory
CREATE TABLE users_auto (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50)
);

-- The "naive" way: Store UUID as a string (Extremely slow with large datasets)
CREATE TABLE users_uuid_string (
    id CHAR(36) PRIMARY KEY,
    username VARCHAR(50)
);

-- The professional way: Store UUID v7 in binary format
CREATE TABLE users_uuid_v7 (
    id BINARY(16) PRIMARY KEY,
    username VARCHAR(50)
);

In practice, when inserting about 1 million records, the users_auto table maintains a stable speed. Conversely, the users_uuid_string table will start to “lose steam” and slow down. Why is there such a gap? Let’s dive into the details.

AUTO_INCREMENT – The Default Choice and Its Limits

Most new projects start with BIGINT AUTO_INCREMENT. Its advantages are undeniable: it only takes up 8 bytes, query speeds are incredibly fast, and data is naturally sorted sequentially.

But life isn’t always a dream. I once handled a system that needed to merge data from three branch databases into a central warehouse. The result was a chaotic mess of duplicate IDs. I spent two sleepless nights just re-mapping relationships across dozens of tables. That’s when I realized AUTO_INCREMENT isn’t a “silver bullet.”

Risks You Need to Consider:

• Leaking business secrets: A hacker only needs to look at the ID to know how many customers you have. If the ID today is 1000 and tomorrow it’s 1050, your competitor knows immediately that you only had 50 more orders.
• Scaling nightmares: In a Microservices architecture, allocating auto-incrementing IDs without collisions across multiple servers is a complex puzzle.
• IDOR vulnerabilities: An attacker can easily guess the next URL by changing id=123 to id=124 to peek at private data.

Why Does UUID v4 Often Make MySQL “Cry”?

UUID v4 completely solves the duplication issue. The probability of an ID collision between two servers on opposite sides of the world is nearly zero. However, if you use CHAR(36) as a primary key, you are seriously wasting resources.

MySQL uses a B+Tree structure to manage indexes. Data on the hard disk is physically sorted by the Primary Key order. Since UUID v4 is completely random, it causes a phenomenon known as Page Splitting.

Imagine you are organizing books on a shelf alphabetically. If a new book starts with the letter “A,” you are forced to push hundreds of existing books aside to squeeze it in at the beginning. Databases work the same way. Random insertions force the hard drive to constantly read and write to re-sort data pages, leading to:

• Index Fragmentation: Index file sizes can swell to 2-3 times larger than necessary.
• I/O Overload: The hard drive (even an SSD) will become a bottleneck due to excessive data movement.
• Low Cache Hit Ratio: The Buffer Pool gets filled with fragmented data pages, reducing overall system performance.

UUID v7 – The Perfect Combination

UUID v7 is a new standard that allows you to keep the benefits of UUIDs without sacrificing database performance. Its structure uses the first 48 bits for a Timestamp.

This means that UUIDs generated later are always larger than previous ones. When using UUID v7, MySQL processes it as smoothly as AUTO_INCREMENT because data is always appended to the end of the B+Tree. You get global uniqueness while avoiding index fragmentation.

Practical Implementation

MySQL does not yet have a built-in UUID_V7() function. You should generate the ID at the Application layer using libraries like uuid7 in Python or Node.js. Most importantly: Store it as BINARY(16).

# Example of generating UUID v7 in Python
import uuid6 

new_id = uuid6.uuid7()
# Save to DB as bytes to save space
cursor.execute("INSERT INTO users_uuid_v7 (id, username) VALUES (%s, %s)", (new_id.bytes, "tech_editor"))

Battle-Tested Experience: Which One Should You Choose?

I once optimized a logging system that reached the 500-million-record mark. Switching from string-based UUID v4 to BINARY(16) UUID v7 increased insert speeds fourfold. At the same time, the index size dropped from 45GB to less than 15GB.

Advice for You:

1. Small or internal projects: Don’t overcomplicate things; just use BIGINT AUTO_INCREMENT.
2. SaaS projects or Mobile Apps: Choose UUID v7 from the start to avoid painful migrations later.
3. The Golden Rule: Always use BINARY(16) instead of VARCHAR(36). Binary comparison is faster and saves exactly 50% of storage space compared to string storage.

Applying the right data type from the start will help your system stay “healthy” even when data hits tens of millions of rows. If you’re having trouble migrating old data to UUID v7, feel free to leave a question in the comments!