Stop 04 · SQL & DatabasesTopics · 4/6

Query Optimization

In short

A query that takes two seconds on your laptop can take twenty minutes on a billion-row table, and on cloud warehouses slow often means expensive too. A few habits fix most of it.

Understanding why some queries run slowly (missing indexes, unnecessary joins, SELECT *) — important once you're pulling training data from tables with millions of rows.

The mascot flips open a thick dictionary using the colourful thumb tabs on its edge.

Think of it like this

Using a dictionary's thumb tabs

To find 'mango' you don't read every page from 'aardvark'. You flip straight to the M tab, then scan a few pages. A database index is those tabs: it jumps to the right rows instead of reading the whole table.

Watch it happen

An index lookup for customer 4211

Index root

ids 1 to 5000

ids 5001+

1 to 2500

2501 to 5000

4211: 12 rows

1/4Without an index, the database reads every row. With one, it starts at the root of a sorted tree.

The big ideas

What you need to know

Read the plan

EXPLAIN (or EXPLAIN ANALYZE in PostgreSQL) shows whether the database scans the whole table or uses an index.

Index what you filter on

Columns used often in WHERE, JOIN and ORDER BY are good index candidates.

Select fewer columns

Name only what you need. Column-based warehouses scan and charge less that way.

Filter early

Cut rows before big joins and aggregations, and avoid wrapping indexed columns in functions.

See it in code

Walk through the code

sql

1-- Slow: scans every row, then sorts2EXPLAIN ANALYZE3SELECT * FROM orders WHERE customer_id = 4211 ORDER BY created_at DESC;4 5-- Add an index that matches the filter and the sort6CREATE INDEX idx_orders_customer_created7    ON orders (customer_id, created_at DESC);8 9-- Faster: index lookup, fewer columns10SELECT order_id, amount, created_at11FROM orders12WHERE customer_id = 421113ORDER BY created_at DESC14LIMIT 20;

1/3EXPLAIN ANALYZE runs the query and reports how it was executed and how long it took.

PostgreSQL syntax. Run EXPLAIN ANALYZE before and after the index to see the plan change from a full scan.

Save yourself the pain

Common mistakes

Oops

Adding an index on every column 'just in case'.

Do this instead

Each index slows down inserts and updates and takes space. Add them for real, frequent queries.

Oops

Writing WHERE YEAR(order_date) = 2026 on an indexed date column.

Do this instead

Use a range instead: order_date >= '2026-01-01' AND order_date < '2027-01-01', so the index can be used.

Your mission

Watch an index work

~30 min

Quick check

EXPLAIN shows 'Seq Scan on orders' for a query filtering on customer_id. What does it suggest?

Go deeper

1Learn what an index is and why it speeds up filtering on a column.
2Practice replacing SELECT * with only the columns you actually need.
3Use EXPLAIN (or your database's equivalent) to see how a query is being executed.