In MySQL’s InnoDB engine, a clustered index defines how the actual table data is physically stored, while non-clustered indexes store only column values and a pointer to the clustered index.

Clustered Index (InnoDB Primary Key)

1
InnoDB stores the entire row inside the B-tree of the primary key index.
2
There can be only one clustered index per table.
3
The physical order of rows on disk follows the primary key order.
4
If no PRIMARY KEY exists, InnoDB chooses the first UNIQUE NOT NULL index. If none exists, it creates a hidden 6-byte ROW_ID.
5
Clustered index lookups are fast when searching by primary key because the data is directly in the index.

Non-Clustered Index (Secondary Indexes)

1
All other indexes in InnoDB are non-clustered.
2
Does NOT store the full row; instead stores:
3
• Indexed column values
4
• Pointer to the clustered index (the primary key value)
5
Requires an extra lookup step (called back to the primary key lookup) to fetch the full row.
6
Multiple non-clustered indexes can exist on a table.

Key Differences

1
Clustered index stores full row data; non-clustered stores only index values + primary key pointer.
2
Clustered index defines physical data order; non-clustered does not.
3
Only one clustered index allowed; many non-clustered indexes allowed.
4
Non-clustered index lookups are slower due to an extra pointer lookup.

In summary, InnoDB’s primary key is the clustered index that organizes how data is stored, while all secondary indexes are non-clustered and require an additional lookup to retrieve full row data.

Difficulty: 6/10

Topics: clustered index, secondary index, InnoDB storage layout

Scenario Questions

0-2 years experience

1
Suppose you need to add a new column to a table and you want to speed up lookups by that column. How would you decide whether to create a primary key or a secondary index, and what difference does InnoDB’s clustered index make?
2
If you run EXPLAIN on a SELECT that uses a WHERE on a non‑primary column and you see 'Using index', what does that tell you about the underlying storage layout in InnoDB?

2-5 years experience

1
Your application is experiencing slow INSERTs after you added a secondary index on a high‑cardinality column. Walk me through why InnoDB’s clustered index might be contributing to this slowdown and what you could change.
2
During a performance review you notice a query that scans many rows even though there’s an index on the filter column. How would you investigate whether the index is clustered or non‑clustered and what steps would you take to fix it?

5-8 years experience

1
We need to design a sharded MySQL cluster for a high‑traffic e‑commerce site. How would the choice between using the primary key as the clustered index versus a surrogate key affect data distribution, hot‑spoting, and read/write latency?
2
Our team is planning to migrate a legacy MyISAM table with many composite indexes to InnoDB. What trade‑offs should we consider regarding clustered index selection, index size, and page splits at scale?

8+ years experience

1
At a company‑wide level we’re standardizing schema guidelines for all services. How would you formulate a policy around primary key design and clustered index usage in InnoDB to balance operational simplicity, backup/restore performance, and long‑term schema evolution?
2
Imagine you have multiple microservices each owning their own MySQL databases, and you need to implement cross‑service reporting that joins large tables. What architectural decisions around clustered vs secondary indexes, partitioning, and denormalization would you recommend to keep query latency low while preserving data integrity?

Follow-up Questions

❓How does the presence of the primary key in secondary index entries affect write amplification?
❓What happens to query performance if you change the primary key on a large table?
❓Can you describe a scenario where a non‑clustered index would be preferable despite InnoDB’s clustered nature?