An index is a table's own table of contents
A textbook's index lets you jump straight to a page instead of reading front to back. A database index does the same for rows — this lesson names the specific kinds and the one crucial choice: whether the table's own rows are physically sorted to match it.
After this lesson
You should be able to
- Explain what an index trades away in exchange for faster search.
- Distinguish a clustered (primary) index from a non-clustered (secondary) index.
An index is a separate, smaller structure that points into the real data
An index is an auxiliary structure built on one or more columns of a table, storing sorted key values alongside pointers to where the matching rows actually live. Searching the index (which is small and sorted) is much faster than scanning the whole table, and the pointer then jumps directly to the right block — this is the mechanism behind SQL's WHERE clause avoiding a full scan whenever a suitable index exists.
This speed is not free: every index must itself be updated whenever the underlying table's rows are inserted, updated, or deleted, adding write overhead in exchange for read speed — the classic trade-off behind why a table is not simply indexed on every column by default.
Clustered: the table itself is sorted to match the index
A clustered (or primary) index requires the table's actual rows to be physically stored in the same sorted order as the index key — this is exactly the sorted file organization from the previous lesson, with an index built directly on top of that physical order. Because a table can only be physically sorted one way at a time, a table can have at most one clustered index.
A non-clustered (or secondary) index makes no such demand — the table's rows can stay in any physical order (even a heap file), while the index separately maintains sorted keys and pointers to wherever each row actually sits. Because this does not constrain the table's physical layout, a table can have several secondary indexes, one for each column commonly searched on.
Try it yourself
A Student table is frequently searched by both roll_number (its primary key, used in almost every query) and by phone_number (used occasionally, for a specific lookup feature). Which column should get the clustered index, and why?
Need a hint?
A table can have only one clustered index, so it should go to the column searched far more often and more centrally to the table's typical use.
Check the worked solution
roll_number should get the clustered index, since it is used in almost every query and is the table's primary key — the physical row order that benefits searches the most should match the column searched most often. phone_number, searched occasionally, is a better fit for a secondary index, which does not force a particular physical row order.
Quick check
Why can a table have several secondary indexes but at most one clustered index?
Why this lesson exists
Syllabus mapping
Cluster Indexes, Primary and Secondary Indexes
Maps to course outcome CO4.