Indexes

How does the database find the orders of customer 3? Without help, it reads the table orders row by row, all of them, and keeps the ones whose customer_id is 3. With 30 rows, that's instant; with 30 million, it takes a while.

An index works like the index at the end of a book: instead of reading the whole book, you look up a word and go straight to its pages. An index on a column keeps the column's values sorted, each with the rows it's in, and in a sorted list, the database finds a value quickly. You create one like this:

CREATE INDEX orders_customer_id ON orders (customer_id);

An index has a name (usually the table and the column), and after ON comes the table and, in brackets, the column. You never mention the index in your queries: the database decides by itself when it helps. The queries stay the same and find the same rows, only faster.

To see what the database plans to do, put EXPLAIN QUERY PLAN in front of a query. Its result has several columns; the one that matters is detail. Without the index:

EXPLAIN QUERY PLAN
SELECT * FROM orders WHERE customer_id = 3;

SCAN means that the database reads the whole table. With the index, it searches:

SEARCH means that it looks the value up in the index, here orders_customer_id. The brackets say which column it searches by, and the question mark stands for the value it's looking for, here 3.

The exact words depend on the version of SQLite; what matters is SCAN or SEARCH. An index helps on big tables, with the columns that queries search by in WHERE, join by in JOIN … ON, and sort by in ORDER BY. A primary key has an index of its own, and so does every UNIQUE column (that's how the database checks quickly that a value isn't there yet). Foreign keys don't get one in SQLite, and as tables are joined by them all the time, they're often the first columns to get one. An index can also have several columns: ON games (genre, price) helps queries that search by the genre, or by the genre and the price, as a phone book sorted by surname and first name does.

An index isn't free, though. It takes space, and every INSERT, UPDATE and DELETE has to update all the indexes of the table, which makes them slower. That's why tables don't get an index for every column, only for the ones that queries really need. And on small tables, like the ones of Pixel Shop, an index makes no difference you could notice.

CREATE UNIQUE INDEX creates an index that also allows each value only once, just like UNIQUE. It's the way to add that rule to a table that already exists, which SQLite's ALTER TABLE can't do. An index you don't need is deleted with DROP INDEX and its name.

Typical mistake: wrapping the column in a function. An index on email doesn't help WHERE LOWER(email) = '…': the index has the emails as they are, not in lowercase, so the database has to work out LOWER() for every row anyway.