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.

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

// One query returns everything
const orders = await db.query(`
  SELECT o.*, u.name, u.email
  FROM orders o
  JOIN users u ON u.id = o.user_id
`);