Understanding Conflict Handling and Resolution Rules

InnoDB stores data very differently from many other engines. Understanding how clustered indexes work, and how your primary key choice shapes physical layout, is one of the highest-value skills for MySQL engineers.

This article walks through how InnoDB organises data on disk, how secondary indexes depend on the primary key, and step-by-step guidance to design stable, efficient primary keys.

1. What is a clustered index in InnoDB?

InnoDB is a clustered storage engine. That means the table data is the primary key (PK) index. The leaf pages of the PK B-tree contain the full row.

Logical view (simplified)

┌──────────────────────────────────────────┐
│  PRIMARY KEY (clustered)                │
│  B-tree on <pk_columns>                 │
│                                          │
│  Leaf pages:                            │
│   [pk, col2, col3, ...]  -- full row    │
└──────────────────────────────────────────┘

┌──────────────────────────────────────────┐
│  Secondary index on <idx_col>           │
│  B-tree on <idx_col, pk>                │
│                                          │
│  Leaf pages:                            │
│   [idx_col, pk] -- pointer to PK page   │
└──────────────────────────────────────────┘

Key points:

Exactly one clustered index per table: the PRIMARY KEY (or a hidden one if none defined).
Rows are stored physically ordered by the PK.
Every secondary index stores the PK as the row pointer.

This has direct implications for performance, page splits, and disk usage.

2. Why primary key choice matters

Because the PK defines the physical order, a poor choice can cause:

Frequent page splits and fragmentation.
Larger secondary indexes (wider PK copied into each one).
Hot page contention on inserts.
More random I/O for range scans.

A good PK is typically:

Stable (never updated).
Unique and minimal in size.
Monotonically increasing for insert-heavy workloads.
Aligned with your most common access patterns when possible.

3. How InnoDB organises pages and rows

InnoDB stores data in 16 KB pages by default. Each page belongs to a B-tree for an index. For the clustered index:

Internal pages hold key ranges and child pointers.
Leaf pages hold full rows, sorted by PK.

Clustered index B-tree (simplified)

        ┌───────────────┐
        │   Root page   │
        └──────┬────────┘
      ┌────────┴───────────┐
      │                    │
┌───────────────┐   ┌───────────────┐
│ Internal page │   │ Internal page │
└──────┬────────┘   └──────┬────────┘
   ┌───┴───┐              ┌─┴───────┐
┌───────────────┐   ┌───────────────┐
│  Leaf page A  │   │  Leaf page B  │
│ [full rows]   │   │ [full rows]   │
└───────────────┘   └───────────────┘

When you insert a new row, InnoDB finds the target leaf page by PK and inserts the row in order. If the page is full, it splits into two pages, which can trigger more I/O and fragmentation.

4. Common primary key patterns and trade-offs

4.1 AUTO_INCREMENT integer primary key

This is the most common and usually the safest default:

CREATE TABLE orders (
  id           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  customer_id  BIGINT UNSIGNED NOT NULL,
  created_at   DATETIME NOT NULL,
  status       TINYINT NOT NULL,
  amount_cents BIGINT NOT NULL,
  PRIMARY KEY (id),
  KEY idx_customer_created (customer_id, created_at)
) ENGINE=InnoDB;

Pros:

Monotonically increasing inserts: new rows go to the right-most leaf page, minimising page splits.
Compact (8 bytes) and stable.
Simplifies foreign keys and joins.

Cons:

PK not meaningful to the business domain.
Can become a hotspot on very high insert rates (mitigated with proper configuration and batching).

4.2 Natural primary key (e.g. email)

Using a business attribute as PK:

CREATE TABLE users (
  email       VARCHAR(255) NOT NULL,
  name        VARCHAR(255) NOT NULL,
  created_at  DATETIME NOT NULL,
  PRIMARY KEY (email)
) ENGINE=InnoDB;

Issues:

Wide PK: every secondary index stores up to 255 bytes instead of 8.
Potential updates: changing an email forces row relocation and secondary index updates.
Insert order may be random, causing more page splits and fragmentation.

Pattern to prefer:

CREATE TABLE users (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  email       VARCHAR(255) NOT NULL,
  name        VARCHAR(255) NOT NULL,
  created_at  DATETIME NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uk_users_email (email)
) ENGINE=InnoDB;

The natural key is still enforced as UNIQUE, but the clustered PK stays small and stable.

4.3 UUID primary keys

UUIDs are attractive for distributed systems, but naive use is expensive in InnoDB.

