lesson 67 Performance & query plans · advanced

Indexes: observe a real access path

Create indexes and inspect their actual query plans.

the idea

An index is a sorted structure that lets SQLite find matching rows without reading the whole table. Create one on columns used in selective filters or joins, then confirm the access path with EXPLAIN QUERY PLAN. Indexes cost storage and write time.

Dataset: Retail

The tables the challenges use, with their first 6 rows. Under each column name: the kind of value it holds, and which table it links to.

worked example

Run these steps in order to see the idea before you write your own query. The step marked “the pattern” is what the challenges ask for. Running the example does not count toward progress.

1. 1

   See a full table scan in the plan. EXPLAIN QUERY PLAN with no index on unit_price shows SQLite scanning order_items to find lines priced above £100.the pattern

   example.sql Run

   sql›

   EXPLAIN QUERY PLAN
   SELECT order_item_id
   FROM order_items
   WHERE unit_price > 100;

2. 2

   See the plan use an index. After CREATE INDEX, the same filter should show SEARCH … USING INDEX—SQLite can jump to matching prices instead of reading every row.

   step-2.sql Run

   sql›

   CREATE INDEX idx_items_unit_price ON order_items(unit_price);
   EXPLAIN QUERY PLAN
   SELECT order_item_id
   FROM order_items
   WHERE unit_price > 100;

common mistake

An index on (order_id, stock_code) helps filters on order_id; it does not help a filter on stock_code alone, because stock_code is not the leading column.