An Index on Two Columns in Python

An index can hold two columns. It is sorted by the first column, and by the second only inside each value of the first, like a phone book sorted by last name, then first name. Finding every "Lee" is quick; finding every "Anna" means reading the whole book.

So a two-column index helps a search on its first column, or on the first and the second together. A search on the second column alone can't jump into it, and the plan says SCAN. Here is an index of this kind on status and ordered:

"CREATE INDEX idx_orders_status_ordered ON orders (status, ordered) "

It can help a search for the paid orders, or for the paid orders in a date range, but not a search on ordered alone.

The check prints the plans of your three queries, creates your index, prints the plans again, then counts the rows each query finds. Your script must print:

Without the index:
customer_query: SCAN orders
both_query: SCAN orders
date_query: SCAN orders
After create_index:
customer_query: SEARCH orders USING INDEX idx_orders_customer_ordered (customer_id=?)
both_query: SEARCH orders USING INDEX idx_orders_customer_ordered (customer_id=? AND ordered>?)
date_query: SCAN orders
Rows found:
customer_query: 2
both_query: 1
date_query: 5

Your task: in create_index, create idx_orders_customer_ordered on orders (customer_id, ordered). Then write the WHERE of the three queries: customer 1's orders, customer 1's orders from '2026-04-01' on, and every customer's orders from '2026-04-01' on.

Assignment
Follow the Coding Tutorial to create the two-column index and write the WHERE of each query.

Hint
Put customer_id first in the brackets: the index is sorted by it first. In the plan, SQLite writes ordered>? even for your >=.