Part 1 of this series covered joins. This part covers indexing - specifically the honest version, not just "add an index and queries get faster." That advice is right about half the time. The other half, it's either the wrong fix for the actual problem, or it makes something else worse. This post covers what an index actually is, the different kinds, and the real tradeoffs each one carries.
A database table without an index answers a query by scanning every single row, in order, checking each one against the filter - a full table scan. An index is a separate data structure that lets the database jump directly to relevant rows instead, the same way a book's index at the back lets a reader jump to page 340 instead of reading every page until they find the right topic.
Clustered Index: The Physical Order of the Table Itself
A clustered index determines the actual physical order rows are stored in on disk. It's not a separate structure sitting alongside the table - the table itself is the clustered index, sorted by whatever column that index is built on.
Think of a physical dictionary. The words themselves are the data, and they're physically printed in alphabetical order - there's no separate "index of words" at the back, because the entire book already is that index. A clustered index works the same way: the table's actual storage order is the index.
-- A table with a clustered index on Id means rows
-- are physically stored in Id order
CREATE TABLE Posts (
Id INT PRIMARY KEY CLUSTERED, -- defines the
-- physical order
Title NVARCHAR(200),
PublishedDate DATETIME
);
-- Because rows are physically ordered by Id, this
-- query is fast - it can jump straight to where
-- Id = 500 would physically be
SELECT * FROM Posts WHERE Id = 500;
-- A range query benefits enormously too - rows 100
-- through 200 are physically adjacent on disk
SELECT * FROM Posts WHERE Id BETWEEN 100 AND 200;
A table can have exactly one clustered index, because rows can only be physically stored in one order at a time. This is usually built on the primary key by default, but it doesn't have to be - it can be built on any column, and choosing the right one matters for tables with heavy range queries on a different column.
Non-Clustered Index: A Separate Structure Pointing Back to the Table
A non-clustered index is a genuinely separate structure from the table itself, holding a copy of the indexed column or columns plus a pointer back to the actual full row.
Think of the actual index section at the back of a non-fiction book. It doesn't contain the book's content - it contains a sorted list of terms, each with a page number pointing back to where the real content lives. Looking up "photosynthesis" in that index is fast specifically because the index itself is small and sorted, even though the real explanation lives many pages away.
-- A non-clustered index on Title, separate from
-- the table's physical (clustered) order by Id
CREATE INDEX IX_Posts_Title ON Posts(Title);
-- This query can now use the index to find matching
-- Titles quickly, then follow the pointer back to
-- the full row for the columns not in the index
SELECT * FROM Posts WHERE Title = 'Azure Key Vault';
-- Conceptually what happens:
-- 1. Search the small, sorted Title index - fast
-- 2. Find the pointer (usually the clustered key)
-- 3. Jump to that row in the actual table for
-- everything else (PublishedDate, etc.)
Unlike clustered indexes, a table can have many non-clustered indexes - one per column or column combination that gets filtered or sorted on frequently. Each one is a genuine tradeoff though, covered further down in this post.
Composite Index: Multiple Columns, and Why Order Matters
A composite index is built across more than one column together, rather than a separate single-column index for each.
-- A composite index on (LastName, FirstName) -
-- column ORDER in the index definition matters
CREATE INDEX IX_Employees_Name
ON Employees(LastName, FirstName);
-- This query benefits fully - it filters on the
-- FIRST column in the index (LastName), which is
-- how the index is actually sorted
SELECT * FROM Employees WHERE LastName = 'Smith';
-- This query ALSO benefits - it filters on both
-- columns, matching the index's sort order exactly
SELECT * FROM Employees
WHERE LastName = 'Smith' AND FirstName = 'Alex';
-- This query gets LITTLE TO NO benefit from the
-- same index - it filters on FirstName alone, which
-- is the SECOND column, not how the index is sorted
SELECT * FROM Employees WHERE FirstName = 'Alex';
Column order in a composite index isn't cosmetic. Think of a phone book sorted by last name, then first name. Finding everyone with last name "Smith" is fast - they're all grouped together. Finding everyone with first name "Alex," regardless of last name, gets no help at all from that same sort order, since Alexes are scattered throughout the entire book. A composite index behaves identically: it only helps a query that filters starting from its leftmost column.
Covering Index: When the Index Alone Can Answer the Query
A covering index includes every single column a specific query needs, so the database can answer the query entirely from the index itself, without ever touching the actual table.
-- Without a covering index - Title is indexed, but
-- PublishedDate is not, so after finding a matching
-- Title, the database still has to jump to the
-- actual table row to get PublishedDate
CREATE INDEX IX_Posts_Title ON Posts(Title);
SELECT Title, PublishedDate
FROM Posts
WHERE Title = 'Azure Key Vault';
-- With a covering index - PublishedDate is added as
-- an INCLUDED column, so it lives inside the index
-- structure itself
CREATE INDEX IX_Posts_Title_Covering
ON Posts(Title)
INCLUDE (PublishedDate);
-- Now the SAME query above is answered entirely from
-- the index - the table itself is never touched at
-- all, which is the fastest possible outcome for a read
A covering index is genuinely the fastest read path available, but it duplicates more data into the index, increasing storage, and it's the first kind of index to quietly stop covering a query once that query changes to select one more column than the index includes - at which point it silently falls back to the slower table lookup, with no error or warning that this happened.
The Honest Part: When an Index Makes Things Worse
Every index speeds up specific reads. Every index also slows down every write to that table, because every INSERT, UPDATE, and DELETE has to update every index on the table, not just the actual row. A table with six indexes pays that update cost six times, on every single write, not once.
Several situations provide little to no real indexing benefit.
Low-cardinality columns are the first - a boolean IsActive column only has two possible values, and an index here rarely helps because the database still ends up scanning roughly half the table either way, with no meaningful narrowing happening.
Small tables are the second - a table with a few hundred rows can often be scanned faster than an index can be looked up and followed, since the overhead of using the index exceeds the cost of just reading the whole small table.
Columns rarely used in WHERE, JOIN, or ORDER BY clauses are the third - an index that nothing actually filters, joins, or sorts on provides zero read benefit while still paying the full write cost on every insert or update.
Write-heavy tables with comparatively few reads are the fourth - a table that's written to constantly but read from rarely pays the full write-side tax of every index without collecting much benefit on the read side.
The actual rule of thumb: an index is a bet that a specific column will be filtered, joined, or sorted on often enough that the read speedup is worth the write cost and storage cost. It is not a default good practice to apply everywhere - it's a targeted tool for a specific, identifiable access pattern.
Key Lessons
A clustered index is the table's physical storage order - exactly one per table, no exceptions.
A non-clustered index is a separate structure pointing back to the real row - many allowed per table, each one a genuine tradeoff.
Composite index column order determines what the index actually helps - it only benefits queries filtering from its leftmost column onward.
A covering index answers a query entirely from the index itself, but silently stops covering the moment a query needs one more column than it includes.
Every index speeds up specific reads while slowing down every write to that table - this tradeoff is real, not theoretical, and gets worse as more indexes accumulate.
Low-cardinality columns, small tables, rarely-filtered columns, and write-heavy tables are all real cases where an index provides little benefit while still costing something on every write.
What's Next
Part 3 of this series covers stored procedures, views, and transactions, including ACID properties and transaction isolation levels.
Summary
An index trades write cost and storage for read speed on a specific, identifiable access pattern - it is not a default good practice, it's a targeted tool. A clustered index is the table's own physical order, one per table. A non-clustered index is a separate lookup structure, many allowed, each with its own cost. A composite index only helps queries filtering from its leftmost column. A covering index is the fastest possible read, until a query outgrows what it includes. Knowing when an index genuinely helps, and being honest about the cases where it doesn't, is what separates real indexing knowledge from just knowing the CREATE INDEX syntax.
Originally published at TechStack Blog: https://www.techstackblog.com/post.html?slug=database-indexing-explained
Part 1 of this series (Joins): https://www.techstackblog.com/post.html?slug=database-joins-explained
More from TechStack Blog: Database: https://www.techstackblog.com/category.html?cat=database
CS Fundamentals: https://www.techstackblog.com/category.html?cat=cs-fundamentals
Top comments (0)