Indexes & Query Optimization

An index is a separate data structure — typically a B-tree — that the database maintains alongside your table. It stores column values in sorted order with pointers back to the full row, letting the engine find matching rows in O(log n) instead of scanning every page.

Indexes are not free. Every write to the table must also update every index on that table. Too many indexes slow down inserts and updates. The right strategy: index columns that appear in WHERE clauses, JOIN conditions, and ORDER BY. Use EXPLAIN (Postgres/MySQL) or EXPLAIN ANALYZE to see what the query planner actually does. The N+1 problem — fetching a list then querying each item individually — is the most common performance killer in ORMs; fix it with eager loading or a single JOIN.

Before

N+1 sketch (db.query placeholders)

1// Fetch orders, then fetch each user separately2const orders = await db.query('SELECT * FROM orders');3for (const order of orders) {4  order.user = await db.query(5    'SELECT * FROM users WHERE id = $1',6    [order.user_id]7  );8}

After

Single query with JOIN

1// One query returns everything2const orders = await db.query(`3  SELECT o.*, u.name, u.email4  FROM orders o5  JOIN users u ON u.id = o.user_id6`);

Exercise

Take a slow SELECT with a WHERE or JOIN. Run EXPLAIN (ANALYZE if safe) before and after adding an index on the filter/join columns. Record whether the plan switched from seq scan to index scan and whether write cost is acceptable for your workload.