The last mile: from correct to fast, and from toy to production
Nine sessions made you correct. This one makes you production-ready. Two ideas do most of the work. First, performance: a database can answer a query by reading every row (a full scan) or by jumping straight to the rows it needs (an index) - and the difference is invisible at 33 rows and enormous at 33 million. Second, portability: the SQL core you learned is a standard, but each engine speaks a slightly different dialect at the edges. Know the core, flag the edges, and you can walk into any warehouse.
EXPLAIN QUERY PLAN and tell a full scan from an index lookup, an intuition for the index tradeoff (faster reads, slower writes, more storage), and a dialect-delta table you can keep on your desk for the day your SQLite muscle memory meets a real warehouse. That is the whole Builder track, landed.
Why queries get slow, and reading a plan 7 min live
A query is a request; the engine decides how to fulfill it, and that plan is where speed lives or dies. The most common slow path is a full table scan - reading every single row to find the few you asked for. EXPLAIN QUERY PLAN shows you the engine's chosen strategy before you commit to it.
LiveRead the plan for a filtered query3 min▶
EXPLAIN QUERY PLAN does not run the query - it tells you how SQLite would. Run this and read the output: with no index on customer_id, it says it will SCAN the orders table - read every row to find customer 1's orders.
EXPLAIN QUERY PLAN SELECT * FROM orders WHERE customer_id = 1;
SCAN means "read the whole table" and SEARCH ... USING INDEX means "jump to the rows via an index". Training your eye to spot SCAN on a big, frequently-filtered table is 80% of practical query tuning.
Indexes: the tradeoff that makes reads fast 7 min live
An index is a sorted lookup structure on a column - like the index at the back of a book. Create one on the column you filter or join by, and the engine can find matching rows without scanning. It is the single highest-leverage performance move. But it is a tradeoff, not free magic.
LiveAdd an index, then re-read the plan3 min▶
This box runs on a fresh seeded copy, so creating an index here is safe and resets next run. We build an index on orders(customer_id), then ask for the plan of the same query from Part 1. The output should now say SEARCH ... USING INDEX instead of SCAN.
CREATE INDEX idx_orders_customer ON orders(customer_id); EXPLAIN QUERY PLAN SELECT * FROM orders WHERE customer_id = 1;
The tradeoff, stated plainly. An index makes reads on that column faster - sometimes thousands of times faster at scale. In exchange, every write (INSERT / UPDATE / DELETE) gets a little slower because the index has to be kept in sync, and the index itself costs storage. So you index the columns you filter and join on constantly, not every column. On a write-heavy table, too many indexes can hurt more than they help.
Self-studyWhich columns deserve an index?2 min read▶
You do not index everything - you index where the engine keeps doing expensive lookups. The reliable candidates are the columns that show up in WHERE and JOIN ... ON over and over on large tables. Primary keys are already indexed for you; foreign keys usually should be, because that is what every JOIN filters on.
- Index: foreign keys, columns in frequent WHERE filters, columns you sort large results by.
- Skip: tiny tables (a scan is already instant), columns you rarely filter on, write-hot tables where index upkeep dominates.
- Measure, do not guess: EXPLAIN QUERY PLAN before and after tells you whether the index is actually used.
SQLite to real warehouses 8 min live
The SQL core you learned - SELECT, WHERE, JOIN, GROUP BY, HAVING, CTEs, window functions - is the same in PostgreSQL, MySQL, BigQuery, and Snowflake. What shifts is the edges: date functions, string functions, how you cap rows, how you match case-insensitively. Here is the delta table to keep on your desk.
| Need | SQLite | PostgreSQL | MySQL | BigQuery | Snowflake |
|---|---|---|---|---|---|
| Cap rows | LIMIT 5 | LIMIT 5 | LIMIT 5 | LIMIT 5 | LIMIT 5 (or TOP 5) |
| Month from date | substr(d,1,7) | DATE_TRUNC('month', d) | DATE_FORMAT(d,'%Y-%m') | DATE_TRUNC(d, MONTH) | DATE_TRUNC('month', d) |
| String concat | a || b | a || b | CONCAT(a, b) | CONCAT(a, b) | a || b |
| Case-insensitive match | LIKE (default) | ILIKE | LIKE (default) | LOWER(x) = ... | ILIKE |
Note the old SQL Server TOP 5 style still shows up in legacy code - most modern engines standardized on LIMIT. When in doubt, the engine's own docs settle it; the concepts never change, only the spelling.
Self-studyWhere SQL actually runs at scale2 min read▶
SQLite runs inside one process reading one file - which is why this whole course fits in a browser tab. A production warehouse like BigQuery or Snowflake spreads one query across many machines, each chewing a slice of a table that may hold billions of rows, then merges the results. You still write the same SELECT ... GROUP BY; the engine handles the parallelism. Your job shifts from "can I express this?" to "will this be cheap and fast?" - which is exactly why plans and indexes (and, in cloud engines, partitioning and clustering) become part of the craft.
- What transfers: the entire language core from b1-b9. Learn it once.
- What is new at scale: cost awareness, partitioning, clustering, and reading bigger, engine-specific query plans.
- The mindset: correct first, then fast. You now have both halves.
Try it yourself - this week ◐ 20-30 min total
- Take one query you actually run at your job (or a b9 query) and run
EXPLAIN QUERY PLANon the equivalent here. Find the word SCAN or SEARCH and say what it means out loud. - On a fresh seeded box, add an index to a scratch column you filter on, re-run EXPLAIN QUERY PLAN, and confirm the plan changed from SCAN to SEARCH USING INDEX.
- Take the dialect table and rewrite one Daybreak query's "month from date" line as if you were on BigQuery, then on Postgres. Same result, different spelling.
- Write down, in one sentence each, the index tradeoff and the difference between what transfers and what shifts between SQLite and a warehouse.
- You finished the Builder track. Pick a real question at work and answer it end to end - confirm, drill, explain, brief - the way you did in b9. That habit is the whole point.
Official sources covered
This session teaches the performance and portability core of the major advanced curricula, run on live data instead of screenshots. Deep engine-specific tuning stays on the official docs. This page covers:
Three questions before you go 🎯 ◐ 90 seconds
1 · What does an index do?
An index is a sorted lookup structure: reads on that column get much faster, but every write must keep it in sync and it costs storage. Index the columns you filter and join on.
2 · What does EXPLAIN QUERY PLAN tell you?
It shows the engine's strategy without running the query: SCAN means read every row, SEARCH ... USING INDEX means jump to the matches. Spotting SCAN on big tables is most of practical tuning.
3 · What transfers from SQLite to a production warehouse?
SELECT, WHERE, JOIN, GROUP BY, HAVING, CTEs, and window functions are the same. The edges - date and string functions, row limits, case-insensitive matching - shift by engine.