Clustered vs Non-Clustered Index: Where the Rows Actually Live

Clustered vs non-clustered index in one line: a clustered index is not a copy of the table, it is the table, with the rows themselves stored in the index's key order, so there can be only one per table; a non-clustered index is a separate sorted structure whose entries hold the key plus a pointer back to the row, so a lookup through it finds the entry and then fetches the row, unless the index already contains every column the query needs. Which column plays the clustering key depends on the engine: SQL Server lets you choose, MySQL's InnoDB uses the primary key, SQLite uses the rowid, and PostgreSQL has no clustered index at all. The engine behaviour below is quoted from the SQL Server, MySQL and PostgreSQL manuals, as of October 2026; the SQLite behaviour is run live.

Index	What it stores	Per table
Clustered	the rows themselves, in key order	exactly one
Non-clustered	key values plus a pointer to each row	as many as you create
Covering non-clustered	key values plus the other columns a query reads	as many as you create

The problem it solves

Two queries both use an index, both show SEARCH in the plan, and one is ten times slower. The difference is usually what happens after the index finds the key. If the rows live in the index, the engine is already holding them. If the index only holds a pointer, every match is a second lookup into the table, and a range of ten thousand matches is ten thousand jumps. Understanding which of your indexes is the table, and which ones point at it, is how you predict that.

What an index is and how to read SCAN against SEARCH is in SQL indexes explained. This article is about where the rows are.

The intuition

Go back to the lesson's photo library. The prints are filed in boxes in the order they arrived. That filing order is the clustered index: the prints are the data, and their physical order is the index. There can be only one such order, because a print can be in only one place. A drawer of cards sorted by photographer, each card naming a box number, is a non-clustered index: you find the card quickly, and then you walk to the box.

• The clustered index is the table. In SQL Server a clustered index sorts and stores the data rows based on the key, and the documentation says there can be only one per table because the rows can be stored in only one order. In InnoDB every table has one, called the clustered index, and it is the primary key if there is one; without a primary key InnoDB uses the first UNIQUE NOT NULL index, and failing that a hidden row id. In SQLite an ordinary table is a B-tree keyed by the 64-bit rowid, and INTEGER PRIMARY KEY is an alias for it.
• A non-clustered index points back. Its leaf entries hold the indexed columns plus a row locator. In InnoDB the locator is the primary key value, so a secondary lookup is two B-tree searches: the secondary index, then the clustered index. In SQLite it is the rowid. In SQL Server it is the clustered key, or a physical row id if the table is a heap. A wide primary key therefore makes every secondary index wider.
• A covering index skips the second step. If every column the query reads is in the index entry, the engine never visits the row. SQLite's plan says USING COVERING INDEX; SQL Server lets you add such columns with INCLUDE.
• Range scans favour the clustering key. Rows with adjacent keys sit on adjacent pages, so WHERE id BETWEEN a AND b reads a contiguous run. The same range on a secondary column finds adjacent index entries whose rows are scattered across the table.

PostgreSQL is the exception: tables are heaps, every index is secondary, and the CLUSTER command rewrites a table once in the order of an index without keeping it that way. Its index-only scans play the covering role.

The choice of clustering key follows: narrow, so secondary indexes stay small; stable, so rows do not move; and increasing, so inserts append at the end instead of splitting pages in the middle.

Watch it run

The animation is the lesson's photo library. WHERE shot_by = 'Okonjo' with no index reads every row from the top. Then CREATE INDEX photo_by builds the second, sorted copy of shot_by, each entry pointing at its row, and the same query becomes a SEARCH that jumps straight into the Okonjo block and follows two pointers. Those two pointers are the point of this article: the entries are not the rows. The index found Okonjo at once, and then went to fetch each photo from where it actually lives, in rowid order.

The code

The lesson's photo table, with id declared as INTEGER PRIMARY KEY, which in SQLite makes it the rowid, the table's own key:

import sqlite3

