UUID vs Auto-Increment: What Your MySQL Table Pays for the Convenience

Both work. One costs you twice the disk space and a lot more write IOPS.

Passing by a question on X that got a lot of engagement: “Tech Lead tells you: ‘Let’s use UUID everywhere instead of auto-increment IDs.’ Will you agree?”

Most replies landed in two camps. One side said UUID is modern, distributed-friendly and just better. The other said auto-increment is faster and simpler. Both camps are partially right. Neither camp talked about what actually happens inside the database when you make the call.

UUIDs are not wrong. Auto-increment is not wrong. But MySQL has opinions about what happens at the storage layer when you pick one over the other, and those opinions compound quietly until someone files a ticket about slow queries on a table that was supposed to be simple.

Here is what the database actually pays when you make this call without reading the fine print.

Why InnoDB Cares So Much About Primary Key Order

MySQL’s InnoDB engine stores every table as a clustered index. That phrase matters more than it sounds.

In a clustered index, the table’s row data lives inside the B+ tree itself, specifically in the leaf nodes, sorted by primary key. There is no separate “heap” of rows that the index points to. The primary key value is not just an identifier; it determines where in the tree the row physically sits.

When you insert a new row, InnoDB has to find the right position in the tree and slot it in.

With an auto-increment integer, every new row has a key value larger than everything already there, so InnoDB always appends to the rightmost leaf and pages fill to about 94%. Predictable, fast, boring in the best way.

With a random UUID (UUIDv4 specifically), each new row’s key is statistically as likely to land in the middle of the tree as at the end. InnoDB reads the correct page into memory, discovers the page is full, splits it in half, and writes both halves back. After the split, both pages are roughly 50% full. The next random insert might hit any page. Every write that causes a split costs at minimum two I/O operations where one would have sufficed.

This is the mechanism. The consequence is that a table with random UUID primary keys grows to nearly twice the physical size of the same data with sequential keys, because the pages never fill properly. MySQL’s own documentation states that sequential inserts produce pages that are “about 15/16 full,” while random inserts yield pages that are “from 1/2 to 15/16 full.” In practice with pure random UUIDs, you land near the bottom of that range.

The write amplification compounds over time too. A half-full page sitting in the InnoDB buffer pool is a cache slot wasted on empty space. Under memory pressure, MySQL evicts pages to disk. With a random UUID table, more pages means more evictions, which means more reads from disk on the next query that needs those rows. The performance degrades gradually, not all at once, which is why it often gets attributed to vague “database slowness” rather than traced to the primary key choice made eighteen months earlier.

That is not opinion. That is the InnoDB B+ tree doing exactly what it was designed to do, just in the direction you did not want.

The Storage Multiplier Nobody Mentions in the Thread

The page fill factor is only part of it. The other part is raw per-row cost.

Auto-increment BIGINT

• Storage per primary key value: 8 bytes
• Insertion behavior: sequential, pages fill to 94%
• Secondary index cost: 8 bytes per entry as the row pointer
• Across 4 secondary indexes plus the clustered index: 40 bytes of key data per row total

UUID stored as CHAR(36)

• Storage per primary key value: 36 bytes (32 hex digits plus four hyphens, stored as a string)
• Insertion behavior: random, pages fill to roughly 50%
• Secondary index cost: 36 bytes per entry, on every index you add
• Across 4 secondary indexes plus the clustered index: 180 bytes of key data per row total

UUID stored as BINARY(16)

• Storage per primary key value: 16 bytes
• Insertion behavior: still random if UUIDv4, the page fill problem does not go away
• Secondary index cost: 16 bytes per entry
• Across 4 secondary indexes plus the clustered index: 80 bytes of key data per row total

Every secondary index you create on a table with a UUID primary key stores that UUID as its row pointer. In InnoDB, secondary indexes do not point to a row’s physical location; they store the primary key value and use that to look up the row in the clustered index. So a table with four secondary indexes stores the primary key value five times per row in total: once in the clustered index, once as a suffix in each secondary index leaf.

The BIGINT table stores 40 bytes of key data per row in that scenario. The CHAR(36) UUID table stores 180 bytes. That difference compounds with every row you write, every index you add, and every buffer pool cache miss on a page that is only half full.

When UUID Wins Without Question

UUIDs exist for real reasons, and the performance cost is worth paying in specific situations.

1. Distributed ID generation. Auto-increment requires a single authoritative sequence; you cannot generate IDs independently on two application servers without coordination. UUIDs are generated client-side, no lock needed, no round-trip to the database.
2. Merging data across systems. If you sync rows between a mobile client, a secondary database and a primary, UUID eliminates collision risk. Auto-increment IDs from two sources will overlap.
3. Exposing IDs in URLs or APIs. An auto-increment integer leaks row count. /orders/10431 tells the world you have at least 10,431 orders. A UUID exposes nothing.
4. Multi-tenancy with data isolation. UUID primary keys make it structurally harder to accidentally cross tenant boundaries via a guessed sequential ID.

If any of these apply, UUID is the right choice. The question then is which UUID and how you store it.

The Three Options That Actually Exist

