Indexes: How They Work and How to Design Them

By the end you can predict from an index's structure whether a query seeks, scans or sorts, and shape one index to carry its filter, sort and constraint.

In SQL Intermediate you created indexes, followed the prefix rule and read SCAN versus SEARCH. Here is why those rules hold and what else decides whether one is used. Examples use loans(id, branch, due, card, isbn, fine) and bookings(id, room, slot, state).

What an index stores

A table is a B-tree keyed by rowid (your id). An index is a second B-tree whose entries are the indexed values followed by the rowid, kept sorted. An index idx_bd on (branch, due) holds entries written branch|due|rowid:

east|03-01|4   east|03-09|1   east|04-02|6   west|03-01|2   west|03-15|5

All east entries are adjacent, so branch = 'east' is one seek: jump to the first match and read on. The 03-01 entries are scattered, so WHERE due = '03-01' cannot seek: only a leading run is contiguous.

A lookup finds a rowid, then descends the table B-tree for the other columns. That second descent disappears when every column the query reads is in the index. Every entry carries the rowid, so SELECT id, due WHERE branch = 'east' plans SEARCH loans USING COVERING INDEX idx_bd (branch=?), while SELECT isbn plans a plain USING INDEX and pays the second descent per row. There is no INCLUDE clause: list the extra columns last.

Equality, one range, then order

Within the equality columns the next column is sorted, so a seek can use it as a range, but nothing after it. With an index on (branch, due, card) and branch = 'east' AND due > '03-05' AND card = 7, the plan shows (branch=? AND due>?). Inside the due range the cards are unsorted, so each entry is tested. With card before due, all three narrow the seek.

The same ordering serves ORDER BY: WHERE branch = 'east' ORDER BY due on idx_bd reads entries already sorted, while ORDER BY isbn adds USE TEMP B-TREE FOR ORDER BY, a sort done after the fetch.

What defeats an index

A seek needs the stored column compared plainly. WHERE fine * 2 = 10 is a SCAN loans even with an index on fine: entries are sorted by fine, not fine * 2. A quoted number is no trap: card = '7' still seeks, because an INTEGER column converts the literal first (type affinity).

LIKE ignores letter case, but a default index uses the BINARY collation, which compares byte by byte, so Mc and mc sort far apart. borrower LIKE 'Mc%' is therefore a SCAN. An index declared (borrower COLLATE NOCASE) makes LIKE seek; GLOB 'Mc*' seeks but is case-sensitive.

The general fix is an expression index, which stores the result of an expression instead of the column. Repeat the expression exactly in the query:

CREATE INDEX idx_year ON loans(strftime('%Y', due));

WHERE strftime('%Y', due) = '2025' now plans SEARCH ... USING INDEX idx_year (<expr>=?). Writing substr(due, 1, 4) instead does not match, and the plan returns to SCAN.

Partial and unique indexes

A partial index stores only the rows matching its WHERE. It is smaller, but usable only when the query repeats that condition:

CREATE INDEX idx_fined ON loans(card) WHERE fine > 0;

WHERE card = 7 AND fine > 0 seeks. Drop the fine test and the plan is SCAN loans: the index does not hold every loan of card 7.

A unique index only compares the rows it stores, so a partial or expression UNIQUE index states a rule a column cannot:

CREATE UNIQUE INDEX uq_one_held ON bookings(room, date(slot)) WHERE state = 'held';

This allows one held booking per room per day; cancelled ones pile up freely. With a held booking for room A on 2025-05-01, a plain INSERT of another held one that day fails with an error; INSERT OR IGNORE skips it silently, and a booking on 2025-05-02 goes through. SELECT name, "unique", partial FROM pragma_index_list('bookings') lists a table's indexes with the two flags as 0 or 1.

Common mistakes

• Indexing strftime('%Y', due) but querying substr(due, 1, 4): the plan stays a SCAN.
• Leaving a partial index's condition out of the query: the plan is a SCAN.
• Leaving the WHERE off a unique index: a cancelled row still blocks the next one.

Your exercise

Create the four named indexes so each query is answered from an index and only the live duplicate signup is rejected. The likeliest slip is copying the shape of one query's index onto another's.