CREATE TABLE events (
  id          CHAR(36) NOT NULL,  -- UUID string
  user_id     BIGINT UNSIGNED NOT NULL,
  created_at  DATETIME NOT NULL,
  payload     JSON NOT NULL,
  PRIMARY KEY (id),
  KEY idx_user_created (user_id, created_at)
) ENGINE=InnoDB;

Problems with random UUIDs as PK:

Insert order is random: rows spread across many pages, causing constant page splits.
PK is wide (36 bytes string or 16 bytes binary) and copied into every secondary index.
Cache efficiency suffers due to poor locality.

Better patterns:

Use AUTO_INCREMENT PK plus a secondary UNIQUE UUID column.
Or use ordered/binary UUID formats that improve locality, while still being cautious about width.

5. Step-by-step: designing a primary key for a new table

Step 1: Identify access patterns

List your top queries:

Lookups by ID?
Range scans by time?
Filters by user or tenant?

Example requirements for a metrics table:

Insert time-series data per device.
Query ranges by device_id and timestamp.
Occasional lookup by internal numeric id.

Step 2: Decide if you need a surrogate key

For most OLTP tables, use a surrogate integer PK and secondary indexes for queries.

CREATE TABLE metrics (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  device_id   BIGINT UNSIGNED NOT NULL,
  ts          DATETIME NOT NULL,
  value       DOUBLE NOT NULL,
  PRIMARY KEY (id),
  KEY idx_device_ts (device_id, ts)
) ENGINE=InnoDB;

If your main access pattern is always device_id, ts range scans and you rarely use id, you might consider a composite PK instead.

Step 3: Consider composite primary keys

Composite PKs can align physical layout with queries:

CREATE TABLE metrics (
  device_id   BIGINT UNSIGNED NOT NULL,
  ts          DATETIME NOT NULL,
  value       DOUBLE NOT NULL,
  PRIMARY KEY (device_id, ts)
) ENGINE=InnoDB;

Benefits:

Data for each device is clustered together in time order.
Range scans by device_id, ts are very efficient.
No extra secondary index needed for that pattern.

Costs:

Every secondary index must include both device_id and ts as the row pointer.
PK is wider than a single BIGINT.
Foreign keys referring to this table need both columns.

Rule of thumb: composite PKs are excellent for narrow, stable columns that match your dominant query pattern. Avoid using wide or frequently updated columns in the PK.

6. Hidden primary keys and why to avoid them

If you do not define a PRIMARY KEY or a UNIQUE NOT NULL index, InnoDB will create a hidden 6-byte row id and use that as the clustered index.

Problems:

You cannot see or use this hidden id in queries.
Secondary indexes still store this hidden value, but you cannot reference it.
Schema evolution and troubleshooting become harder.

Always define an explicit primary key. It makes behaviour predictable and index design clearer.

7. Impact on secondary indexes

Every secondary index in InnoDB is logically:

INDEX <name> (<index_columns>, <primary_key_columns>)

Even if you do not see the PK columns in the definition, they are appended internally. This matters because:

Wider PK = wider secondary indexes = more disk and memory.
Secondary index lookups require a double read: first the secondary index, then the PK page.
Covering indexes may be easier to build if the PK is already part of the needed columns.

When designing indexes, keep PK width in mind. For high-cardinality, wide natural keys, prefer a surrogate integer PK to keep secondary indexes compact.

8. Best practices summary

Always define a primary key. Do not rely on the hidden row id.
Prefer integer AUTO_INCREMENT PKs for general OLTP workloads.
Keep the PK stable: avoid updates to PK columns.
Keep the PK narrow: smaller data type, fewer columns.
Avoid random PKs (e.g. naive UUIDs) for write-heavy tables.
Use composite PKs when they match a dominant access pattern and remain narrow.
Enforce business uniqueness with separate UNIQUE indexes, not necessarily as PK.
Review secondary indexes regularly; remember they all include the PK.

9. Practical checklist before creating a table

List top 3 query patterns and filters for the table.
Decide if a surrogate integer PK is acceptable (usually yes).
If not, design a composite PK that is:
- Narrow (few, small columns).
- Stable (no updates).
- Aligned with main range queries.
Define UNIQUE constraints for natural keys separately.
Estimate PK width and its effect on secondary indexes.
Consider insert pattern: sequential vs random; adjust PK choice accordingly.
Test with realistic data volume and query mix before production.

This article offers general technical guidance. Validate all configurations in a safe environment before applying them to production.

Choosing the right primary key in InnoDB is not just a modelling decision; it defines how your data is physically laid out and how every index behaves. By keeping keys narrow, stable, and aligned with your access patterns, you gain better performance, simpler operations, and more predictable scaling. Treat PK design as a first-class part of schema design, not an afterthought.