UUID v1, v4, v6, v7 and Snowflake in MySQL, Benchmarked

Published · 22 min read

In the previous article, I showed the cost each extra index adds to your INSERTs and how UUID changes that math. The conclusion was that indexes are not free, and UUID amplifies the problem.

But that analysis only compared INT vs UUID (v4). And in the real world, “UUID” is not a single thing. There are six relevant versions, each with different trade-offs. On top of that, there is the Snowflake ID, which is not even a UUID and in many scenarios is the best choice.

This article was born from a simple question, “if UUID v7 is time-ordered, does it behave like INT AUTO_INCREMENT in MySQL?”. The short answer, almost. The long answer is this article.

I ran a benchmark with 6 identifier types, 4 index scenarios, 7 INSERT strategies and 5 SELECT types.

The result? UUID is not the villain. The villain is using UUID v4 as CHAR(36) without thinking about the indexing strategy alongside it.

Understanding each identifier type

If you already know the difference between v1, v4, v6, v7 and Snowflake, you can skip ahead to the B-Tree section, how-mysql-stores-all-of-this-internally. If not, this section explains why the choice of ID type is an architecture decision that will follow you around for years.

INT AUTO_INCREMENT, the classic that still works

The good old sequential integer. MySQL generates it, it is sequential by nature, it takes 4 bytes (INT) or 8 bytes (BIGINT). Inserts always go to the end of the B-Tree. Zero page splits. It is the performance reference everything else is measured against.

The problem shows up when your system grows. If you have 3 microservices generating orders at the same time, each talking to a different database, who coordinates the IDs? You need some centralized mechanism, a ticket server, or pre-allocated ranges, or some form of distributed lock. That adds complexity and a single point of failure.

Also, sequential IDs are predictable. An attacker can iterate GET /api/users/1, /2, /3 and find out how many users you have, your growth rate, and access records they should not see (if authorization is weak). Sounds unlikely? OWASP lists “Insecure Direct Object References” as one of the most common API vulnerabilities.

UUID v1 timestamp + MAC address (RFC 4122, 2005)

The first attempt at solving the distributed ID problem. v1 combines a high-precision timestamp (100 nanoseconds, using the Gregorian calendar with the epoch at October 15, 1582) with the generating machine’s MAC address. That guarantees uniqueness without central coordination. If each machine has a different MAC and the clock never goes backwards, the UUIDs never collide.

But there are two problems.

Privacy, your machine’s MAC address is embedded in the UUID. RFC 9562 is blunt about it, “Exposed MAC addresses can be used as an attack surface to locate network interfaces and reveal various other information about such machines”. And with VMs and containers, the MAC does not even truly guarantee uniqueness anymore.

Performance in MySQL, this is where it gets interesting. The timestamp bits in v1 are scrambled. The structure is time_low (32 bits) + time_mid (16 bits) + time_hi_and_version (16 bits). The time_low is the least significant part of the timestamp, and it comes first. As a result, two v1 UUIDs generated back to back can have completely different first 32 bits, because the part that changes the most (time_low) is the one leading.

MySQL has UUID_TO_BIN(uuid, 1) with a swap flag that reorders the timestamp bytes into the right order. But it is a manual conversion. You have to remember to apply it on every INSERT and every SELECT. Forgot the swap flag in one query? Full scan.

UUID v4, pure random (RFC 4122, 2005)

122 bits of pure randomness. No timestamp, no MAC, no information about when or where it was generated. Maximum privacy, and the simplest to implement, every language has a native uuid4().

And maximum performance cost. Every INSERT lands in a random position in the B-Tree. To understand why that is so bad, let’s go into InnoDB and see how it manages data pages.

InnoDB organizes data in 16KB pages. When the PK is sequential, it fills each page to ~94% before creating the next one, which is the optimal fill factor. With random UUID v4, every new INSERT can land in any page. If the target page is already full, InnoDB does a page split, splitting the page in two, each with ~50% occupancy. In large tables, that means:

Percona documented a case where a table with 1 billion rows and UUID v4 as CHAR(36) took up almost 1 TB. After reorganizing with an ordered key, it dropped to ~450 GB. Half. The fill factor of the index pages was ~50% with random UUID vs ~90% with a sequential key.

UUID v6, v1 cleaned up (RFC 9562, 2024)

In May 2024, the IETF published RFC 9562, the first update to the UUID specification in almost 20 years. One of the explicit motivations was the database performance problem:

