lesson 67 Performance & query plans · advanced

Indexes: observe a real access path

Create indexes and inspect their actual query plans.

the idea

An index offers an access structure at the cost of storage and write work. Use EXPLAIN QUERY PLAN to see SQLite choose SCAN, SEARCH, or a covering index. Plans describe physical access; they are not measured speed. This tiny dataset cannot establish a production performance improvement.

tables in this lesson

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

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

products4 columns · 6 rows

idinteger · key	nametext	categorytext	pricereal
1	Notebook	Stationery	12
2	Desk lamp	Home	45
3	Keyboard	Tech	80
4	Pen set	Stationery	8
5	Headphones	Tech	120

showing 5 of 6 rows

example

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

example.sql

CREATE INDEX idx_orders_customer ON orders(customer_id);
EXPLAIN QUERY PLAN
SELECT id,quantity
FROM orders
WHERE customer_id=1;

Watch for: SQL Server clustered/nonclustered and columnstore indexes have different storage details from SQLite indexes.

build it step by step

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

1
Plan a lookup before indexing. Without an index, filtering orders by customer_id may scan. EXPLAIN QUERY PLAN shows the current path.
step-1.sql
sql›
```
EXPLAIN QUERY PLAN
SELECT id, quantity
FROM orders
WHERE customer_id = 1;
```
2
Add an index and re-check the plan. CREATE INDEX can change the access path. Compare the new plan to the scan you saw before.
step-2.sql
sql›
```
CREATE INDEX idx_orders_customer ON orders(customer_id);
EXPLAIN QUERY PLAN
SELECT id,quantity
FROM orders
WHERE customer_id=1;
```