Your MySQL Primary Key Travels Into the Other Indexes

Article · Through the layers

A table has a long public identifier. Using it as the primary key feels tidy: one identifier in the URL, in Rust and in the database. Later the table acquires indexes for status, creation time and customer. The identifier choice is still there, inside those indexes too.

In InnoDB, a secondary index entry carries the primary key columns. That gives the engine a way to reach the full row through its clustered index. It also means a wide primary key is a repeated cost across secondary indexes. The InnoDB index documentation explains this layout. If the table has several secondary indexes, count that repeated key before choosing the identifier type.

This article compares two deliberately small tables from Rust. One has a BIGINT primary key. The other has a 36-character ASCII primary key. Both have a status column and an index on status. The text values look like UUIDs, but the fixture generates them predictably. It is not testing a UUID generator or random insertion behavior.

Ask for a column the index already carries

The fixture inserts one hundred rows into each table, with the same status distribution. Half the rows have status one. The query asks for the primary key of those rows through the status index.

async fn inspect(c: &mut sqlx::MySqlConnection, table: &str) -> Result<()> {
    let (plan_sql, count_sql) = match table {
        "b_pk_small" => (
            "EXPLAIN SELECT id FROM b_pk_small FORCE INDEX (by_status) WHERE status = ?",
            "SELECT COUNT(*) FROM b_pk_small WHERE status = ?",
        ),
        "b_pk_wide" => (
            "EXPLAIN SELECT id FROM b_pk_wide FORCE INDEX (by_status) WHERE status = ?",
            "SELECT COUNT(*) FROM b_pk_wide WHERE status = ?",
        ),
        _ => return Err("unknown fixture table".into()),
    };
    for row in sqlx::query(plan_sql).bind(1_i32).fetch_all(&mut *c).await? {
        let key: Option<String> = row.try_get("key")?;
        let extra: Option<String> = row.try_get("Extra")?;
        println!("{table}: key={key:?}, extra={extra:?}");
    }
    let count: i64 = sqlx::query_scalar(count_sql)
        .bind(1_i32)
        .fetch_one(&mut *c)
        .await?;
    assert_eq!(count, 50);
    Ok(())
}

The source is mysql_primary_key.rs, pinned to MySQL 8.4.13 and SQLx 0.9.0. The helper selects between two fixed SQL statements. SQL parameters represent values, not table identifiers. There is no reason to construct SQL from a web request to inspect these two known tables.

Notice that the index definition names only status. It does not explicitly name the primary key. Yet the query can get the identifier from an index entry that includes the primary key. The plan output is there to inspect the chosen access and its extra information. MySQL documents the relevant fields in its EXPLAIN output reference.

The fixture uses FORCE INDEX to make the teaching comparison explicit. With only one hundred rows, an optimizer can reasonably choose a table scan. Forcing an index here helps examine its contents; it is not advice to force that index in a service. A production hint needs a separate reason and can become stale as data changes.

A hundred rows will not show the storage saving

The assertions require 50 matching rows in each table. The binary prints ordinary EXPLAIN output and reported index allocation. EXPLAIN describes the estimated access path here; it is not an executed-query profile. No storage saving or speedup is asserted.

Small tables are particularly bad at answering storage questions from allocation totals. Pages have minimum allocations, statistics can be approximate, and a few rows do not fill those pages like a large table. Equal reported sizes in this fixture would not mean a 36-character key and an eight-byte integer have identical storage costs. Different sizes would not establish the saving for your application either.

For a storage comparison, build both designs with the same representative records and secondary indexes. Keep insert order and identifier generation recorded. Measure all indexes after loading and updating the data, then compare public-ID lookups and writes too. This ordered hundred-row input does not reproduce the page activity of randomly generated identifiers.

A smaller internal key adds another index

One alternative is a compact internal primary key plus a unique public identifier. That can reduce the key copied into each secondary index. But the public identifier still needs its own unique index if requests look up rows by it. Those lookups may need a secondary-index search followed by a clustered-row lookup.

Count that extra index before celebrating a smaller key. For a table with few secondary indexes and heavy lookups by public identifier, the trade may be different from a table with many indexes and internal joins. The public identifier's generation, uniqueness and information exposure are product concerns too. A numeric internal key should not automatically become a public URL just because it exists.

Binary UUID storage is another distinct design. Sixteen bytes and a 36-character text representation are different widths. Converting the representation can help without changing the logical identifier, but now every boundary needs a consistent encoding. Mixing byte order conventions in two services creates a data problem that an index size comparison cannot diagnose.

Choose with the access paths in view

Start by listing the indexes the table actually needs. For each one, ask what key must be carried to identify the row. Then list the queries that retrieve a row through the public identifier, join from another table or return only identifiers. This turns “which ID is best?” into a few concrete access paths.

The related stock reservation example has a tiny numeric stock key and a separate unique order key. Those keys have different jobs. Giving every table the same identifier policy would hide that difference.

Changing a primary key on an existing table is also more than changing a Rust field. Foreign keys, secondary indexes, migrations and external references need review. Before scheduling a migration, rehearse it on representative data and check the operational cost. A compact index layout is useful only if the change can be introduced safely.

For a new table, keep a short note beside the schema: the expected row count, secondary indexes, public lookup path and reason for the chosen primary key. Six months later, that note can explain why a key exists more clearly than a benchmark screenshot from a hundred-row toy table.