“UUID versions that are not time ordered, such as UUIDv4, have poor database-index locality. This means that new values created in succession are not close to each other in the index.”

v6 is the most conservative solution. It takes the same data as v1 (Gregorian timestamp + node/MAC), but reorders the timestamp bits so the most significant ones come first. As a result, v6 UUIDs are naturally sortable without needing a swap flag.

The RFC is blunt about when to use it, “Systems that do not involve legacy UUIDv1 SHOULD use UUIDv7 instead”. In other words, v6 exists as a migration bridge for those who already have v1 in production. If you are starting from scratch, skip straight to v7.

UUID v7, the modern recommendation (RFC 9562, 2024)

48 bits of Unix timestamp (milliseconds) in the most significant bits + 74 bits of randomness. No MAC address, no hardware information. Private and sortable.

The 48 timestamp bits give 1 millisecond resolution and cover up to the year 10,889. If two v7 UUIDs are generated in the same millisecond, the random part (rand_a + rand_b = 74 bits) guarantees uniqueness. Some implementations use a monotonic counter in the rand_a bits to guarantee total ordering even within the same millisecond.

In practice, v7 behaves almost like an AUTO_INCREMENT for InnoDB. Inserts always go to the end of the B-Tree, pages keep the optimal fill factor, zero page splits. The difference is that it guarantees global uniqueness without coordination.

Adoption is accelerating. PostgreSQL 18 added a native uuidv7(). Python 3.14 added uuid.uuid7() to the stdlib. Java, Go, Rust and JavaScript already have mature libraries.

Snowflake ID, Twitter’s solution (2010)

In 2010, Twitter faced a concrete problem. It needed to migrate from MySQL to Cassandra to scale, but Cassandra has no AUTO_INCREMENT. The engineering team published the post “Announcing Snowflake” on the engineering blog and open-sourced the code.

The requirements were simple but demanding, “We needed something that could generate tens of thousands of ids per second in a highly available manner […] these numbers have to fit into 64 bits”.

The solution: a 64-bit ID (half a UUID) made of:

Twitter uses an epoch of November 4, 2010. Discord uses January 1, 2015. Instagram uses a variation with 41 bits timestamp + 13 bits shard ID + 10 bits sequence.

The advantages are clear: sequential (ordered inserts), compact (fits in an 8-byte BIGINT, half a UUID), fast to generate (no crypto random needed), and the embedded timestamp eliminates the need for a separate created_at column.

The trade-off? You need to manage machine IDs. In a dynamic Kubernetes cluster, that is extra work (the “Stateless Snowflake” paper on arXiv covers solutions for this). And the 64 bits give “only” ~4.6 × 10¹⁸ possible IDs, versus ~5.3 × 10³⁶ for UUID v4. In practice, that is plenty.

What about ULID?

Worth mentioning ULID (Universally Unique Lexicographically Sortable Identifier), which is similar to UUID v7, 48 bits of timestamp + 80 bits of random, encoded in Crockford Base32. The main difference is that ULID has 80 bits of randomness (vs 74 in v7) and uses a more readable encoding. But ULID is not a UUID, it does not fit in a database UUID field and there is no RFC standardizing it. With the arrival of v7 in RFC 9562, the recommendation is clear. If you are going to use the UUID format, use v7. If you want something more compact, consider Snowflake.

How MySQL stores all of this internally

Before getting to the numbers, it is worth understanding the mechanism behind them. This section explains why the ID type has so much impact and why so many people forget the cost.

B+Tree and Clustered Index

InnoDB uses a structure called a B+Tree to organize data and indexes. In a B+Tree, the data lives in the leaves (leaf nodes) and the internal nodes store pointers for navigation. The search is logarithmic. For 1 million rows, it takes ~3-4 levels of nodes to reach the data.

The crucial detail is that in InnoDB, the Primary Key defines the clustered index. That means the table’s data is stored physically in PK order. It is not a coincidence, it is the physical organization on disk. When you insert a row with PK = 42, it physically ends up between rows 41 and 43.

That has consequences:

Page Splits, the cost

InnoDB allocates data in 16KB pages. When a page is full and new data needs to be inserted into it, InnoDB performs a page split, it creates a new page, moves half the data to it, and inserts the new data. Each page ends up with ~50% occupancy.

