Detect a missing index (slow query)

Show that looking up a customer's orders scans the whole orders table, add the missing index, and confirm the plan switches to an index search.

1. 1Run EXPLAIN QUERY PLAN SELECT id, order_date, total FROM orders WHERE customer_id = 3 and note the SCAN.
2. 2CREATE INDEX idx_orders_customer_id ON orders (customer_id).
3. 3Run the same EXPLAIN QUERY PLAN again as your last statement; it should now SEARCH using the index.

• orders has an index on customer_id
• The last result set is the plan using idx_orders_customer_id
• The script uses EXPLAIN QUERY PLAN

• Every run starts from a freshly seeded database, so your script must include its own setup statements.
• Use the exact index name idx_orders_customer_id; it appears in the plan text.
• Only the last result set of your script is compared, so finish with the verification query.