How do clustered and non-clustered indexes differ in MySQL’s InnoDB engine?

Question

Accepted Answer

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. InnoDB stores the entire row **inside the B-tree of the primary key index**. There can be **only one** clustered index per table. The physical order of rows on disk follows the primary key order. If no PRIMARY KEY exists, InnoDB chooses the first UNIQUE NOT NULL index. If none exists, it creates a hidden 6-byte ROW_ID. Clustered index lookups are fast when searching by primary key because the data is directly in the index. All other indexes in InnoDB are non-clustered. Does NOT store the full row; instead stores:   • Indexed column values   • Pointer to the clustered index (the primary key value) Requires an extra lookup step (called **back to the primary key lookup**) to fetch the full row. Multiple non-clustered indexes can exist on a table. Clustered index stores full row data; non-clustered stores only index values + primary key pointer. Clustered index defines physical data order; non-clustered does not. Only one clustered index allowed; many non-clustered indexes allowed. 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.

How do clustered and non-clustered indexes differ in MySQL’s InnoDB engine?

Clustered vs Non-Clustered Indexes in MySQL InnoDB

How do clustered and non-clustered indexes differ in MySQL’s InnoDB engine?

Clustered vs Non-Clustered Indexes in MySQL InnoDB

Scenario Questions

Follow-up Questions