With a sequential PK, this rarely happens. InnoDB fills a page to ~94% and starts the next one. With a random PK, page splits are constant, because any page can be the target.

The impact is multiplicative:

The cost, secondary indexes carry the PK

In InnoDB, every entry in a secondary index stores the row’s PK as its pointer. That means if your PK is a 16-byte UUID (BINARY(16)) and you have 5 secondary indexes, each row stores the PK 6 times in total, once in the clustered index + once in each secondary index.

With CHAR(36), that is 36 bytes per copy. With 9 indexes, that is 10 copies × 36 bytes = 360 bytes of key alone per row. Switching to BINARY(16), it drops to 10 × 16 = 160 bytes. And with INT (4 bytes), it is only 10 × 4 = 40 bytes.

That is why PlanetScale recommends in its MySQL for Developers course, “unsigned big integers are the most reliable and performant data type” for PKs.

CHAR(36) vs BINARY(16): never use CHAR(36)

If you are storing UUIDs as CHAR(36) in production, you are wasting 56% of the space for no reason. And that waste multiplies with every index.

-- WRONG, 36 bytes per UUID, string comparison
CREATE TABLE users (
    id CHAR(36) PRIMARY KEY,
    ...
);

-- RIGHT, 16 bytes per UUID, binary comparison (faster)
CREATE TABLE users (
    id BINARY(16) PRIMARY KEY,
    ...
);

-- To convert in the application
INSERT INTO users (id) VALUES (UNHEX(REPLACE('550e8400-e29b-41d4-a716-446655440000', '-', '')));
SELECT HEX(id) FROM users;

In this benchmark, all UUIDs are BINARY(16). The numbers you are about to see are already the optimized scenario. With CHAR(36), everything would be significantly worse.

Methodology

Environment

Why trimmed mean and not a simple average?

In I/O benchmarks, outliers are common, an OS garbage collection, a Docker flush, an antivirus background scan. A single bad run can skew the average by 50%+.

The solution, 7 runs per scenario, discard the best and worst times, and average the remaining 5 (trimmed mean). That gives much more stable results than a simple average. Our StdDev dropped from ~30% to ~5% in most scenarios.

Each scenario also has a disposable warmup of 10k rows before the real measurements, to warm up the InnoDB buffer pool and eliminate the “cold start” effect.

Windows optimizations

Windows/NTFS is notoriously slower than Linux/ext4 for fsync operations. To reduce variance without compromising the results:

Important, these settings are for benchmarking. In production, keep innodb_flush_log_at_trx_commit = 1 for durability.

Index scenarios

What was measured

All the code: https://github.com/francocontigo/index-insert-mysql-benchmark

Part 1: INSERT, ID types

Throughput vs Indexes (200k rows)

The first thing you notice, INT AUTO and Snowflake dominate at baseline, but they all converge as indexes increase.

Observation 1: UUID v7 is the most resilient to indexes

Looking at the drop column, v7 loses only 35% of its throughput going from zero to 9 indexes. INT AUTO loses 61%.

How is that possible?

The explanation is subtle. INT AUTO starts with very high throughput (219k/s) because the baseline is trivial, append-only, no index work. When you add 9 indexes, the cost of maintaining the indexes dominates. And since the columns in the benchmark are the same type as the PK, with INT the secondary indexes are also INT (4 bytes), fast to maintain, but the absolute cost is high because the baseline was sky high.

With UUID v7, the baseline is already lower (129k/s) because BINARY(16) is bigger. But since v7 is sequential, page splits in the secondary indexes are minimal. The cost of adding an index is almost just the linear write into the B-Tree, without the cascading fragmentation that happens with v4.

v4 gets the worst of both worlds, the lowest baseline and a steep drop. Randomness in the PK + randomness in the secondary indexes = fragmentation in every structure.

Observation 2: With many indexes, the ID type matters less

Look at the “many (9)” column of the heatmap: INT (86k), v1 (83k), v7 (84k), v6 (78k), Snowflake (77k), all between 77–86k/s. The exception is v4 (45k).

That makes sense. With 9 indexes, every INSERT effectively does 10 writes (1 to the table + 9 to the indexes). The I/O cost of the indexes dominates. The difference between “fast sequential PK” and “slightly slower sequential PK” becomes small when the bottleneck is maintaining 9 B-Trees.

The lesson, the ID type matters a lot when you have few indexes (where the PK dominates the cost). With many indexes, they all suffer in a similar way, except v4, which suffers more because it fragments everything.

