Intermediate LevelSQL & MySQL
Compare Clustered Indexes vs Non-Clustered Indexes vs Covering Indexes.
❓ Interview Prompt: Compare Clustered Index (physical row ordering), Non-Clustered Index (secondary B-Tree), and Covering Index (zero table lookup).
Comparing B-Tree Index variants:
1
Clustered Index: Dictates the physical storage order of data rows on disk (In MySQL InnoDB, the Primary Key IS the clustered index). Only 1 clustered index per table!
2
Non-Clustered Index (Secondary Index): A separate B-Tree containing indexed key values and pointers (Primary Key values) to the clustered index rows.
3
Covering Index: A secondary index that contains ALL columns requested by a query. Allows the engine to return results directly from the index tree without executing a secondary clustered index lookup (Double Lookup)!
Code Implementation Example
sql
-- Creating a Covering Index satisfying: WHERE status = 'ACTIVE' SELECT id, email
CREATE INDEX idx_users_status_email ON users(status, email);
-- Executing Covering Index Query (EXPLAIN shows: Extra: Using index)
EXPLAIN SELECT id, email FROM users WHERE status = 'ACTIVE';💡Creating a Covering Index to eliminate secondary table row lookups.
🎯 Interview Key Takeaway
Design Covering Indexes to satisfy high-frequency queries directly from the index B-Tree without triggering secondary table lookups.
#SQL#Clustered Index#Covering Index#Indexes#Intermediate
Back to all SQL & MySQL Questions→