Indexes and EXPLAIN

Without help, finding rows by a value means scanning the whole table — fine for five rows, slow for five million. An index is a sorted lookup structure (think of a book's index) that lets the database jump straight to matching rows.

The trade-off

An index makes reads that filter or join on its column dramatically faster — turning a full scan into a direct lookup (the same O(n) → O(log n) idea from the complexity lesson). The cost: it uses extra space, and it must be updated on every write, so indexing slows inserts/updates a little. Index the columns you filter and join on most; don't index everything.

Creating an index

CREATE INDEX idx_orders_customer ON orders (customer_id);

Now joins and WHERE customer_id = … can use the index instead of scanning. Primary keys are indexed automatically; you add indexes for other hot columns.

EXPLAIN: see the plan

The database decides how to run a query. EXPLAIN QUERY PLAN (SQLite's form) shows that decision — crucially, whether it's scanning a table or using an index:

You'll see a SCAN (reads every row). Add an index on country and the same EXPLAIN would show a SEARCH using it instead — measurable proof the index helps. This is the "measure, don't guess" rule for queries: EXPLAIN is your profiler.

Where to go next

Fast and correct reads aside, changing data safely needs transactions — next.