What is the difference between a clustered and non-clustered index?
Why Interviewers Ask This
This question tests conceptual clarity. Interviewers want to hear a precise, confident definition before moving to more complex MySQL / SQL topics. It also reveals how well you can explain technical ideas to non-experts.
Answer
A clustered index determines the physical order in which data is stored on disk. The table data IS the index — the leaf nodes of the B-tree contain the actual row data. There can be only one clustered index per table (because data can only be sorted in one way). In MySQL InnoDB, the primary key is always the clustered index. If you define no primary key, InnoDB picks the first UNIQUE NOT NULL column; if none exists, it creates a hidden 6-byte row ID. Non-clustered index (secondary index) is a separate structure from the table data. Leaf nodes contain the indexed column values plus a pointer to the actual row data. In InnoDB, that pointer is the primary key value (not a physical row pointer) — so a secondary index lookup requires two lookups: first find the primary key in the secondary index, then look up the row data in the clustered (primary key) index. This is called a "double lookup" or "bookmark lookup." There can be many non-clustered indexes per table. MySQL terminology doesn't use "clustered/non-clustered" explicitly — InnoDB always uses the primary key as the clustered index, and all other indexes are secondary (non-clustered).
Pro Tip
If you're unsure about a detail, say so honestly and explain your reasoning. Interviewers respect candidates who can think through uncertainty rather than bluffing.