Option 1: Auto-increment BIGINT

Use this when you have a single-database setup, no external ID exposure concern, and maximum insert throughput matters. A table that will grow large benefits the most from sequential keys.

A signed BIGINT gives you 9.2 quadrillion possible values. You will not run out. Insert performance is as good as InnoDB gets. Joins are fast because foreign keys referencing it are 8 bytes. Every secondary index is as compact as possible.

The only real weakness is that generating IDs requires the database to be reachable, which matters if you need to know the ID before writing. For example, if your API returns the new resource ID in the response body before the transaction commits, you need the ID first. That is a real constraint in some architectures.

Option 2: UUID as BINARY(16) with uuid_to_bin() and swap_flag

MySQL 8 ships with a function specifically for this problem. The call is uuid_to_bin(uuid(), 1) where the second argument is the swap flag. It reorders the time-low and time-high components of a version-1 UUID to make the resulting binary more sequential. You get a UUID for external exposure, the database stores 16 bytes instead of 36, and the swap pushes insertion behavior closer to sequential.

-- Create the table with a swap-optimised binary UUID default
CREATE TABLE orders (
  id BINARY(16) NOT NULL DEFAULT (uuid_to_bin(uuid(), 1)),
  user_id BIGINT NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
);

-- Query by ID (pass swap_flag=1 to bin_to_uuid to get the canonical string back)
SELECT bin_to_uuid(id, 1) AS uuid, user_id
FROM orders
WHERE id = uuid_to_bin('your-uuid-string-here', 1);

In a Laravel project, the spatie/laravel-binary-uuid package handles this transparently. You declare the column as binary(16) in the migration, attach the trait to the model, and Eloquent handles the conversion on reads and writes.

use Spatie\BinaryUuid\HasBinaryUuid;

class Order extends Model
{
    use HasBinaryUuid;
    public $incrementing = false;
    protected $keyType = 'string';
}

One limitation worth knowing: the swap flag works with UUIDv1 (time-based). If you feed UUIDv4 (random) into uuid_to_bin(..., 1), the swap achieves nothing because there is no time component to reorder. And UUIDv1 encodes the MAC address of the generating machine, which is a mild but real information leak.

Option 3: UUIDv7

UUIDv7 is the current recommendation from the IETF. RFC 9562, published in May 2024, formally defines it. The first 48 bits are a Unix millisecond timestamp. The remaining bits are random, providing uniqueness. Because the most significant bits are time-ordered, UUIDv7 values increase monotonically over time, much like an auto-increment ID. InnoDB treats inserts almost exactly like sequential insertions, and your page fill factor climbs back toward 94%.

You get a UUID (globally unique, collision-resistant, safe to expose) while keeping insertion performance close to auto-increment BIGINT. It is the cleanest answer to the problem.

In a Laravel and MySQL setup, you generate UUIDv7 in PHP and store it as BINARY(16). The Symfony UID component supports this with a single call.

use Symfony\Component\Uid\Uuid;

// In a model boot or factory
$id = Uuid::v7()->toBinary(); // returns 16 raw bytes, ready for BINARY(16) column

-- Migration column definition
id BINARY(16) NOT NULL PRIMARY KEY

MySQL does not yet have a native uuid_v7() function. You generate the value in application code and pass 16 bytes to the query. Not elegant, but not a problem either.

How to Decide

The decision is not UUID vs auto-increment. The real question is which constraints apply to your system.

Start here:

1. Do you need to generate IDs without a database round-trip? If yes, auto-increment is out.
2. Will rows from multiple sources be merged into this table? If yes, you need globally unique IDs.
3. Is ID exposure in URLs or APIs a security or privacy concern? If yes, sequential integers leak information.
4. Is your database MySQL/InnoDB, and will this table grow past a few million rows? If yes, storage and write performance matter enough to think carefully about UUID format.

If you answered yes to all four: use UUIDv7 stored as BINARY(16). You get global uniqueness and near-sequential insertion performance. The only cost is generating UUIDv7 values in application code, which is a one-line library call.

If you answered yes to points 1 through 3 but no to point 4 (small table, low write volume): UUIDv4 as CHAR(36) is acceptable. The page split problem is real but the absolute cost at low scale is manageable. Optimise when the table size actually becomes a problem, not before.

If you answered no to points 1 through 3: auto-increment BIGINT. The operational simplicity alone is worth it.

The Gap Between the Thread and the Table

UUID everywhere makes distributed systems cleaner and avoids a whole category of ID collision bugs. The instinct is not wrong.

What the Slack thread skips is the format. “UUID” is not one thing. UUIDv4 with random bits has different storage characteristics than UUIDv7 with time-ordered bits, and “CHAR(36) vs BINARY(16)” is not a cosmetic choice. It determines how much your secondary indexes cost on every read, and how much buffer pool memory you burn keeping half-empty pages cached.

The conversation worth having is: which version, stored how, on which database engine. That is thirty seconds longer than a thumbs-up reaction. Your database will be running that table for the next five years.

The database does not care what you put in the Slack thread. It only cares what you put in the CREATE TABLE.