Lesson 8 of 8 · 6 min

Indexes and schema design

How indexes make queries fast and what they cost, reading a query plan, normalisation in plain words, and a checklist for designing tables.

On this page
  1. What an index is
  2. What to index
  3. What indexes cost
  4. Reading a query plan
  5. Normalisation in plain words
  6. When to break the rules
  7. A schema design checklist
  8. Where to go next

The last lesson is about making a database fast and keeping its design clean as it grows.

What an index is

Without an index, finding a customer by email means reading every row until a match turns up. That is a full table scan: fine for a hundred rows, slow for ten million.

An index is a separate, sorted structure (usually a B-tree) that points to rows, like the index at the back of a book. The database can jump straight to the right place.

CREATE INDEX idx_orders_customer_id ON orders (customer_id);

Primary keys and UNIQUE columns get an index automatically.

What to index

Index the columns your queries search, join and sort on:

  • Foreign keys: orders.customer_id, order_items.product_id. PostgreSQL does not index these for you, and joins on them are everywhere.
  • Columns in frequent WHERE clauses: status, email, created_at.
  • Columns you sort by for paged lists.

A composite index covers several columns, and the order matters:

CREATE INDEX idx_orders_customer_date ON orders (customer_id, ordered_at);

This index helps WHERE customer_id = 1 and WHERE customer_id = 1 ORDER BY ordered_at. It does not help WHERE ordered_at = '2026-11-01' alone, because the index is sorted by customer first, like a phone book sorted by surname can't find everyone named Asha.

What indexes cost

Every index makes reads faster and writes slower, because each INSERT, UPDATE and DELETE has to update the index too. Indexes also take disk space. Add them for queries you actually run, not "just in case".

Reading a query plan

Ask the database how it will run a query:

EXPLAIN SELECT * FROM orders WHERE customer_id = 1;
  • PostgreSQL: look for Seq Scan (reads everything) versus Index Scan. EXPLAIN ANALYZE runs the query and shows real timings.
  • MySQL: the type column shows ALL for a full scan and ref or range when an index is used.
  • SQLite: EXPLAIN QUERY PLAN shows SCAN versus SEARCH ... USING INDEX.

When a query is slow, run EXPLAIN before changing anything.

Normalisation in plain words

Normalisation is a set of rules for splitting data into tables so each fact is stored once. The first three "normal forms" cover almost everything:

  1. First normal form: one value per cell. No comma-separated lists like tags = 'sql,db,web'. Use a separate table with one row per tag.
  2. Second normal form: every column depends on the whole primary key. In order_items (order_id, product_id, quantity, product_name), product_name depends only on product_id, so it belongs in products.
  3. Third normal form: columns depend on the key and nothing else. If orders stored customer_city, that depends on the customer, not the order, so it belongs in customers.

The short version: if you would have to update the same fact in two places, split the table.

When to break the rules

Sometimes you copy data on purpose (denormalise) for speed or history. An order should store the price at the time of purchase, because the product's price will change later:

ALTER TABLE order_items ADD COLUMN unit_price NUMERIC(10, 2);

That is not duplication; it is a different fact ("what the customer paid").

A schema design checklist

  • Every table has a primary key.
  • Every relationship has a foreign key with a clear name (customer_id, not cust).
  • Columns are NOT NULL unless "unknown" is a real, valid state.
  • Money uses NUMERIC or whole-number paise or cents. Times use TIMESTAMP with a time zone where your database supports it.
  • UNIQUE constraints exist for things that must be unique in the real world (emails, slugs, SKUs).
  • Foreign keys and frequently filtered columns are indexed.
  • Table and column names follow one style, such as plural snake_case tables.
  • Schema changes go through migrations: numbered SQL files checked into Git, so every environment has the same structure.

Where to go next

  • Practise on a real dataset: export something you care about to CSV and import it into SQLite.
  • Learn your database's specifics: PostgreSQL's JSONB and full-text search, or MySQL's storage engines.
  • Connect a database to an application with a driver or ORM, and write a migration for every change.

You now know enough SQL to design, query and maintain the database behind a real application.

More from Databases and SQL