A Covering Index

An index entry holds a copy of the index's columns and a pointer to the full row. When a query reads a column the index doesn't hold, SQLite finds the matching entries in the index, then goes to the table for each row to read the rest. The plan calls that USING INDEX.

When the index holds every column the query reads, in the SELECT and in the WHERE, SQLite never opens the table. It answers from the index alone, and the plan says USING COVERING INDEX. For example, an index on orders (status, ordered) holds every column this query reads:

"SELECT ordered "
"FROM orders "
"WHERE status = 'paid' "

Adding a column to an index doesn't change the rows a query returns, only where SQLite reads them from. Keep the searched column first, so the search can still jump in, and add the extra columns after it.

The check creates old_index, prints your query's plan, creates new_index, prints the plan again and shows the rows. Your script must print:

With old_index:
query: SEARCH orders USING INDEX idx_orders_customer_ordered (customer_id=?)
After new_index:
query: SEARCH orders USING COVERING INDEX idx_orders_customer_ordered_status (customer_id=?)
ordered | status
2026-03-02 | paid
2026-04-05 | paid
(2 rows)

Your task: old_index on orders (customer_id, ordered) is given. Write query: the ordered and status of customer 1's orders. Then write new_index: idx_orders_customer_ordered_status on orders (customer_id, ordered, status).

Assignment
Follow the Coding Tutorial to write the query, clause by clause, and the covering index that adds status.

Hint
new_index is two strings: CREATE INDEX and the name on one line, ON orders (…) on the next. Keep the space at the end of each string so the two lines don't run together.