UUID() vs AUTO_INCREMENT in MySQL: A Deep Technical Analysis of Performance Mechanics and Benchmarks

Choosing a primary key strategy in MySQL is one of the highest-leverage decisions in schema design. The two most common contenders—AUTO_INCREMENT integers and UUIDs—differ fundamentally in how they interact with InnoDB’s storage engine. This post examines the internal mechanics, quantifies the performance differences through published benchmarks, provides production-ready code patterns, and evaluates practical alternatives.

InnoDB Clustered Index Mechanics

InnoDB stores table data in a B+ tree ordered by the primary key (the clustered index). Leaf pages contain the full row data. Secondary indexes store the primary key value as a pointer back to the clustered index.

Sequential Keys (AUTO_INCREMENT)

When the primary key is monotonically increasing:

• New rows are almost always appended to the rightmost leaf page.
• Page splits are rare and occur only when the rightmost page fills.
• InnoDB’s page fill factor targets ~94 % for sequential inserts.
• Buffer pool locality is excellent: the hot page stays in memory.
• Secondary indexes remain compact because the primary key is small (4 or 8 bytes).

The relevant MySQL internals are controlled by innodb_autoinc_lock_mode (0 = traditional, 1 = consecutive, 2 = interleaved). Mode 2 is preferred for concurrent bulk inserts.

Random Keys (UUIDv4)

A pure random 128-bit value produces the opposite behavior:

• Each insert lands at a random position in the B+ tree.
• The engine must locate the correct leaf page (often requiring a disk read once the working set exceeds the buffer pool).
• Frequent page splits occur; after a split both pages are typically ~50 % full.
• Average fill factor drops to the 50–70 % range, inflating index size.
• Buffer pool thrashing increases dramatically at scale because there is no “hot” page.
• Every secondary index inherits the 16-byte (or larger) primary key, multiplying storage and cache pressure.

MySQL’s built-in UUID() function returns a UUIDv1 string (time-based). Without reordering, the most significant bits are not the timestamp, so the values are only partially ordered. Random UUIDv4 values are worse.

Ordered UUID Variants

UUIDv7 (RFC 9562) places a 48-bit Unix timestamp in the most significant bits followed by random bits. This restores near-sequential insertion order while preserving global uniqueness and client-side generation. MySQL 8.0+ can store these efficiently as BINARY(16).

Storage Footprint Comparison

Because every secondary index stores a copy of the primary key, a table with five secondary indexes pays the size difference five extra times.

Performance Benchmarks

The numbers below synthesize results from multiple independent studies (obviy.us 2022, Programster 2018, Chinese-language million-to-ten-million row tests, PlanetScale analyses, and recent UUIDv7 measurements). Absolute throughput varies with hardware, but relative ordering is consistent.

Insert Throughput (rows/sec, approximate)

One long-running test inserting tens of millions of rows showed INT AUTO_INCREMENT sustaining ~6× higher p99 insert rates than CHAR(36) UUIDv4 once the table reached ~64 M rows. UUIDv1 (with proper bit swapping) and UUIDv7 close most of that gap.

Query Latency

• Point lookups by primary key: AUTO_INCREMENT remains fastest; BINARY(16) UUIDv7 is typically within 10–20 %.
• Range scans / ordered retrieval: sequential keys win decisively because of sequential I/O and better cache locality.
• Secondary index lookups: difference is smaller because the secondary index itself is not ordered by the primary key, but the larger key size still hurts cache efficiency.

Storage & Fragmentation

After inserting ~10 M rows:

Code Examples

Schema Definitions

-- Classic sequential primary key
CREATE TABLE orders_ai (
    id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    customer_id   BIGINT UNSIGNED NOT NULL,
    amount        DECIMAL(12,2) NOT NULL,
    created_at    TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    PRIMARY KEY (id),
    KEY idx_customer (customer_id)
) ENGINE=InnoDB;

-- Compact UUID primary key (UUIDv7 preferred)
CREATE TABLE orders_uuid (
    id            BINARY(16) NOT NULL,
    customer_id   BIGINT UNSIGNED NOT NULL,
    amount        DECIMAL(12,2) NOT NULL,
    created_at    TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    PRIMARY KEY (id),
    KEY idx_customer (customer_id)
) ENGINE=InnoDB;

