Indexes & EXPLAIN QUERY PLAN

Make lookups fast

Lesson 19 of 20 · 11 min · Module 6: Performance and the capstone

With ten orders, every query is instant. With ten million, "show this customer’s orders" can take seconds unless the database has an index: a sorted structure that finds matching rows without reading the whole table.

Start this lesson →

Free. No installation: you write and run the code in your browser, and it is checked automatically.

By the end, you’ll be able to…

Create an index for a frequent query
Choose the column order of a composite index
Read a query plan

The idea

CREATE INDEX idx_name ON orders (customer_id, ordered_at) builds an index on those columns, in that order. It helps queries that filter on customer_id and sort by ordered_at. EXPLAIN QUERY PLAN before a query shows whether SQLite will SCAN the whole table or SEARCH using an index.

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

01Like a book index

Without an index, the database reads every row, which the plan calls SCAN. With an index on customer_id it jumps straight to that customer’s rows: SEARCH … USING INDEX.

CREATE INDEX idx_c ON orders (customer_id);
EXPLAIN QUERY PLAN SELECT * FROM orders WHERE customer_id = 2;

Output

SEARCH orders USING INDEX idx_c (customer_id=?)

02Column order matters

An index on (customer_id, ordered_at) is sorted by customer, then by date within each customer. It serves WHERE customer_id = ? ORDER BY ordered_at perfectly, but it cannot help a query that filters only on ordered_at.

03Indexes have a cost

Every index takes space and slows down inserts and updates a little, because it must be kept up to date. Add indexes for queries you actually run often, and check their effect with the plan.

Your turn

Create an index on orders whose first column is customer_id and second column is ordered_at. Then return id, ordered_at and status of customer 2’s orders, sorted by date.

What the checks look for

Returns customer 2’s orders in date order
An index on orders starts with customer_id
Its second column is ordered_at

Try it in the editor →

Take this with you

Indexes turn full scans into direct lookups for the queries you design them for.

Go a little further

Run EXPLAIN QUERY PLAN for the same query before and after creating the index and compare SCAN with SEARCH.