Indexes: Speeding Up Reads

Level 3: Level 3: Data Modeling & Schema Designmedium25 minCREATE INDEXindexing FK / WHERE / JOIN / ORDER BY columnsread vs write trade-off

Add B-tree indexes on the columns queries actually filter and join on.

Indexes turn scans into seeks

An index is a sorted secondary structure (a B-tree) that lets the database seek directly to matching rows instead of scanning every row. On a filter like WHERE customer_id = 42, an index on customer_id turns an O(n) table scan into an O(log n) seek. The columns worth indexing are exactly the ones queries filter, join, and sort on:

FK columns: you join on them constantly.
WHERE predicate columns: the selective filters.
ORDER BY / GROUP BY columns: an index can supply pre-sorted rows.

What is auto-indexed for free. A UNIQUE constraint and every PRIMARY KEY except a single-column INTEGER PRIMARY KEY create an index automatically, so you rarely index those yourself. The exception matters: a single-column INTEGER PRIMARY KEY is the table's rowid, so seeking by it is already fast and SQLite creates no separate index object for it (pragma_index_list shows nothing). Either way you index the other hot columns, especially FKs, which are not auto-indexed in SQLite.

Worked example

-- fact_sales is joined to dim_customer on customer_sk all day long
CREATE INDEX idx_fact_sales_customer ON fact_sales(customer_sk);

-- a mart repeatedly filters events by date
CREATE INDEX idx_events_date ON raw_events(event_date);

Anatomy:

CREATE INDEX  idx_fact_sales_customer  ON fact_sales (customer_sk)
                    │                        │            │
              index name (convention:      table      indexed column(s)
              idx_<table>_<col>)                   (multi-col = composite index)

A composite index (a, b) speeds filters on a and on a, b together (the leftmost-prefix rule), but not on b alone.

The trade-off: indexes cost writes

Every index must be updated on every INSERT / UPDATE / DELETE. More indexes = faster reads, slower writes, more storage. So index selectively: the FK and filter columns queries actually use, not every column "just in case."

In the warehouse this differs, a lot. Columnar warehouses (Snowflake, BigQuery) generally don't have traditional B-tree indexes at all; they rely on columnar storage, partitioning, clustering keys, and micro-partition pruning. CREATE INDEX is a row-store (SQLite / Postgres / MySQL) concept. The principle (help the engine skip data instead of scanning) carries over, but the mechanism is partition/cluster design, not indexes.

Common pitfall: over-indexing. An index on a column no query filters on is pure write-cost with zero read benefit. Before adding one, name the query it helps. The second pitfall: forgetting FKs aren't auto-indexed in SQLite. An unindexed FK makes every join scan. Leave a comment on non-obvious indexes explaining the query they serve.

Recap: indexes turn scans into seeks on filter/join/sort columns; UNIQUE and most PRIMARY KEYs are auto-indexed (a single-column INTEGER PRIMARY KEY needs none, it is the rowid) but FKs are not: index those, index selectively because every index taxes writes, and remember warehouses use partitioning/clustering instead.

Execution mode: you write a multi-statement script. It runs against a fresh in-memory SQLite DB, then hidden assertion queries inspect the indexes you created via pragma_index_list / pragma_index_info.

Sample data for this example

CREATE TABLE orders_unindexed (order_id INTEGER PRIMARY KEY, customer_id INTEGER, total_cents INTEGER);
CREATE TABLE orders_indexed   (order_id INTEGER PRIMARY KEY, customer_id INTEGER, total_cents INTEGER);
INSERT INTO orders_unindexed (customer_id, total_cents) VALUES (42, 1000), (7, 500), (42, 250);
INSERT INTO orders_indexed   (customer_id, total_cents) VALUES (42, 1000), (7, 500), (42, 250);
CREATE INDEX idx_orders_indexed_customer ON orders_indexed(customer_id);

Worked example (SQL)

-- The SAME filter against two identical tables. Only orders_indexed has an index on customer_id.
-- Read the detail column: the unindexed table SCANs every row, the indexed one SEARCHes straight to
-- the match. The COMPOUND QUERY / UNION ALL rows are just the scaffolding that stacks the two.
EXPLAIN QUERY PLAN
SELECT * FROM orders_unindexed WHERE customer_id = 42
UNION ALL
SELECT * FROM orders_indexed   WHERE customer_id = 42;

Apply

Your turn

The task this lesson builds to.

A fact_sales table is joined to dim_customer on customer_sk in every mart, but customer_sk is a plain column, not the primary key, so SQLite has not auto-indexed it and the join scans all 500 rows. Add the one index that turns that join into a seek.

You only need to create the index. The seed already loaded the 500 rows, and the grader checks that an index exists on the right column of fact_sales.

3 hints and 2 automated checks are waiting in the workspace.

Practice

Make it stick

A second problem on the same idea, so it survives past today.

You're given a slow three-table mart (seeded for you). The dashboard runs a fact_sales → dim_customer → dim_product join, filtered by dim_product.category and sorted by order_date. Add the two indexes that matter and deliberately skip the ones that don't:

Add an index on fact_sales(customer_sk): an FK join column.
Add an index on fact_sales(product_sk): an FK join column.
Do not index revenue_cents (never filtered or joined) or dim_product.product_sk (already the PK, auto-indexed).
Add a SQL -- comment noting each index taxes every INSERT / UPDATE / DELETE, which is why you index selectively.

4 hints and 3 automated checks are waiting in the workspace.