-- Hybrid: sequential clustering key + public UUID
CREATE TABLE orders_hybrid (
    id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    public_id     BINARY(16) NOT NULL,
    customer_id   BIGINT UNSIGNED NOT NULL,
    amount        DECIMAL(12,2) NOT NULL,
    created_at    TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    PRIMARY KEY (id),
    UNIQUE KEY uk_public_id (public_id),
    KEY idx_customer (customer_id)
) ENGINE=InnoDB;

Generating and Storing Ordered UUIDs (MySQL 8.0+)

-- Convert a UUIDv1 (from UUID()) into a time-ordered BINARY(16)
INSERT INTO orders_uuid (id, customer_id, amount)
VALUES (UUID_TO_BIN(UUID(), 1), 42, 99.99);

-- Retrieve in human-readable form
SELECT BIN_TO_UUID(id, 1) AS uuid, amount FROM orders_uuid;

C# Generation of UUIDv7 (using a modern library)

using System;
using UUIDNext; // or similar UUIDv7 implementation

public static class IdFactory
{
    public static Guid NewOrderedId() => Uuid.NewDatabaseFriendly(Database.MySql);
    // or Uuid.NewSequential() depending on library

    public static byte[] ToBinary(Guid g) => g.ToByteArray(); // careful with endianness
}

For strict RFC 9562 UUIDv7, prefer a library that implements the standard (e.g., UUIDNext, UuidExtensions, or a hand-rolled implementation that places the 48-bit Unix timestamp in the high bits).

JavaScript / Node.js (using uuid package + custom v7)

import { v7 as uuidv7 } from 'uuid'; // modern uuid package supports v7

const id = uuidv7();                 // string
const binary = Buffer.from(id.replace(/-/g, ''), 'hex'); // for BINARY(16)

Application-Level Insert Pattern (Hybrid)

// Generate public UUID client-side, let MySQL assign the clustering key
var publicId = IdFactory.NewOrderedId();
await db.ExecuteAsync(
    @"INSERT INTO orders_hybrid (public_id, customer_id, amount)
      VALUES (@publicId, @customerId, @amount);
      SELECT LAST_INSERT_ID();",
    new { publicId = publicId.ToByteArray(), customerId, amount });

Alternatives and Trade-off Matrix

When to Prefer Each

• Single-primary or strongly consistent single-writer: BIGINT AUTO_INCREMENT. Simplest, fastest, smallest.
• Multi-region, offline clients, or microservices that must generate IDs independently: UUIDv7 stored as BINARY(16), or the hybrid pattern.
• Public-facing identifiers that must be opaque: Keep a sequential internal primary key and expose a UUID (or ULID) as a unique secondary column. This preserves InnoDB’s optimal clustering while giving the application a globally unique, non-guessable identifier.
• Extremely high insert rates with sharding: Consider application-level sequence generators or Snowflake-style IDs that embed a shard/worker ID and timestamp.

Practical Recommendations

1. Never use CHAR(36) or VARCHAR(36) as a primary key in a high-volume InnoDB table.
2. Prefer BINARY(16) for any UUID primary key and convert at the application boundary.
3. Prefer UUIDv7 (or a properly reordered UUIDv1) over UUIDv4 when a UUID primary key is required.
4. For most new systems the hybrid pattern (sequential clustered primary key + unique UUID) delivers the best balance of performance, storage, and operational flexibility.
5. Monitor INFORMATION_SCHEMA.INNODB_METRICS (or Performance Schema) for page-split rates and buffer-pool hit ratios when experimenting with random keys.
6. After bulk loading random UUIDs, run OPTIMIZE TABLE (or equivalent online rebuild) if fragmentation becomes measurable.

Conclusion

AUTO_INCREMENT remains the performance champion for InnoDB because it aligns perfectly with the clustered index design. Random UUIDv4 primary keys impose a measurable and growing tax on inserts, storage, and cache efficiency once tables exceed the buffer pool. Ordered UUID variants (especially UUIDv7) and the hybrid sequential-plus-UUID pattern recover most of the lost performance while retaining the architectural benefits of client-generated, globally unique identifiers.

Choose the key strategy that matches your consistency, distribution, and scale requirements—then measure. The difference between a well-chosen primary key and a naïve UUID primary key can easily be an order of magnitude in write throughput and several times the storage cost at large scale.

---

Tools and Versions

Need some UUIDs for testing? Try our tools for generating UUIDv4   UUIDv7   ULID   NanoID and decoding validating.
For a detailed comparison of UUID versions, visit our Complete Guide to UUID Versions.