Clustered vs Non-Clustered
A clustered index determines the physical order of rows on disk - the table IS the index. A non-clustered index is a separate lookup structure pointer to heap rows. Every table has at most one clustered index. Understanding this affects query planning for range scans.
Clustered vs Non-Clustered
- Clustered: rows physically stored in index order - fast range scans
- One clustered index per table (usually the PRIMARY KEY)
- Non-clustered: separate B-tree pointing to heap rows - requires extra row lookup
- PostgreSQL: all indexes are non-clustered by default (heap storage)
- PostgreSQL CLUSTER command - reorganizes heap to match index order (one-time, not maintained)
- MySQL InnoDB: PRIMARY KEY IS the clustered index (physical storage order)
- SQL Server: explicitly CREATE CLUSTERED INDEX vs NONCLUSTERED
Clustered/Non-Clustered Examples
-- MySQL InnoDB: PRIMARY KEY is automatically clustered
-- Rows are stored in id order on disk
CREATE TABLE orders (
id INT AUTO_INCREMENT PRIMARY KEY, -- clustered index
user_id INT,
status VARCHAR(50),
INDEX idx_status (status) -- non-clustered
);
-- Range scan on PK (clustered - very fast, contiguous disk pages)
SELECT * FROM orders WHERE id BETWEEN 1000 AND 2000;
-- Range scan on status (non-clustered - random I/O to heap)
SELECT * FROM orders WHERE status = 'pending';
-- PostgreSQL CLUSTER (non-persistent, one-time reorder)
CLUSTER orders USING idx_orders_created;
-- Reorders the table heap to match the index
-- NOT maintained on insert/update - must re-cluster periodically
-- Covering index (avoids heap lookup entirely)
CREATE INDEX idx_orders_user_covering ON orders(user_id)
INCLUDE (status, total_amount);
-- For: SELECT status, total_amount FROM orders WHERE user_id = 1
-- All data in the index → no heap access neededQuick Quiz
Tip
Tip
Practice Clustered vs NonClustered in small, isolated examples before integrating into larger projects. Breaking concepts into small experiments builds genuine understanding faster than reading alone.
Relationships define how tables connect through foreign keys
Common Mistake
Warning
A common mistake with Clustered vs NonClustered is skipping edge case testing - empty inputs, null values, and unexpected data types. Always validate boundary conditions to write robust, production-ready sql code.
Practice Task
Note
Practice Task - (1) Write a working example of Clustered vs NonClustered from scratch without looking at notes. (2) Modify it to handle an edge case (empty input, null value, or error state). (3) Share your solution in the Priygop community for feedback.
Key Takeaways
- A clustered index determines the physical order of rows on disk - the table IS the index.
- Clustered: rows physically stored in index order - fast range scans
- One clustered index per table (usually the PRIMARY KEY)
- Non-clustered: separate B-tree pointing to heap rows - requires extra row lookup