Understanding MySQL Clustered vs Non‑Clustered Indexes: Key Differences Explained

What Is an Index Structure?

An index in a database works like a book's table of contents, speeding up data lookup by avoiding full table scans.

In MySQL, an index (also called a key) is a data structure that lets the storage engine quickly locate records without scanning the entire table.

What Is a Clustered Index?

A clustered (or primary) index stores rows in the same physical order as the indexed column, typically the primary key.

Understanding clustered indexes requires understanding primary keys. If a table is created without an explicit primary key, InnoDB chooses one based on the following rules:

If a non‑null unique index exists, the first such index becomes the primary key.

Otherwise, InnoDB creates a hidden 6‑byte pointer as the primary key.

Assuming a 16 KB page can hold three rows, the storage layout looks like:

To locate a specific row, the engine would need to scan pages sequentially, which is inefficient.

By creating a directory that maps primary keys to page numbers, a binary search can quickly find the target page.

The primary key thus acts like a book's index, improving data lookup efficiency.

However, storing this mapping in a contiguous array raises issues as data grows:

The required continuous space becomes impractically large.

When a page is fully deleted, subsequent directory entries must shift, incurring high cost.

One solution is to store directory entries alongside user data, forming a structure similar to a B‑tree:

Note: Each directory entry contains two columns – the primary key and the page number.

When many entries exist, multiple levels of directories are built, resulting in a B‑tree where leaf nodes store all column values.

For an InnoDB integer column with a fan‑out of about 1,200, a tree height of 4 can store 1,200³ ≈ 1.7 billion values. The root page stays in memory, so a table with a billion rows typically requires only three disk accesses to locate a value.

Clustered indexes are unique; a table can have only one clustered index because data and index share the same storage.

What Is a Non‑Clustered Index?

Non‑clustered (secondary) indexes are built on top of the clustered index. Their leaf nodes store the primary key value rather than the physical row location, requiring a second lookup to retrieve the full row.

Thus, data resides in one place while the index resides elsewhere, with pointers linking to the data.

Key Differences Between Clustered and Non‑Clustered Indexes

Clustered indexes locate the data in a single step; non‑clustered indexes first retrieve the primary key, then use the clustered index to fetch the row.

A table can have only one clustered index but multiple non‑clustered indexes.

Clustered indexes generally offer higher query performance, but frequent updates to the indexed column can degrade write performance due to physical data reordering.

Non‑clustered indexes are ordinary secondary indexes that do not affect the table's physical storage order.

Clustered indexes store records contiguously on disk; non‑clustered indexes are logically ordered and may be scattered physically.