Indexes in Java

To find the rows a WHERE asks for, SQLite needs a way in. With no help it reads the whole table, row by row: a scan. An index is a sorted list of one column's values, each pointing to its rows, like the index at the back of a book. With one, SQLite can search: jump straight to the matching rows.

You create an index with CREATE INDEX, a name, then ON, the table and the column in brackets. For example, an index for finding the order lines of a product:

String createIndex = "";
createIndex += "CREATE INDEX idx_items_product_id ON order_items (product_id) ";

To see what SQLite does, put EXPLAIN QUERY PLAN in front of a query: instead of the rows, it answers its plan. An index never changes which rows a query finds, only how it finds them.

The check prints the plan of query, runs createIndex, then prints the plan again. It must show:

Plan: SCAN orders
Plan: SEARCH orders USING INDEX idx_orders_customer_id (customer_id=?)

Your task: in run(), build query, the plan of a query that finds the orders of customer 1, and createIndex, an index named idx_orders_customer_id on the customer_id column of orders.

Assignment
Follow the Coding Tutorial to write the query first, add EXPLAIN QUERY PLAN in front of it, then write the index.

Hint
The query is SELECT * from orders with a WHERE on customer_id, one piece per query += line, each ending with a space. EXPLAIN QUERY PLAN goes on its own line above the SELECT * line, so it comes first.