15 Make a lookup fast

15 / 15

Goal

Make SELECT * FROM orders WHERE customer_id = 3 use an index instead of scanning the table.

To find one customer's orders, the database has to look at every row in orders — a full table scan. With 16 rows, nobody notices. With 16 million, every page load waits.

An index is a sorted copy of one column with pointers back to the rows, like the index at the back of a book. With one on customer_id, the database jumps straight to the matching rows.

EXPLAIN QUERY PLAN shows how SQLite intends to run a query without running it. SCAN orders means every row; SEARCH orders USING INDEX means a jump.

Try it

Read the plan, add the index, read it again. Watch the scan sweep every row — and then not.

Indexes aren't free: every INSERT and UPDATE has to keep them current. Index the columns you search and join on, not everything.

Go to the terminal

Hint

Look at the plan with EXPLAIN QUERY PLAN SELECT * FROM orders WHERE customer_id = 3;, then CREATE INDEX idx_orders_customer ON orders (customer_id); and look again.

Show a solution

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

This interactive environment needs JavaScript. Everything you need to read is on this page, and the solution works the same way for real.

SQL

Starting SQL…