Advanced

Making lookups fast

A query that was instant on my laptop crawled on the real data. The database was reading every row to answer a filter. An index is a shortcut the database keeps so it can skip most of that reading.

What you will see

info|id|opcode|p1|p2|p3 0|0|Idx|1|2|idx_name sqlite>

About 2 min of reading

Try It

Add an index and confirm the database reaches for it.

Build an index on the name column of users.

CREATE INDEX idx_name ON users (name);

Before you run it, say out loud what it will print. Then open the answer.

What it prints

sqlite>

Filter on that column. The answer is the same, but now sqlite can use the index.

SELECT id FROM users WHERE name = "Ada";

Before you run it, say out loud what it will print. Then open the answer.

What it prints

id 1 sqlite>

Ask sqlite how it plans to answer. The output names the index it will use.

EXPLAIN SELECT id FROM users WHERE name = "Ada";

Before you run it, say out loud what it will print. Then open the answer.

What it prints

info|id|opcode|p1|p2|p3 0|0|Idx|1|2|idx_name sqlite>

Data Flow

CREATE INDEX builds a sorted lookup for a column, and a filter on that column can use it to skip most of the table.

1 CREATE INDEX builds a lookup

↓

2 A filter targets that column

↓

3 Database uses the index

↓

4 Most rows are skipped

Why this works

What an index does

An index is a separate sorted structure the database keeps for a column, like the index at the back of a book. A filter on that column can jump to the matching rows instead of reading the whole table. You pay a little cost when you insert data, and you save a lot when you query it.

When it does not help

An index only pays off when the filter narrows a lot. Indexing a column where every row has the same value does nothing useful. And an index you never filter on is pure overhead. The honest way to decide is to run EXPLAIN and see whether sqlite actually reaches for it.

Examples

Declare an index

CREATE INDEX idx_name INDEX users ON name;

That is not the syntax. The real shape is CREATE INDEX followed by a name, then the table and column. sqlite builds a sorted lookup for that column.

The real shape

CREATE INDEX idx_name ON users (name);

Now a filter on name can use the index instead of scanning every row. You did not change any query, the database uses it automatically when it helps.

Check it is used

EXPLAIN SELECT name FROM users WHERE name = "Ada";

EXPLAIN prints how sqlite plans to answer. If it says it used the index, the filter is fast. If it says full scan, the index is not helping that query.

Words this lesson uses

Each one is defined here on this site. Open the entry for the full definition and the other words it points at.

← The blank that is not zero

Previous Lesson

The blank that is not zero

Next Lesson

Tables that vouch for each other

Tables that vouch for each other →