📘 What is a Clustered Index?
A clustered index is a special type of index that determines the physical order of rows in a table.
In other words: the table’s data is stored in the order of the clustered index.
🔑 Key Characteristics:
- ✅ Only one per table
Because the data can be physically ordered only one way. - ✅ Data is stored with the index
The clustered index contains the actual table data — there is no separate storage. - ✅ Primary Key = Clustered Index (by default)
In many databases (e.g., MySQL InnoDB, SQL Server), the primary key automatically becomes the clustered index unless you specify otherwise.
📊 Example:
CREATE TABLE Users (
id INT PRIMARY KEY,
name VARCHAR(100),
email VARCHAR(100)
);
id is the clustered index
Rows in the Users table are physically stored sorted by id
So:
SELECT * FROM Users WHERE id = 5;
➡️ The database can go directly to the row, without any extra lookup.
🧠 Think of it Like:
A clustered index is like a phonebook where the pages are sorted by last name.
You flip directly to the page you need — no extra lookup required.
🔄 Clustered vs Non-Clustered:
| Feature | Clustered Index | Non-Clustered Index |
|---|---|---|
| Data in index? | ✅ Yes, contains table data | ❌ No, points to data |
| Affects row order? | ✅ Yes | ❌ No |
| Number per table | Only 1 | Many allowed |
| Lookup speed | 🔥 Very fast for indexed column | Good, but needs extra lookup |
🛠 When to Use a Clustered Index:
Use a clustered index on:
- Columns frequently used in
WHERE,JOIN,ORDER BY - Columns with high selectivity (many unique values)
- Primary keys (almost always clustered)