Observation 3: 50k vs 200k, volume amplifies the difference

With 50k rows, the differences are smaller and noisier. The whole table fits in the buffer pool and many page splits are absorbed in memory. With 200k, the buffer pool starts having evictions and the real I/O pattern shows up.

If your system has tables with millions of rows (and it probably does), the difference will be even more pronounced than the numbers here.

Part 2: INSERT, optimized strategies

Same ID types, but now varying the insertion technique. The question comes up:

What gets you more, changing the ID type or changing how you insert?

Observation 4: Batch size, the jump from 100 to 1000 rows is what matters most

Going from batch 100 to batch 1000 gives a 40–75% throughput jump. After 1000, the gain is incremental. And multi-row VALUES (a single SQL statement with 5000 values) is consistently the fastest.

Why is batch 100 so bad? Every commit in InnoDB does (with default settings) an fsync of the redo log. With batch 100, that is 2,000 commits to insert 200k rows. With batch 5000, it is only 40 commits. Fewer fsyncs = less waiting on I/O = more throughput.

Observation 5: With 9 indexes, large batches still pay off

With 9 indexes, absolute throughput drops, but the pattern holds. Multi-row VALUES and batch 5k stay at the top. And once again, v4 sits isolated at the bottom.

One point, batch 10k is not necessarily better than batch 5k. In several scenarios, 10k was slightly slower. The likely reason is that MySQL needs to allocate more memory for the statement and the parsing overhead of a SQL statement with 10,000 VALUES starts to weigh. The sweet spot is between 1,000 and 5,000 for most scenarios.

Observation 6: UUID v4 with pre-sorting helps, but does not fix it

The idea is, if v4 is random and that causes page splits, what happens if we sort the data before inserting? The data arrives at the B-Tree in ascending order, minimizing splits.

The dashed green line in the chart shows that UUID v7 with multi-row reaches 84,341/s in the same scenario. v4 optimized to the max still sits 42% below default v7.

The lesson, pre-sorting is a good practice if you are stuck on v4 and cannot migrate. But if you can choose, choose v7, it is the optimization that pays for itself.

Observation 7: Comparisons, the best config for each type

The final ranking for 200k rows, many scenario (9 indexes), best strategy of each type:

  1. INT AUTO multi-row 5k: 89k/s
  2. UUID v7 multi-row 5k: 84k/s (−6% vs INT)
  3. Snowflake batch 5k: 82k/s (−8% vs INT)
  4. UUID v4 sorted batch 5k: 49k/s (−45% vs INT)

UUID v7 sits only 6% behind INT AUTO_INCREMENT in the heaviest scenario. That is an acceptable “tax” to gain global uniqueness, coordination-free distribution and ID privacy.

Part 3: SELECT, FOR THE LOVE OF GOD I AM DONE TALKING ABOUT INSERT

So far we have only talked about writes. But indexes exist to speed up reads. The trade-off only makes sense when we look at both sides.

Observation 8: Point Lookup 56-76x faster with an index

Without an index (baseline), a WHERE col_a = X does a full table scan in ~42ms. With any number of indexes, it drops to ~0.6ms. A speedup of 56-76x depending on the type.

And here one more thing shows up. With an index, the difference between ID types disappears. INT, UUID v4, UUID v7, Snowflake, they all sit at ~0.6ms. The data type matters for writes. For an indexed point lookup, it is irrelevant.

The reason is that the point lookup walks the secondary index (a B-Tree with few levels) and then does a bookmark lookup on the PK to fetch the full row. The PK size matters for the index size (disk space), but the latency of a single lookup is dominated by the number of tree levels, which is the same for all of them.

Observation 9: COUNT + GROUP BY, the most dramatic gain from indexes

This is the query that benefits the most from indexes.

INT/Snowflake are already relatively fast without an index (96ms vs 443ms for v4). That is because INT takes 4 bytes vs 16 bytes for BINARY(16). More rows fit in a 16KB page, more pages fit in the buffer pool, less I/O. Data size has a direct effect on the scan.

With an index, the gap shrinks but does not disappear (42ms vs 65ms). GROUP BY with an index uses an index scan (walks the index in order), and the size of the index entries matters. BINARY(16) has 4x more bytes per entry than INT.

Observation 10: Range Scan, where data size hurts

Here the difference is insane and does not depend on the number of indexes:

3.5x slower, consistently. The reason is structural: a range scan reads sequential pages from disk (or the buffer pool). With BINARY(16), each row + auxiliary columns is significantly bigger. More data = more pages = more time.

This is a cost inherent to UUID as a data type, regardless of version. If your application does a lot of range scans on BINARY(16) columns, consider a hybrid strategy: PK as UUID v7 (for distributed uniqueness), but filter and lookup columns as INT when possible.

Analyzing the Trade-Off

This one is my favorite in the article. On the left, INSERT throughput drops as indexes increase. On the right, SELECT latency drops (improves) with the same progression.

Three takeaways from this chart:

  1. The jump from baseline to few (2 indexes) is asymmetric. The INSERT cost is ~25%. The SELECT gain is ~98% (42ms to 0.6ms on the point lookup). Two well-chosen indexes are the best performance investment there is.
  2. After “few”, SELECT returns are diminishing. From few to medium to many, the point lookup barely changes (0.6ms to 0.6ms to 0.7ms). What improves a bit is ORDER BY (51ms to 44ms to 26ms, because more query scenarios are covered). But the INSERT cost keeps growing linearly.
  3. UUID v4 pays the highest price. It starts lower and drops faster. In the many scenario, it sits at 45k/s, almost half of v7 (84k/s). For reads, performance is similar to all the others. In other words, v4 pays more on INSERT without gaining anything on SELECT.

The lesson, 2-4 well-chosen indexes (based on real queries, not speculation) cover 90%+ of scenarios. 9 indexes “just in case” cost 60% of write throughput for a marginal read gain.

Recommendations off the top of my head

Or, what I take away from all this.

Which ID type to use?

Need global uniqueness + distributed?

UUID v7 if you want the RFC standard and maximum compatibility. Snowflake if you want maximum performance, 8 bytes, and can manage machine IDs.

Don’t need distributed?

INT AUTO_INCREMENT or BIGINT AUTO_INCREMENT. Simple, fast, 4–8 bytes. Validated for decades.

Already using UUID v4 in production and cannot migrate?

At the very least switch from CHAR(36) to BINARY(16), an immediate 56% space gain, use batch inserts of 1000+, sort the data before inserting when possible. Consider a gradual migration to v7, both formats coexist in the same BINARY(16) column.

Never use UUID v1 in new projects. RFC 9562 explicitly recommends v7. v1 only makes sense for compatibility with legacy systems.

How to INSERT the right way

  1. BINARY(16) for UUIDs, not CHAR(36). Always.
  2. Generate the ID client-side, not in MySQL. MySQL’s UUID() only generates v1 (with the MAC exposed) and is slower than generating it in the application.
  3. Batches of 1,000+ rows per commit. The jump from 100 to 1000 is the biggest gain.
  4. Multi-row VALUES when possible. INSERT INTO t (a,b) VALUES (1,2), (3,4), (5,6) is faster than executemany in most drivers because it sends a single SQL statement.
  5. If you use v4, sort before inserting. Not as good as v7, but it reduces page splits.
  6. For massive loads (>500k rows), consider LOAD DATA INFILE with a staging table + UNHEX().

How to audit your indexes

Before creating an index, use EXPLAIN:

EXPLAIN SELECT * FROM pedidos WHERE cliente_id = 13;

If type = ALL, it is a full scan, a candidate for an index. If type = ref or range, an index is already being used.

To find indexes that were never used:

SELECT object_schema, object_name, index_name, count_star
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE index_name IS NOT NULL
ORDER BY count_star ASC;

count_star = 0 means the index has never been accessed since the last restart. A candidate for removal. But check that it is not serving a weekly batch or a monthly report before dropping it.

Careful, performance_schema needs to be enabled (it is by default, but it consumes ~200MB of RAM). In this benchmark we disabled it because of the container’s memory limit.

Conclusion

UUID v7 is the best choice for anyone who needs distributed identifiers. It degrades only 35% with 9 indexes (vs 61% for v4), sits 6% behind INT in the heaviest scenario with an optimized strategy, and indexed reads are identical to every other type.

If you need fast range scans and do not need distributed, Snowflake is unbeatable, 8 bytes, sequential, 58ms range scan (vs 200ms for UUIDs).

UUID is not the villain. The villain is using random v4 as CHAR(36) without thinking about the indexing strategy, without proper batching, without measuring the impact.

#mysql#data-engineering#databases