db = sqlite3.connect(":memory:")
db.executescript("""
  CREATE TABLE photo (id INTEGER PRIMARY KEY, shot_by TEXT, box INTEGER);
  INSERT INTO photo VALUES (1, 'Okonjo', 4), (2, 'Weiss', 9), (3, 'Okonjo', 2);
""")
def plan(sql):
    return [r[3] for r in db.execute("EXPLAIN QUERY PLAN " + sql)]

# id is the rowid: the table's B-tree is the index
print(db.execute("SELECT rowid, id, shot_by FROM photo WHERE id = 2").fetchall())
print(plan("SELECT box FROM photo WHERE id = 2"))
print(plan("SELECT box FROM photo WHERE id BETWEEN 2 AND 3"))
# [(2, 2, 'Weiss')]
# ['SEARCH photo USING INTEGER PRIMARY KEY (rowid=?)']
# ['SEARCH photo USING INTEGER PRIMARY KEY (rowid>? AND rowid<?)']

# a secondary index: its own B-tree, each entry pointing back at a rowid
db.execute("CREATE INDEX photo_by ON photo(shot_by)")
print(plan("SELECT box FROM photo WHERE shot_by = 'Okonjo'"))      # index, then the row
print(plan("SELECT id FROM photo WHERE shot_by = 'Okonjo'"))       # the rowid is in the entry already
db.execute("CREATE INDEX photo_by_box ON photo(shot_by, box)")
print(plan("SELECT box FROM photo WHERE shot_by = 'Okonjo'"))      # covering: no row fetch
# ['SEARCH photo USING INDEX photo_by (shot_by=?)']
# ['SEARCH photo USING COVERING INDEX photo_by (shot_by=?)']
# ['SEARCH photo USING COVERING INDEX photo_by_box (shot_by=?)']

rowid and id are the same number, and a lookup by id says USING INTEGER PRIMARY KEY: no separate index exists, the table is searched directly. The first shot_by query says USING INDEX: find the entry, then fetch box from the row. Asking only for id makes the same index covering, because every secondary entry carries the rowid it points at. Adding box to the index makes the original query covering too.

Now the cost of the pointer, on 200,000 photos. The same 20,000 rows are selected three ways: by a range on the clustering key, by a range on a secondary column that then has to fetch each row for shot_by, and by the same secondary range answered from the index alone:

import time

big = sqlite3.connect(":memory:")
big.executescript("""
  CREATE TABLE photo (id INTEGER PRIMARY KEY, shot_by TEXT, box INTEGER);
  CREATE INDEX photo_box ON photo(box);
""")
big.executemany("INSERT INTO photo VALUES (?, ?, ?)",
                ((i, f"P{i % 50}", (i * 7919) % 200000) for i in range(200000)))

def timed(sql):
    start = time.perf_counter()
    n = big.execute(sql).fetchone()[0]
    return n, round((time.perf_counter() - start) * 1000, 1)

print(timed("SELECT COUNT(shot_by) FROM photo WHERE id BETWEEN 100000 AND 119999"))
print(timed("SELECT COUNT(shot_by) FROM photo WHERE box BETWEEN 100000 AND 119999"))
print(timed("SELECT COUNT(*) FROM photo WHERE box BETWEEN 100000 AND 119999"))
# (20000, 0.8)
# (20000, 6.3)
# (20000, 0.5)
# on one machine, roughly; the row counts are exact, the milliseconds are not

Same 20,000 rows each time. The range on id reads one contiguous run of the table. The range on box finds 20,000 adjacent index entries whose rows are scattered across the table, because box was deliberately generated out of id order, and fetching each one made it several times slower. Drop the row fetch, by reading nothing the index does not hold, and the secondary range is as fast as the clustered one.

SQLite also lets you pick the clustering key, with WITHOUT ROWID. The table is then stored in primary-key order and has no rowid at all:

db.executescript("""
  CREATE TABLE negative (box INTEGER, frame INTEGER, shot_by TEXT,
                         PRIMARY KEY (box, frame)) WITHOUT ROWID;
  INSERT INTO negative VALUES (4, 1, 'Okonjo'), (4, 2, 'Okonjo'), (9, 1, 'Weiss');
""")
print(plan("SELECT shot_by FROM negative WHERE box = 4"))
print(plan("SELECT shot_by FROM negative WHERE shot_by = 'Weiss'"))
try:
    db.execute("SELECT rowid FROM negative")
