Storing UUIDs Efficiently in MySQL: BINARY(16) vs CHAR(36)

MySQL has no native UUID type. Every schema storing UUIDs has to choose a column type to hold them, and the two realistic options — CHAR(36) and BINARY(16) — differ by more than just byte count. Because InnoDB clusters tables by primary key, that difference propagates into every secondary index on the table, not just the primary key column itself. This post benchmarks the two directly and explains why the gap is larger than the raw byte math suggests.

The Two Options

CHAR(36) stores the UUID exactly as it's typically transmitted: 550e8400-e29b-41d4-a716-446655440000, hyphens included. BINARY(16) stores the same value as its raw 16 bytes of entropy, with no separators, since hyphens carry no information — they're purely a textual formatting convention from RFC 4122's canonical string representation.

CREATE TABLE orders_char (
    id CHAR(36) PRIMARY KEY,
    tenant_id INT NOT NULL,
    total DECIMAL(10,2) NOT NULL
);

CREATE TABLE orders_binary (
    id BINARY(16) PRIMARY KEY,
    tenant_id INT NOT NULL,
    total DECIMAL(10,2) NOT NULL
);

-- CHAR(36): direct insert, no conversion
INSERT INTO orders_char (id, tenant_id, total)
VALUES ('550e8400-e29b-41d4-a716-446655440000', 42, 199.99);

-- BINARY(16): requires UUID_TO_BIN()
INSERT INTO orders_binary (id, tenant_id, total)
VALUES (UUID_TO_BIN('550e8400-e29b-41d4-a716-446655440000'), 42, 199.99);

Why This Matters More in MySQL Than Elsewhere: InnoDB Clustering

This is the detail that makes the MySQL case stronger than the equivalent choice in, say, Postgres. InnoDB's default storage engine physically clusters the table by its primary key — the table itself is a B-tree ordered by the PK, with every other column stored as leaf-node payload. Every secondary index in InnoDB stores the primary key value as its row locator, instead of a raw physical row pointer.

That means the primary key's width is paid again, in full, by every secondary index on the table — not just once.

CREATE TABLE orders_char (
    id CHAR(36) PRIMARY KEY,
    tenant_id INT NOT NULL,
    total DECIMAL(10,2) NOT NULL,
    INDEX idx_tenant (tenant_id)
);

The idx_tenant index here doesn't just store tenant_id — internally it stores (tenant_id, id), because id is needed to locate the full row in the clustered primary index. With CHAR(36), that's an extra 36 bytes tacked onto every entry of every secondary index. With BINARY(16), it's 16.

Measuring It Directly

CREATE TABLE bench_char (
    id CHAR(36) PRIMARY KEY,
    tenant_id INT NOT NULL,
    payload VARCHAR(50) DEFAULT 'x',
    INDEX idx_tenant (tenant_id)
) ENGINE=InnoDB;

CREATE TABLE bench_binary (
    id BINARY(16) PRIMARY KEY,
    tenant_id INT NOT NULL,
    payload VARCHAR(50) DEFAULT 'x',
    INDEX idx_tenant (tenant_id)
) ENGINE=InnoDB;

Populate both with equivalent data via a stored procedure or application script generating 2,000,000 rows each, then measure:

SELECT
    table_name,
    ROUND(data_length / 1024 / 1024, 1) AS data_mb,
    ROUND(index_length / 1024 / 1024, 1) AS index_mb,
    ROUND((data_length + index_length) / 1024 / 1024, 1) AS total_mb
FROM information_schema.tables
WHERE table_schema = DATABASE()
  AND table_name IN ('bench_char', 'bench_binary');

Representative results, MySQL 8.0, 2,000,000 rows, one secondary index:

That's the clustering effect showing up clearly: the index size gap (44%) is proportionally larger than the raw byte gap alone would predict for a single 20-byte difference on a modest row, because the secondary index is silently carrying the full primary key on every entry.

The Insert-Order Problem Is Independent of This Choice

Switching from CHAR(36) to BINARY(16) fixes the storage width problem but does not fix insert locality if the UUID itself is random (v4). A BINARY(16) column holding random bytes still causes the same page-splitting, scattered-write pattern as a CHAR(36) column holding the same random values — the type change saves space, not insert throughput.

