MySQL Tuning, Indexing & Concurrency
5-8 Years (Senior)
Medium
What is the difference between Clustered Index and Secondary Index in InnoDB?
Real-World Scenario / Incident Description
An interviewer asks why secondary index lookups perform a secondary traversal back to the clustered index (Bookmark Lookup).
240 views
•
Updated 1 month ago
Clustered vs Secondary Index
- Clustered Index: The physical table data rows are stored directly inside the leaf nodes of the Primary Key B+Tree.
- Secondary Index: Leaf nodes store only the indexed column value and the Primary Key value (not a physical row pointer).
- To retrieve non-indexed columns, MySQL must perform a Bookmark Lookup (reading secondary index -> extracting PK -> searching Clustered Index).
Related Concepts:
#Clustered Index
#Secondary Index
#InnoDB Internals
More MySQL Tuning, Indexing & Concurrency Interview Questions
How does the InnoDB B+Tree Index work internally, and why does Leftmost Prefix Rule dictate composite index column ordering?
Read →
Explain InnoDB Locking Modes: Record Locks, Gap Locks, and Next-Key Locks, and how they prevent Phantom Reads.
Read →
How do you optimize deep pagination (`SELECT * ... LIMIT 5000000, 20`) on large tables with 100M rows?
Read →
How do you interpret MySQL `EXPLAIN ANALYZE` output to diagnose `Using filesort` and `Using temporary`?
Read →
Explain MySQL Table Partitioning (Range, List, Hash) vs. Application-Level Sharding for 1TB+ databases.
Read →