An Index That Sorts

To answer ORDER BY, SQLite needs the rows in that order. If nothing hands them over sorted, it reads them all and sorts them in a temporary structure, and the plan prints an extra line: USE TEMP B-TREE FOR ORDER BY. A LIMIT doesn't avoid it: SQLite can't know which rows come first until it has seen every one.

An index is always kept sorted by its columns. When you sort by the index's column, SQLite reads the rows from the index, already in order, and stops as soon as the LIMIT is met. It can walk an index from either end, so a plain index also serves DESC. For example, this index keeps the orders sorted by status, so a query that sorts by status can read them from it in order:

"CREATE INDEX idx_orders_status ON orders (status) "

The plan still starts with SCAN, since there's no WHERE to jump to, but it adds USING INDEX: the scan follows the index's order. The check prints the plan without and with your index, then the rows. Your script must print:

Without an index:
query: SCAN orders
query: USE TEMP B-TREE FOR ORDER BY
After create_index:
query: SCAN orders USING INDEX idx_orders_ordered
id | ordered | status
7 | 2026-05-30 | paid
6 | 2026-05-21 | cancelled
5 | 2026-05-07 | paid
(3 rows)

Your task: write query: the id, ordered, status of the 3 newest orders, sorted by ordered, newest first. Then write create_index: idx_orders_ordered on orders (ordered).

Assignment
Follow the Coding Tutorial to write the sorted query, one clause per line, and the index on ordered.

Hint
DESC goes after the column in the ORDER BY, and LIMIT comes last. The index needs no DESC: SQLite reads it backwards.