Random UUID keys fragment InnoDB. Ordered ones write clean

InnoDB stores a table physically ordered by its primary key. Give that table a random UUID key and every insert lands at a random position in the B-tree, splitting pages that were nowhere near full and evicting buffer-pool pages that inserts a moment later will need again. A time-ordered UUID — version 7, or Laravel's Str::orderedUuid() — restores append-like behaviour while keeping everything that made UUIDs attractive. The difference is invisible on a small table and structural on a busy one, which is exactly the trap: the tables most likely to get UUID keys — audit logs, message ledgers, event streams — are the highest-write tables in the system, and the cost arrives months after the schema shipped.

This came up while building a messaging cost ledger where the audit table takes a row for every API call and the ledger a row per message. Both wanted UUID keys for good reasons. Both would have been quietly wrong with random ones.

Why the clustered index cares where your key lands

An InnoDB table is its primary key index. Rows live in 16 KB pages ordered by key value, so the key you choose decides the physical write pattern:

• Auto-increment: every new key is the largest yet. Inserts append to the right-most page; pages fill completely and are written once.
• Random UUIDv4: every new key is a coin flip across the entire keyspace. Inserts land in arbitrary pages; full pages split into two half-full ones; the working set for inserts becomes the whole index.

Two costs compound. Page splits leave the index physically larger than its data — pages hovering half-full mean the same rows occupy roughly twice the pages, and every one of them flows through the buffer pool. And because the next insert is equally likely to touch any page, the buffer pool stops being a cache of hot pages and becomes a lottery. Secondary indexes make it worse: in InnoDB every secondary index entry carries the primary key as its row pointer, so a 36-character random key is paid for again in every index on the table.

The cost arrives late

The reason this survives review and load testing: while the whole index fits in the buffer pool, random inserts are nearly free — page splits happen in memory and the damage is only size. The behaviour changes when the index outgrows the pool. Random inserts now regularly touch pages that are not resident, each one a disk read before the write can proceed, and insert latency develops a long tail that no code change explains.

Nothing in the application changed. The table crossed a size threshold, and a decision made in a migration file eighteen months earlier started charging interest. On an audit table that takes a row per API call, "eighteen months" is optimistic.

Time-ordered UUIDs restore the append

A UUIDv7 leads with a millisecond timestamp, so keys generated now sort after keys generated a moment ago. Inserts return to the right-most page, splits become rare, and the buffer pool goes back to caching the hot tail instead of the whole index. You keep what UUIDs bought you: client-side generation before the row exists, no cross-environment collisions, no information leak about row counts the way sequential integers leak them.

Laravel has shipped this for years, with one version wrinkle worth knowing precisely:

• Laravel 12 and 13: the HasUuids trait generates UUIDv7 out of the box. If you use it, you are already ordered.
• Laravel 9.30 through 11: HasUuids generated ordered UUIDs too (a timestamp-first arrangement rather than spec v7), so the default was safe there as well.
• The trap is everything that is not HasUuids: a Str::uuid() in a creating callback, a package that mints its own v4, a database-side default — MySQL's own UUID() is a version 1 laid out time-low first, which interleaves almost as badly as random.

Because the default has changed shape across versions, the codebase this came from pins the choice explicitly rather than inheriting whatever the framework does this year:

use Illuminate\Database\Eloquent\Concerns\HasUuids;
use Illuminate\Support\Str;

/**
 * Drop-in replacement for HasUuids that guarantees time-ordered ids,
 * regardless of framework version or future default changes. New rows
 * land adjacent in the clustered index; existing rows are unaffected.
 */
trait OrderedUuid
{
    use HasUuids;

    public function newUniqueId(): string
    {
        return (string) Str::orderedUuid();
    }
}

Then every high-write model states it:

class ApiLog extends Model
{
    use OrderedUuid;
    // ...
}

An explicit trait also gives the decision a home for its documentation — the comment explains why the ordering matters, which is what stops a future refactor from "simplifying" it back to Str::uuid().

Or just use auto-increment?

Fair question, since it makes the whole problem vanish. Sometimes the answer is yes — a purely internal table that never shows its ids to anyone loses nothing by being keyed with a bigint.

The ledger and audit tables kept UUIDs for two specific reasons. Their ids appear in API responses and admin URLs, and sequential integers there leak volume — how many messages you send a day is readable from the gap between two ids, which is commercial information handed to anyone with two data points. And their rows are correlated with external systems by ids that must be mintable before the row exists, from more than one process, without coordination. Ordered UUIDs keep both properties and give back the write pattern; they are the middle option, not a compromise.

What ordered keys cost you

Two honest trade-offs, one real and one usually imaginary.

The real one: a time-ordered id carries its creation time. Anyone who can read the id can recover roughly when the row was created, and sort any set of ids chronologically. For an internal audit log this is a feature. For a public-facing identifier it may not be — if exposing creation time matters, expose a separate random public id and keep the ordered key internal, rather than giving up the write pattern.

The usually-imaginary one: "all inserts hitting the last page creates a hotspot." True in the sense that auto-increment has the same property; InnoDB has handled right-most-page insertion as its most common case for decades. Unless you are sharding writes across servers by key range, the hot tail is the fast path, not a problem.

One adjacent decision while you are here: Laravel's uuid() migration column is CHAR(36). Storing UUIDs as BINARY(16) halves-and-more the key that every secondary index carries. It costs readability in ad-hoc queries; on a table with several indexes and heavy writes it is often worth it, and it is far easier to choose on day one than to convert later.

Measure your own table before believing any of this

Fragmentation is measurable, so check rather than assume. Free space trapped in the table is visible per table:

SELECT table_name,
       ROUND(data_length / 1024 / 1024)   AS data_mb,
       ROUND(index_length / 1024 / 1024)  AS index_mb,
       ROUND(data_free / 1024 / 1024)     AS free_mb
FROM information_schema.tables
WHERE table_schema = DATABASE()
ORDER BY data_free DESC;

A high free_mb relative to data_mb on a UUID-keyed, insert-heavy table is the signature — space allocated, half-emptied by page splits, and not returned. For the before-and-after, generate a few million rows keyed with Str::uuid() and again with Str::orderedUuid() and compare the two numbers; the gap is the argument, and it is more persuasive from your own schema than from anyone's blog post.

Two things to know about fixing an existing table. New ordered keys do not repair old fragmentation — they stop adding to it, and inserts stop landing in the fragmented middle, which is most of the win. And OPTIMIZE TABLE (an online rebuild in modern MySQL) compacts what history left behind, at the price of a rebuild on what is, by definition, your busiest table — schedule it accordingly.

Where this landed in practice

In the messaging system this came from, the two tables that take a row per event — the cost ledger that every send opens and every webhook status updates, and the audit log recording each API call — both carry the trait. They are precisely the tables whose write rate is decided by customers rather than by engineers, which makes them the tables least able to afford a write pattern that degrades with size.

Both ship that way in laravel-whatsapp-cost-control (MIT, Laravel 12 and 13) — the migrations and models arrive with ordered keys already wired, because a default you have to remember to apply is a default that will eventually be forgotten on the one table that mattered.