Indexes

In one line

B-Trees, composite and covering indexes, selectivity — how queries actually get fast.

0/7 · 0%

Think of it like this

The index at the back of a textbook. Without it, finding 'photosynthesis' means reading every page (a full scan). With it, you jump straight to page 214. But keeping that index updated costs a little extra work every time the book is edited (an insert or update).

Key ideas

01
B-Tree index: log(n) seeks; good for equality + range; the default index in every RDBMS.
02
Composite index (a, b): ordered AND — helps queries on a, and on (a,b), but NOT on b alone. Leftmost-prefix rule.
03
Covering index: index contains ALL columns the query needs → no table fetch at all (index-only scan).
04
Selectivity: how many distinct values — unique email = high selectivity → great index; boolean = terrible.
05
Hash index only for equality; GiN/trigram/Full-text for LIKE/text search; avoid leading-wildcard LIKE '%x'.
06
Costs: each index = extra storage + write amplification on INSERT/UPDATE. 3-5 per table max is a sane posture.
07
Reading SQL for indexes is the #1 DB interview skill: SELECT fields → WHERE/ORDER/JOIN columns → index design.

Java / Spring map

→
JPA @Index on entities; flyway scripts for prod indexes; EXPLAIN in dev tooling.

Code & diagrams

B-tree index lookup vs full scandiagram

Rendering diagram…

IndexDesign.sqlsql

From slow query to index decision in three lines.

-- the query that a PM brings you:
SELECT id, status
FROM orders
WHERE user_id = 42
  AND status = 'PAID'
ORDER BY created_at DESC
LIMIT 20;

-- index decision:
CREATE INDEX idx_orders_user_status_created
  ON orders (user_id, status, created_at DESC);
-- (user_id) equality → then (status) equality → then created_at range/sort
-- and it COVERS (id, status) → index-only scan, no table touch

-- catch: the same index does NOT help a query filtering by status alone
-- (leftmost prefix rule) -- that's why you design indexes per access pattern.

Explain without notes

Why is an index on (status, user_id) useless for this query? Leftmost prefix, out loud.

Practice

Take three real queries and write the optimal composite index for each.

Trade-offs

↔
Covering indexes are beautiful and fat; every index taxes writes — balance for your read:write ratio.

Run it in production

You've designed it. Now build, operate, and break the same idea hands-on in the DevOps courses:

Stateful · Connections, pooling & query performance

EXPLAIN ANALYZE, indexes, PgBouncer, and lock contention on a live database.

Completion checklist

I can design indexes from a query using leftmost-prefix + covering logic.

Back to phase

Command Palette