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
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
WHEREclauses: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) versusIndex Scan.EXPLAIN ANALYZEruns the query and shows real timings. - MySQL: the
typecolumn showsALLfor a full scan andreforrangewhen an index is used. - SQLite:
EXPLAIN QUERY PLANshowsSCANversusSEARCH ... 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:
- 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. - Second normal form: every column depends on the whole primary key. In
order_items (order_id, product_id, quantity, product_name),product_namedepends only onproduct_id, so it belongs inproducts. - Third normal form: columns depend on the key and nothing else. If
ordersstoredcustomer_city, that depends on the customer, not the order, so it belongs incustomers.
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, notcust). - Columns are
NOT NULLunless "unknown" is a real, valid state. - Money uses
NUMERICor whole-number paise or cents. Times useTIMESTAMPwith a time zone where your database supports it. UNIQUEconstraints 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_casetables. - 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
JSONBand 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
- Lesson 1 · 6 minWhat is a database?Tables, rows and columns, primary and foreign keys, relational versus NoSQL databases, and what SQL actually is.
- Lesson 2 · 6 minCreating tablesCREATE TABLE, choosing data types, constraints that keep data valid, and building the shop database used for the rest of the course.
- Lesson 3 · 5 minReading data with SELECTChoose columns, rename them with aliases, calculate new values, remove duplicates, and understand the order SQL runs a query in.