indexing • Advanced

Clustered vs Secondary Indexes (Heap Tables vs Index-Organized)

In MySQL InnoDB, the Clustered Index (Primary Key) stores the entire row payload directly inside leaf nodes. Secondary indexes store the Primary Key value, requiring a secondary lookup (Bookmark Lookup).

Intuitive Mental Model

The Physical Book vs Index: The Clustered Index is the book chapters printed in page order. Secondary indexes are the alphabetical index at the back pointing to page numbers.

SQL DDL / DML QueryPostgreSQL / MySQL

-- MySQL InnoDB (Clustered on Primary Key):
-- PK Leaf = [id, name, email, created_at, ...]
-- Secondary Index idx_email Leaf = [email, id] (Requires PK lookup!)

-- PostgreSQL (Heap Table Architecture):
-- All indexes (PK and Secondary) point to (Block#, Offset#) tuple pointers in the Heap file.

Key Architectural Takeaways

•In InnoDB, every table has exactly ONE Clustered Index (defaults to Primary Key).
•Covering Indexes: If a query selects only columns present in the secondary index, it avoids the secondary clustered index lookup (Index-Only Scan).
•Keep Primary Keys compact (e.g. BIGINT over UUIDv4) in InnoDB to minimize the size of all secondary index leaf pages.

Common Production Mistake

Using random UUIDv4 as clustered primary keys in MySQL InnoDB, causing frequent B-Tree leaf page splits and severe write degradation.