A clustered index is an index whose leaf level contains the table rows themselves, stored in the order of the index key. In databases, this means the clustered index does not point to the data; it is the data, organized as a B-tree. Because rows can be stored in only one order, a table can have at most one clustered index.
The clustered index is central to indexing in SQL Server and MySQL InnoDB, where it determines how a table is laid out on disk and how every other index locates rows. Choosing its key is one of the most consequential physical design decisions for a table.
How a clustered index stores data
A clustered index is a B-tree index in which the root and intermediate pages hold key values and pointers, while the leaf pages hold complete rows sorted by the clustering key. Leaf pages are linked to their neighbors, so the engine can walk through a key range in order without returning to the root.
Finding a single row takes one traversal from root to leaf, typically three or four page reads even for tables with hundreds of millions of rows, because each page holds hundreds of keys. Once the leaf is reached, the row is already there: no extra lookup to a separate table structure is needed.
A table without a clustered index is called a heap. Its rows sit in no particular order, and other indexes reference them through a physical row identifier that contains the file, page and slot number. Heaps can be fast for bulk loading but are weaker for range queries and suffer from forwarded records when updated rows no longer fit on their page.
Clustered and non-clustered indexes compared
The essential difference is what the leaf level contains. A clustered index stores the rows, while a non-clustered index stores index keys plus a row locator that leads back to the row. In a clustered table, that locator is the clustering key, which ties every secondary index to the clustered key design.
| Aspect | Clustered index | Non-clustered index |
|---|---|---|
| Leaf content | Full table rows | Key columns plus row locator |
| Number per table | One | Many, often dozens allowed |
| Physical order | Defines row order | Separate structure, own order |
| Range queries | Very efficient, sequential reads | Efficient only if covering |
| Extra lookup | None | Key lookup unless the index is covering |
| Storage overhead | No separate copy of data | Additional pages per index |
When a non-clustered index lacks columns the query needs, the engine performs a key lookup into the clustered index for every matching row. For a few rows this is cheap; for thousands it can be slower than a full scan, which is why covering indexes with included columns exist.
Clustered indexes in major database systems
Database engines implement clustering differently, and the same term does not mean the same thing everywhere. Knowing the engine’s behavior prevents wrong expectations, especially when moving between systems.
| Engine | Clustering behavior | Practical consequence |
|---|---|---|
| SQL Server | Optional; the primary key is clustered by default | Heaps are possible but usually avoided |
| MySQL InnoDB | Always clustered on the primary key | Without a key, a hidden 6-byte row ID is used |
| PostgreSQL | No maintained clustered index; tables are heaps | CLUSTER reorders once, order decays with writes |
| Oracle | Heap by default, optional index-organized tables | IOT behaves like a clustered index |
| SQLite | Rowid tables clustered on rowid | WITHOUT ROWID clusters on the primary key |
In InnoDB, if no primary key is declared, the engine uses the first unique index on non-null columns, and only then falls back to the hidden row ID. Because secondary indexes store primary key values, a wide primary key in InnoDB makes every secondary index larger.
Choosing a good clustering key
A good clustering key is narrow, unique, static and ever-increasing. Narrow keys keep all secondary indexes small, uniqueness avoids hidden uniquifiers, static values prevent costly row moves and increasing values append new rows at the end instead of splitting pages.
| Key type | Advantages | Drawbacks |
|---|---|---|
| Integer identity or sequence | 4 or 8 bytes, sequential inserts | No business meaning, hot last page under heavy concurrency |
| Random GUID | Globally unique, generated anywhere | 16 bytes, random inserts cause page splits |
| Time-ordered UUID | Unique and roughly sequential | Still 16 bytes wide |
| Natural business key | Meaningful, may avoid a join | Often wide and may change |
| Composite date and ID | Fast range scans by time | Wider secondary indexes |
Sometimes the best choice is driven by queries rather than inserts. A table of measurements read almost always by time range may be clustered on timestamp plus an identifier, trading some insert cost for very efficient sequential range reads.
Pitfalls and maintenance
Most clustered index problems come from a poorly chosen key. Random keys cause page splits, in which half of a full page moves to a new page, leaving both half empty and increasing index fragmentation. Updating a clustering key physically moves the row and forces updates in every non-clustered index.
- Fragmentation: frequent splits scatter pages; rebuilding or a lower fill factor helps on random-key tables.
- Last-page contention: many sessions inserting sequential keys compete for the same page latch.
- Assuming result order: a clustered index does not guarantee result order without
ORDER BY. - Clustering by default: accepting the primary key as clustering key without checking query patterns.
The query planner shows how the clustered index is used. A clustered index seek means efficient targeted access, while a clustered index scan reads the whole table and often signals a missing non-clustered index or a non-selective predicate.
In everyday language
A clustered index works like a phone book sorted by last name. The entries themselves are in alphabetical order, so finding all people named Smith means opening the right page and reading the following lines. There is no separate list telling you where Smith is.
Because a book can only be sorted one way, a table can have only one clustered index. Other indexes are like the index at the back of a textbook: separate lists that point to the right page.
Seen in the real world
An online store keeps an orders table with about 200 million rows. It was originally clustered on a random GUID generated by the application, and nightly reports filtering orders by date were slow, while insert-heavy sales events produced heavy page splitting and fragmentation above 90 percent.
The database team adds a sequential bigint order ID as the new clustering key, keeps the GUID as a unique non-clustered index for external references and adds a non-clustered index on the order date with included columns used by the reports.
After the change, inserts append to the end of the table, fragmentation stays low and secondary indexes shrink because their row locator is 8 bytes instead of 16. The reports switch from full scans to index seeks with range reads, and their run time drops substantially.