Clustered Index
A clustered index defines the physical row order of the table itself, so range scans on its key are sequential, but every secondary index must route through it.
Definition
A clustered index is the index whose leaf pages are the table data: rows are stored in key order. One table has one clustered order — the primary key in InnoDB, or explicitly chosen in SQL Server. Secondary indexes store the clustered key as their row pointer, so lookups through them cost an extra tree descent into the clustered index.
Context and scope
Explains why InnoDB secondary index lookups are two-phase, why random primary keys scatter inserts, and why range queries on the clustering key are fastest.
Common misconceptions
- Clustered indexes make all queries faster — they accelerate range scans on the clustering key and can penalize everything routed through secondary indexes
- Every index is clustered — tables have one physical order; additional indexes are secondary structures referencing it
- A UUID primary key is just as good — random keys spread inserts across the whole tree, causing page splits and buffer churn on write-heavy tables
Anti-patterns
- Choosing a random UUID as the InnoDB primary key on a high-insert table and suffering fragmented page splits
- Writing wide secondary lookups without covering columns and paying clustered-index double descent per row
- Rebuilding a clustered index without considering that it physically rewrites the entire table
Related concepts
Related examples
Authoritative source
https://dev.mysql.com/doc/refman/8.0/en/innodb-index-types.html
What it is
One index gets to be the table: its leaves are the rows, and the table physically follows that key order. Range scans on the clustering key become sequential reads of adjacent pages. Everything else — secondary indexes — points into that structure.
How it works
In InnoDB the primary key is the clustered index, and secondary index leaves store the primary key rather than a row offset. A secondary hit therefore does a second lookup into the clustered tree unless the secondary index covers the query. SQL Server lets you pick any unique key or a non-key clustering column.
When to care
Insert-heavy tables want monotonically increasing or time-ordered keys to append rather than split. Range-filtered queries want the clustering key aligned with the filter. Secondary-heavy read models want covering secondary indexes to avoid the double descent.
Pitfalls
Random primary keys scatter inserts across pages, degrading write throughput and cache efficiency at scale. Very wide primary keys bloat every secondary index. Reorganizing the clustered order rewrites the table — plan maintenance windows around it.