Part 132: Clustered vs Non-Clustered Indexes

The physical organization of table rows on disk.

1. Clustered Index (Primary Index)

• The table IS the index. The leaf nodes of a clustered index contain the actual table data rows.
• A table can have only ONE clustered index because physical data on disk can only be sorted in one order.
• In MySQL InnoDB, the Primary Key is automatically the clustered index.

2. Non-Clustered Index (Secondary Index)

• A separate B-Tree structure containing only the indexed column(s) plus a pointer back to the clustered index key.
• A table can have many secondary indexes.

Key Takeaways

• Choose your clustered index key carefully (monotonically increasing IDs avoid page fragmentation).

In Part 133, we explore Composite Indexes and the Leftmost Prefix Rule!