except sqlite3.OperationalError as e:
    print(e)
# ['SEARCH negative USING PRIMARY KEY (box=?)']
# ['SCAN negative']
# no such column: rowid

A search by box goes straight into the table through its primary key, with no second lookup, because the rows are stored in (box, frame) order. A search by shot_by has no index and scans. There is no rowid to ask for.

The complexity

Both kinds of index are B-trees, so finding a key is O(log n). The difference is what follows:

• Clustered lookup: O(log n) and the row is in hand. A range of k rows is O(log n + k) with the rows read in storage order.
• Non-clustered lookup: O(log n) in the index plus one row fetch per match, each a further O(log n) into the clustered index in InnoDB, or a page read by rowid in SQLite. A range of k rows is O(log n + k log n) worth of scattered reads.
• Covering lookup: back to O(log n + k), because the fetch is skipped.
• Writes: every secondary index is one more B-tree to maintain, and a wide clustering key is copied into each of them.

Where it goes wrong

• A random clustering key. A random UUID as the InnoDB primary key inserts into the middle of the tree, splits pages, and makes every secondary index carry sixteen bytes per entry. Prefer an increasing integer, or a time-ordered id.
• Expecting two clustered indexes. The rows can be in one order. The second "clustered" order you want is a non-clustered index, with the row fetch that comes with it.
• Range queries on a secondary column, reading many columns. Each match is a jump into the table. Either cover the query or accept that past some fraction of the table a full scan is cheaper, which is what the planner will choose.
• Assuming PostgreSQL keeps CLUSTER order. It rewrites the table once; new rows go wherever there is space.
• Changing the clustering key's value. The row moves, and every secondary index entry pointing at it must change too.

When it shows up in interviews

Directly, as "what is the difference between a clustered and a non-clustered index?", and then as a follow-up to any slow-query question: "it uses the index, so why is it slow?" The answer they want is the row fetch, and the fix they want is a covering index or a different clustering key. The B-tree itself, and why it serves ranges, is in B-tree vs hash index; which columns to put in the index, and in what order, is in composite and covering indexes.

How to say it in an interview

"The clustered index is the table itself, stored in the order of one key, so there is exactly one per table and a range on that key reads adjacent rows. A non-clustered index is a separate sorted structure whose entries point back at the row, by the clustering key in InnoDB or the rowid in SQLite, so a lookup through it costs the index search plus a row fetch per match. If the index contains every column the query reads, it is covering and the fetch is skipped. I pick a narrow, increasing clustering key, and I cover the hot secondary queries."

The scan-to-search switch that starts this story is the Indexes lesson.

Frequently asked questions

What is the difference between a clustered and a non-clustered index?

A clustered index stores the table's rows in the order of its key, so it is the table and there is only one. A non-clustered index is a separate structure of key values with pointers to the rows, so a lookup finds the entry and then fetches the row. A table can have many non-clustered indexes.

Can a table have more than one clustered index?

No. The rows can be physically stored in only one order. SQL Server allows one clustered index, InnoDB always has exactly one, and SQLite's rowid or WITHOUT ROWID primary key plays the same role. Any further ordering you need is a non-clustered index that points back at the rows.

Is a primary key a clustered index?

Often, but not by definition. In InnoDB the primary key is the clustered index. In SQL Server a primary key creates a clustered index by default, but you can make it non-clustered and cluster on another column. In SQLite INTEGER PRIMARY KEY is the rowid, the table's own key. In PostgreSQL a primary key is an ordinary index on a heap table.

Sources

• Clustered and nonclustered indexes — SQL Server documentation
• Clustered and Secondary Indexes — MySQL 8.4 Reference Manual
• CLUSTER — PostgreSQL Documentation
• Rowid Tables — SQLite Documentation
• Clustered Indexes and the WITHOUT ROWID Optimization — SQLite Documentation