Stop 03 · SQL for Data AnalysisTopics · 9/10

Query Optimization Basics

In short

A query that takes 4 minutes on a big table blocks your work and can annoy everyone sharing the database. A few habits make most queries much faster.

Understanding why some queries run slowly (missing indexes, unnecessary joins, SELECT *) well enough to write reasonably efficient queries against large tables.

The jaadu uses coloured tabs in a thick book to jump to a page, with a stopwatch nearby.

Think of it like this

Using the book's index

To find 'photosynthesis' in a 600-page textbook, you don't read every page. You check the index at the back and jump to page 212. A database index works the same way: it lets the database jump to matching rows instead of scanning them all.

Watch it happen

Full scan vs index lookup

No index

Reads all 10 million rows
Checks each row's customer_id
Slow and grows with table size

Index on customer_id

Jumps straight to matching rows
Reads a handful of pages
Stays fast as the table grows

Filter and join on indexed columns when you can. Ask your data engineer which columns are indexed.

The big ideas

What you need to know

Select only what you need

SELECT * pulls every column. Naming five columns moves far less data, especially in cloud warehouses that bill by data scanned.

Filter early

Put date and status filters in WHERE so fewer rows reach joins and grouping.

Don't wrap indexed columns

WHERE YEAR(order_date) = 2026 can stop an index being used. Use a date range instead.

Read the plan

EXPLAIN shows how the database will run a query, including whether it scans the whole table.

See it in code

Walk through the code

sql

1-- Slower: function on the column, every column fetched2SELECT * FROM orders3WHERE EXTRACT(YEAR FROM order_date) = 2026;4 5-- Faster: plain range on the column, only needed columns6SELECT order_id, customer_id, amount7FROM orders8WHERE order_date >= '2026-01-01'9  AND order_date <  '2027-01-01';10 11EXPLAIN SELECT order_id FROM orders WHERE customer_id = 42;

1/3Wrapping order_date in a function usually prevents the database using its index.

Same result, very different cost on a big table. EXPLAIN shows whether an index is used.

Save yourself the pain

Common mistakes

Oops

Running SELECT * on a huge table just to look at it.

Do this instead

Add LIMIT 100 and name columns. In BigQuery, LIMIT alone doesn't reduce data scanned, so pick columns too.

Oops

Joining big tables first and filtering at the very end.

Do this instead

Filter each table down first, often inside CTEs, then join the smaller results.

Your mission

Read your first query plan

~25 min

Quick check

Which WHERE clause is most likely to use an index on order_date?

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.