Clustered vs Non-Clustered Index in MySQL (InnoDB)

In InnoDB, the table is the clustered index: rows are physically stored in primary-key order. Every other index is secondary and points back to the primary key. This one design fact explains a surprising amount of MySQL performance behaviour.

Key Points

• Clustered index = the primary key; leaf nodes contain the full row
• Secondary (non-clustered) indexes store indexed columns + the primary key value
• Secondary lookups do two steps: find PK in secondary index → find row in clustered index
• Big primary keys bloat every secondary index — prefer compact INTs
• Range scans on the primary key are extremely fast (rows are adjacent)
• Covering indexes skip the second lookup entirely — huge win for analytics