Indexes: when to add them and when not to

What an Index Does

An index is a separate data structure (usually a B-tree) that the database maintains alongside your table. It lets the engine jump directly to matching rows instead of scanning every row. The cost: every INSERT, UPDATE, and DELETE must also update the index.

When to Add an Index

• Every foreign key column (prevents full scans on JOINs and deletes).
• Columns used in WHERE clauses on large tables.
• Columns used in ORDER BY or GROUP BY.

-- Index on FK
CREATE INDEX idx_orders_customer ON orders(customer_id);

-- Composite index for a common query pattern
-- SELECT * FROM orders WHERE customer_id = ? AND status = ?
CREATE INDEX idx_orders_customer_status ON orders(customer_id, status);

-- Covering index: includes the columns SELECT needs, avoids table lookup
CREATE INDEX idx_products_sku_price ON products(sku, price);

When Not to Add an Index

• Small tables (under a few thousand rows) — a full scan is faster.
• Columns with very low cardinality (boolean, status with 2 values) — the index saves little.
• Tables that are written to far more than read.

Profile first with EXPLAIN/EXPLAIN ANALYZE. Add indexes in response to slow queries, not pre-emptively for every column.