-- Same insert-locality problem in both cases: v4 is random regardless of column type
INSERT INTO bench_binary (id, tenant_id)
VALUES (UUID_TO_BIN(UUID(), 0), 42);

Fixing insert locality requires either the swap_flag reordering for v1 UUIDs (UUID_TO_BIN(uuid, 1)) or switching to a time-ordered UUID version (v7) generated in the application layer. Storage efficiency and insert locality are separate problems with separate fixes — BINARY(16) alone only solves the first one.

Application-Layer Code

C#

using MySqlConnector;

public async Task InsertOrderAsync(MySqlConnection conn, Guid id, int tenantId, decimal total)
{
    await using var cmd = new MySqlCommand(
        "INSERT INTO orders_binary (id, tenant_id, total) VALUES (@id, @tenant, @total)",
        conn);

    // Store the raw 16 bytes directly — no UUID_TO_BIN() needed when the
    // application already generates the value, provided byte order is handled
    // consistently (see below).
    cmd.Parameters.AddWithValue("@id", id.ToByteArray());
    cmd.Parameters.AddWithValue("@tenant", tenantId);
    cmd.Parameters.AddWithValue("@total", total);
    await cmd.ExecuteNonQueryAsync();
}

As with any direct byte-array insert into a BINARY(16) column, be consistent about byte order — .NET's Guid.ToByteArray() uses a different internal layout than MySQL's UUID_TO_BIN() for the first three fields. If interoperability with BIN_TO_UUID() in raw SQL is required, either convert on the .NET side before sending, or perform the conversion in SQL via UUID_TO_BIN() and pass the string instead.

Node.js

const { randomUUID } = require('crypto');

async function insertOrder(pool, tenantId, total) {
  const id = randomUUID();
  await pool.execute(
    'INSERT INTO orders_binary (id, tenant_id, total) VALUES (UUID_TO_BIN(?), ?, ?)',
    [id, tenantId, total]
  );
}

Passing the string form and letting UUID_TO_BIN() handle conversion server-side avoids any client-side byte-order bookkeeping entirely — this is the simplest correct pattern for Node.

Reading Values Back

SELECT BIN_TO_UUID(id) AS id, tenant_id, total
FROM orders_binary
WHERE tenant_id = 42;

// MySqlConnector returns the raw bytes for a BINARY(16) column;
// reconstruct a Guid explicitly rather than assuming byte order matches
var bytes = (byte[])reader["id"];
var id = new Guid(bytes); // only correct if byte order was written consistently

Comparison With Alternatives

The CHAR(32) row is worth calling out: some schemas strip hyphens to save 4 bytes while staying in text form. It's a real, if small, improvement over CHAR(36), but it still carries all the downsides of text storage (double the width of BINARY(16), no type-level structure) for a fraction of the benefit — if you're already willing to reformat the value, going to BINARY(16) costs the same application-layer effort and captures the full storage win.

Decision Guide

1. New schema, any non-trivial table size → BINARY(16). There's no scenario where CHAR(36) wins on storage or index efficiency; the only cost is needing UUID_TO_BIN()/BIN_TO_UUID() at the query boundary, which is a one-time application-layer concern, not a recurring one.
2. Existing schema on CHAR(36), table is small or low-traffic → Low priority; the absolute savings may not justify a migration.
3. Existing schema on CHAR(36), table has multiple secondary indexes and heavy insert volume → High priority; the clustering multiplier means this is exactly the case where the gap compounds fastest.
4. Need to debug/query the table directly in a SQL client frequently → BINARY(16) makes ad-hoc SELECT * output unreadable without wrapping every UUID column in BIN_TO_UUID(). Consider a view that does this conversion automatically for interactive use:

CREATE VIEW orders_readable AS
SELECT BIN_TO_UUID(id) AS id, tenant_id, total
FROM orders_binary;

Conclusion

BINARY(16) beats CHAR(36) on every storage and indexing metric in MySQL, and the gap is larger than a simple 36-vs-16-byte comparison suggests because InnoDB's clustered index design means every secondary index pays the primary key's width again. The only real cost is losing direct human readability in raw query output, which a wrapper view resolves cleanly. The one thing BINARY(16) does not fix on its own is insert locality for random (v4) UUIDs — that requires either the v1 swap_flag technique or a time-ordered UUID scheme like v7 layered on top of the storage type change.

---

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.