Indexes and speed

What is an index?

Think of the index at the back of a book. To find every page that mentions Kingfishers, you could read the whole book, or turn to K in the index and go straight to pages 14, 52 and 210. A database index does the same job for a column: a separate, sorted list of the column's values, each pointing at the rows that hold it.

Without an index on member_id, finding member 3's loans means reading every row in loans and testing each one. That is called a full scan. With an index, SQLite looks up 3 in the sorted list (a few steps, even for millions of rows) and reads only the matching rows.

An index never changes the answer a query gives. It only changes how much work the database does to find it. Your SELECT stays exactly the same.

How do I create and remove an index?

CREATE INDEX name ON table (column); builds one. Name it after what it covers, such as idx_loans_member_id, because index names share one namespace with tables and must be unique in the database. DROP INDEX name; removes it, and IF NOT EXISTS / IF EXISTS work the same way as for tables.

Building the index reads the whole table once. After that, SQLite keeps it up to date on every INSERT, UPDATE and DELETE. You never refer to it in a query: the query planner chooses whether to use it.

CREATE INDEX idx_loans_member_id ON loans (member_id);
SELECT name, tbl_name
FROM sqlite_master
WHERE type = 'index';

How do I tell whether a query uses an index?

Put EXPLAIN QUERY PLAN in front of a SELECT. SQLite doesn't run the query. It returns a short description of how it would. The detail column is the part to read.

SCAN loans means SQLite reads every row. SEARCH loans USING INDEX idx_loans_member_id (member_id=?) means it jumps to matching rows with the index. USING COVERING INDEX is better still: every column the query needs is inside the index, so the table itself isn't touched. USE TEMP B-TREE FOR ORDER BY means SQLite has to sort the rows itself after finding them.

Run the plan below before and after creating the index in the task. PostgreSQL and MySQL use EXPLAIN (or EXPLAIN ANALYZE), and their output looks different, but the same question applies: is it scanning the whole table, or using an index?

EXPLAIN QUERY PLAN
SELECT *
FROM loans
WHERE member_id = 3;

Which columns are worth indexing?

Index columns you search, join or sort on often, in tables that grow large. Foreign keys are the classic case: loans.member_id and loans.book_id are used in nearly every join, and SQLite does not index them for you. Primary keys and UNIQUE columns already have an index.

Columns with very few distinct values, like a yes/no is_returned, gain little from an index on their own, because each value matches a large share of the table. Small tables don't need indexes either. With 30 loans, a scan is instant. The plan still shows the difference, which is why it is the right tool for practising, but the speed gain only shows when a table has thousands of rows or more.

• Good: foreign keys, columns in frequent WHERE filters, columns in ORDER BY on big tables.
• Usually not worth it: tiny tables, columns with two or three possible values, columns you never filter on.
• Already done for you: INTEGER PRIMARY KEY, UNIQUE columns.

What does an index cost?

Every index is a copy of its columns, kept in sorted order. It takes disk space, and every INSERT, UPDATE or DELETE on the table must update each index too. A table with eight indexes does nine writes for every new row.

That is why indexing every column is a mistake. Add an index when a real query is slow or its plan shows a scan on a large table, check the plan again, and remove indexes nothing uses. For a table that is written far more often than it is read, like a log, fewer indexes is usually better.

What is a UNIQUE index for?

CREATE UNIQUE INDEX does two jobs: it speeds up lookups and it refuses duplicate values, exactly like a UNIQUE constraint. It is also the way to add a uniqueness rule to a table that already exists, because SQLite's ALTER TABLE can't add constraints.

If the table already has duplicates, creating the index fails with a UNIQUE constraint error. Find them first with GROUP BY and HAVING, fix them, then build the index. NULLs don't count as duplicates, so several books without an ISBN are still allowed.

SELECT isbn, COUNT(*) AS copies
FROM books
WHERE isbn IS NOT NULL
GROUP BY isbn
HAVING COUNT(*) > 1;

Why does column order matter in a multi-column index?

An index on (member_id, loaned_on) is sorted by member first, then by date within each member, like a phone book sorted by surname and then first name. It can find all of member 3's loans, or member 3's loans in March, and return them already in date order. It can't find all loans on a given date quickly, just as a phone book can't find everyone called Ruth.

Rule of thumb: put the column you filter on with = first, then the column you sort by or filter with a range. Run the plan below and notice there is no separate sort step.

CREATE INDEX idx_loans_member_date ON loans (member_id, loaned_on);
EXPLAIN QUERY PLAN
SELECT loaned_on, book_id
FROM loans
WHERE member_id = 3
ORDER BY loaned_on;

Why would SQLite ignore my index?

Wrapping the column in a function or calculation hides it from the index. WHERE lower(author) = 'terry pratchett' can't use an index on author, because the index stores the original values. Match the stored values instead, or create an index on the expression itself: CREATE INDEX idx_books_author_lower ON books (lower(author));.

LIKE '%gods' with a leading wildcard can't use an ordinary index either, since the start of the text is unknown. And on tiny tables the planner may decide a scan is cheaper anyway, which is a sensible choice. When an AI tool suggests indexes, ask it which query each one serves, then check that query's plan. An index nothing uses is pure cost.

EXPLAIN QUERY PLAN
SELECT title
FROM books
WHERE lower(author) = 'terry pratchett';

Field notes

• An index works like the one at the back of a book: it jumps to matches without reading every row.
• CREATE INDEX name ON table (column); builds one, named after what it covers.
• Put EXPLAIN QUERY PLAN before a query to see how SQLite would run it.
• Index columns you often search or join on, such as foreign keys in large tables.

Easy to get wrong

A multi-column index can only jump to rows by its first column. Put the column you filter with = first.

A function around the column stops SQLite from using the index on it, turning a quick search into a full scan.

The primary key is how SQLite stores rows in the first place. An extra index on it only costs space and write time.

A plain index speeds up lookups but accepts duplicate values. Only a UNIQUE index enforces the rule.

Questions people ask

Does an index change the order of SELECT results?

It can, by accident. Without ORDER BY, rows come back in whatever order is cheapest, and a new index may change that. If you need a particular order, always write ORDER BY.

Do I need to rebuild indexes?

No, SQLite keeps them up to date on every write. Running ANALYZE gathers statistics that help the planner choose between indexes on large tables, and VACUUM compacts the file, but neither is needed for correctness.

How do I list the indexes on a table?

SELECT * FROM pragma_index_list('loans') shows each index, whether it is unique, and whether you created it ('c') or SQLite made it for a UNIQUE or PRIMARY KEY rule. pragma_index_info('index_name') lists its columns in order.

Why does my plan still say SCAN on a small table?

Sometimes the planner decides reading 30 rows is cheaper than using an index, and it is right. Plans matter on large tables. Equality lookups like member_id = 3 usually use an index when one exists.

Is a primary key an index?

In effect, yes. Ordinary SQLite tables are stored sorted by rowid, so finding a row by its INTEGER PRIMARY KEY is a SEARCH without any extra index. Other primary key types get an automatic unique index.