Indexing strategy

Why it matters

The single most common cause of a slow database is a query that reads the whole table to find a handful of rows. An index turns that full scan into a direct lookup, often making a query hundreds of times faster. But every index also slows down inserts and updates and takes disk space, so a DBA's job is not "add indexes" but "add the right indexes and remove the useless ones".

The core idea: a sorted copy with pointers

Most indexes are B-trees: a sorted, balanced structure of the indexed column values, each pointing to the table row. Because it is sorted, the database can jump straight to customer_id = 42 and walk forward, instead of checking every row. Think of the index at the back of a textbook.

An index helps when a query:

• filters with =, IN, <, >, BETWEEN or LIKE 'abc%' on the indexed columns,
• joins on them,
• sorts by them (ORDER BY can read the index in order and skip a sort step).

An index rarely helps when the filter matches a large share of the table (low selectivity, such as status = 'active' when 95% of rows are active) or when the column is wrapped in a function (WHERE lower(email) = ...) unless you create an index on that expression.

Composite indexes and the leftmost prefix

A composite index covers several columns in order: (customer_id, status, ordered_at) is sorted by customer, then status within each customer, then date. It can serve:

• WHERE customer_id = ?
• WHERE customer_id = ? AND status = ?
• WHERE customer_id = ? AND status = ? ORDER BY ordered_at

It cannot efficiently serve WHERE status = ? alone, because status is not the first column. Rule of thumb: put equality columns first, then the range or sort column last.

A covering index contains every column a query needs, so the database never touches the table. In PostgreSQL you can add extra columns with INCLUDE; in SQLite you simply add them to the end of the index.

Worked example: reading the plan

SQLite shows its plan with EXPLAIN QUERY PLAN:

EXPLAIN QUERY PLAN
SELECT id, ordered_at, total_cents
FROM orders
WHERE customer_id = 3 AND status = 'shipped'
ORDER BY ordered_at DESC;

Without an index, the detail column reads something like:

SCAN orders
USE TEMP B-TREE FOR ORDER BY

SCAN means every row is read; the temp B-tree is an extra sort. Now add a composite index and ask again:

CREATE INDEX idx_orders_customer_status_date
  ON orders (customer_id, status, ordered_at);

SEARCH orders USING INDEX idx_orders_customer_status_date (customer_id=? AND status=?)

SEARCH ... USING INDEX is a direct lookup, and the sort step has gone because the index is already in date order (SQLite reads it backwards for DESC). Exact wording varies a little between SQLite versions.

In PostgreSQL the equivalent is EXPLAIN (ANALYZE, BUFFERS) SELECT ..., which runs the query and shows real timings. Look for Seq Scan on big tables versus Index Scan or Index Only Scan. MySQL uses EXPLAIN and EXPLAIN ANALYZE (8.0.18+).

You can also inspect an index's columns. In SQLite:

SELECT seqno, name FROM pragma_index_info('idx_orders_customer_status_date');

Other index types worth knowing

• Unique indexes enforce uniqueness (created automatically for UNIQUE and primary keys).
• Partial indexes index only some rows: CREATE INDEX ... ON orders (ordered_at) WHERE status = 'pending'; Small and fast when queries always use that filter (PostgreSQL and SQLite both support them).
• Expression indexes: CREATE INDEX ... ON users (lower(email));
• PostgreSQL also has GIN (JSONB, arrays, full-text), GiST and BRIN (very large, naturally ordered tables such as time series).

The cost side

Every insert, update or delete must also update each index on the table. Ten indexes on a busy table can make writes several times slower. In PostgreSQL, pg_stat_user_indexes shows idx_scan per index; an index with zero scans after weeks of normal traffic is a candidate to drop (check replicas and monthly jobs first). In production PostgreSQL, build indexes with CREATE INDEX CONCURRENTLY so writes are not blocked.

Common mistakes

• Indexing every column "just in case".
• Composite indexes in the wrong order (range column first).
• Duplicate indexes: (a) is redundant when (a, b) exists.
• Trusting a tiny test table: on 50 rows the planner may scan anyway, because a scan is cheaper. Test with realistic volumes.
• Forgetting to refresh statistics (ANALYZE) after a big load, so the planner misjudges row counts.

Quick recap

• A B-tree index turns scans into lookups for selective filters, joins and sorts.
• Composite indexes follow the leftmost-prefix rule: equality columns first, range or sort column last.
• Read plans before and after (EXPLAIN QUERY PLAN, EXPLAIN ANALYZE) and remember the write cost.

Try it

In the Do tab, design the composite index for a customer order-history query and compare the plans.