Read a Query Plan in Python

Before SQLite runs a SELECT, it picks a way to find the rows: its query plan. Put EXPLAIN QUERY PLAN in front of the SELECT and SQLite answers with that plan instead of the rows. SCAN orders means it reads every row of the table. SEARCH orders USING INDEX … means it jumps straight to the matching part of an index.

An index only helps when the WHERE compares the column itself. The index on ordered is sorted by the dates, not by what a function makes of them, so a function around the column sends SQLite back to a scan. Here is a query for one day:

day_query = (
    "EXPLAIN QUERY PLAN "
    "SELECT * "
    "FROM orders "
    "WHERE ordered = '2026-04-05' "
)

With an index on ordered, its plan is SEARCH orders USING INDEX idx_orders_ordered (ordered=?). Write the WHERE as date(ordered) = '2026-04-05' instead and the plan is SCAN orders.

Dates stored as YYYY-MM-DD text sort in date order, so >= and < work on them directly. A whole month can be a range on the bare column: from its first day, and before the first day of the next month. The check prints the plan of month_query, runs create_index, prints both plans, then shows the rows of range_query without its first line:

month_query: SCAN orders
After CREATE INDEX:
month_query: SCAN orders
range_query: SEARCH orders USING INDEX idx_orders_ordered (ordered>? AND ordered<?)
range_query without EXPLAIN QUERY PLAN:
id | customer_id | ordered | status
5 | 4 | 2026-05-07 | paid
6 | 2 | 2026-05-21 | cancelled
7 | 3 | 2026-05-30 | paid
(3 rows)

Your task: month_query already selects May's orders with strftime. Put EXPLAIN QUERY PLAN in front of it. Write create_index, an index named idx_orders_ordered on the ordered column of orders. Then write range_query: May's orders again, as a range on ordered itself, with EXPLAIN QUERY PLAN in front.

Assignment
Follow the Coding Tutorial to read the plan of a query, add an index, and rewrite the May filter so the index is used.

Hint
EXPLAIN QUERY PLAN is one more string line above the SELECT. For May, compare ordered with >= '2026-05-01' and < '2026-06-01', joined by AND, with no function around the column.