lesson 48 Advanced product analysis · advanced

Query plans & indexes

Inspect how SQLite reads data and uses indexes.

the idea

An index can help SQLite find or order rows efficiently. EXPLAIN QUERY PLAN shows SCAN versus SEARCH and index use. Indexes cost storage and slow writes; the optimizer may still scan a tiny table.

tables in this lesson

The challenges use this data. Each column shows its type underneath; → marks a link to another table.

customers4 columns · 8 rows

idinteger · key	nametext	citytext	joined_attext
1	Alice	Chicago	2024-01-10
2	Ben	Austin	2024-02-12
3	Cara	Seattle	2024-03-05
4	Dev	Chicago	2024-04-20
5	Elena	NULL	2024-05-01

showing 5 of 8 rows

orders6 columns · 12 rows

idinteger · key	customer_idinteger	product_idinteger	quantityinteger	statustext	ordered_attext
101	1	1	2	paid	2025-01-01
102	2	3	1	paid	2025-01-02
103	1	2	1	paid	2025-01-05
104	3	5	2	pending	2025-01-08
105	4	4	5	paid	2025-02-01

showing 5 of 12 rows

example

The idea in one query. Press Run to see what it returns; running examples never affects your progress.

example.sql

EXPLAIN QUERY PLAN
SELECT name
FROM customers
WHERE city='Chicago';

Watch for: A full scan is not automatically bad; table size and selectivity matter.

build it step by step

Each step changes one thing. Run them in order and watch the result change.

1
Run the filtered lookup. This is the query whose plan you will inspect. Note the city predicate—indexes may later speed this path.
step-1.sql
sql›
```
SELECT name
FROM customers
WHERE city = 'Chicago';
```
2
Read the query plan. EXPLAIN QUERY PLAN names the access strategy (scan, search, and so on). Indexes change which path the engine picks.
step-2.sql
sql›
```
EXPLAIN QUERY PLAN
SELECT name
FROM customers
WHERE city = 'Chicago';
```