What is the difference between a clustered and a non-clustered index?

Question

Accepted Answer

53
 What is the difference between a clustered and a non-clustered index?
 Level: Advanced — A core InnoDB concept that separates junior from senior candidates.

 The difference is about where the actual row data lives relative to the index.

 
 Clustered Index: Determines the physical order of rows in the table. The index IS the table data — leaf nodes contain the actual rows. There can be only one per table (usually the PRIMARY KEY).
 Non-Clustered (Secondary) Index: A separate structure that stores the indexed column(s) plus a pointer (the primary key value) back to the actual row. A table can have many of these.
 

 
 Key InnoDB behaviour: A secondary index lookup often requires a second step — it finds the primary key, then uses the clustered index to fetch the full row. This extra hop is called a "bookmark lookup" or double lookup.
 

 Simple Analogy
 
 
 
 Dictionary vs Textbook Index
 A clustered index is like a dictionary — the content itself is sorted alphabetically. A non-clustered index is like a textbook's back-of-book index — a separate list that points you to the page number where the content actually lives.
 
 

 Side-by-Side Comparison
 
 
 
 
 Feature
 Clustered
 Non-Clustered
 
 
 
 
 Stores actual data?
 Yes (in leaf nodes)
 No (stores pointers)
 
 
 Physical row order
 Defines it
 Independent of it
 
 
 Number per table
 One
 Many
 
 
 Lookup speed
 Faster (direct)
 Slightly slower (extra hop)
 
 
 Default on
 PRIMARY KEY
 Other indexes
 
 
 
 

 Quick Example
CREATE TABLE employees (
 emp_id INT PRIMARY KEY, -- CLUSTERED index (row data stored here)
 email VARCHAR(100),
 last_name VARCHAR(50)
);

-- NON-CLUSTERED (secondary) index
CREATE INDEX idx_lastname ON employees(last_name);

-- This uses the secondary index to find the emp_id,
-- then the clustered index to fetch the full row (double lookup)
SELECT * FROM employees WHERE last_name = 'Ansari';

-- A "covering index" avoids the second lookup by

Feature	Clustered	Non-Clustered
Stores actual data?	Yes (in leaf nodes)	No (stores pointers)
Physical row order	Defines it	Independent of it
Number per table	One	Many
Lookup speed	Faster (direct)	Slightly slower (extra hop)
Default on	PRIMARY KEY	Other indexes

What is the difference between a clustered and a non-clustered index?

Answer with Explanation

What is the difference between a clustered and a non-clustered index?

Simple Analogy

Dictionary vs Textbook Index

Side-by-Side Comparison

Quick Example