lesson 48 Advanced product analysis · advanced

Query plans & indexes

Inspect how SQLite reads data and uses indexes.

the idea

EXPLAIN QUERY PLAN describes how SQLite will read the data: SCAN reads a whole table, SEARCH uses an index to jump to matching rows, and a covering index answers from the index alone. A plan is evidence about access paths, not a timing measurement.

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

   Read the plan for customer_id. Run this. Explain shows SEARCH on an index—SQLite jumps to that customer’s rows instead of reading the whole orders table.

   step-1.sql Run

   sql›

   EXPLAIN QUERY PLAN
   SELECT order_id
   FROM orders
   WHERE customer_id = 18102;

2. 2

   Read the plan for country. Run this. Without a helpful index on country, the plan shows SCAN and SQLite checks every order row.the pattern

   example.sql Run

   sql›

   EXPLAIN QUERY PLAN
   SELECT order_id
   FROM orders
   WHERE country = 'Germany';

common mistake

An index on orders.customer_id helps a filter on customer_id; it does not help a filter on country.