Indexes: Creating and Using Them

By the end you can create and drop indexes, choose the column order of a multi-column index, and prove with EXPLAIN QUERY PLAN that a query really uses one.

Fundamentals mentioned that a PRIMARY KEY keeps a sorted lookup structure. This lesson lets you build your own and check, instead of hope, that a query uses it.

What an index buys and costs

An index is a sorted copy of one or more columns, each entry pointing back at its row. A query that filters on those columns jumps to the matching entries instead of reading every row. The price: every INSERT, UPDATE and DELETE must also update each index on the table, and each one takes space. So index the columns your queries search on, not every column.

Creating and dropping

CREATE INDEX idx_books_author ON books(author);
CREATE INDEX IF NOT EXISTS idx_books_author ON books(author);
CREATE UNIQUE INDEX idx_loans_once ON loans(book_id, member);
DROP INDEX idx_books_author;

IF NOT EXISTS skips silently when the name is taken. UNIQUE adds the constraint rule on top: a second loan of book 1 to 'amy' fails with UNIQUE constraint failed: loans.book_id, loans.member, and the CREATE itself fails if the existing rows already hold a duplicate. DROP INDEX removes only the index; the rows stay. A PRIMARY KEY or UNIQUE column already has an index; do not add another.

Check, do not trust

Put EXPLAIN QUERY PLAN in front of a SELECT and SQLite prints how it would run it. Take books(id INTEGER PRIMARY KEY, isbn TEXT UNIQUE, author, genre, year, title):

EXPLAIN QUERY PLAN SELECT * FROM books WHERE author = 'Le Guin';
QUERY PLAN
`--SCAN TABLE books

Verdict one, SCAN, means every row is read. After CREATE INDEX idx_books_author ON books(author) the same statement prints:

`--SEARCH TABLE books USING INDEX idx_books_author (author=?)

Verdict two: SEARCH ... USING INDEX names the index and, in parentheses, the columns it matched. isbn and id already have lookups of their own:

WHERE isbn = 'i1'  ->  SEARCH TABLE books USING INDEX sqlite_autoindex_books_1 (isbn=?)
WHERE id = 7       ->  SEARCH TABLE books USING INTEGER PRIMARY KEY (rowid=?)

Which clauses an index serves

• WHERE with = or a range (<, >, BETWEEN) on the indexed column.
• Join columns. A join looks up matching rows once per row of the other table, so index the column it compares, usually the foreign key. WHERE book_id = 1 on loans prints SCAN TABLE loans until CREATE INDEX idx_loans_book ON loans(book_id); then it prints SEARCH TABLE loans USING INDEX idx_loans_book (book_id=?).
• ORDER BY. The index is already sorted, so no sort is needed. With an index on (genre, year), WHERE genre = 'sf' ORDER BY year prints one SEARCH line. ORDER BY title adds a second line, USE TEMP B-TREE FOR ORDER BY: a separate sort.

Multi-column indexes and the leftmost-prefix rule

CREATE INDEX idx_books_genre_year ON books(genre, year);

An index is usable from its leftmost column onward. This one serves genre alone, and genre with year, but not year alone:

WHERE genre = 'sf' AND year = 1969  ->  SEARCH ... (genre=? AND year=?)
WHERE genre = 'sf'                  ->  SEARCH ... (genre=?)
WHERE genre = 'sf' AND year > 1960  ->  SEARCH ... (genre=? AND year>?)
WHERE year = 1969                   ->  SCAN TABLE books

The order of the terms in the WHERE does not matter, only the order of the columns in CREATE INDEX: WHERE year = 1969 AND genre = 'sf' still matches both. Put the column your queries always filter on first.

Common mistakes

• Columns in the wrong order. An index on (year, genre) leaves WHERE genre = 'sf' printing SCAN TABLE books, although the index contains genre.
• Leaving an old index behind. A forgotten single-column index can turn a query you meant to leave as SCAN into SEARCH, and it still slows every write. Remove it with DROP INDEX.
• Making an index UNIQUE on a column with repeats. The CREATE fails with UNIQUE constraint failed, the index never exists, and the plan still reads SCAN.

Your exercise

A small orders table, an index left over from an earlier experiment, and three EXPLAIN QUERY PLAN statements. Fix the indexes so each plan reads as the starter's comments describe; the printed plan lines are graded. The likeliest slip is the column order of the two-column index.