SQL-Engineering-Handbook

Why This Module Exists

Every prior module in this handbook teaches you to write SQL. This module teaches you to understand what the database actually does when it runs that SQL — specifically, how it decides whether to scan a table row by row or jump directly to the rows you need, and what happens to that decision over the months and years an index actually lives in production.

Indexes are the single biggest lever between a query that returns in 2ms and one that times out under production load. A Data Analyst who can write a correct JOIN but cannot explain why it’s slow, or a Junior Data Analyst who cannot read an EXPLAIN plan, will hit a ceiling fast in any real engineering organization. This module exists to remove that ceiling.

Full table scan vs. index seek

Who This Is For

Aspiring Data Analysts and Analytics Engineers who have completed the core SQL modules (00–09) and are ready to move from “SQL that works” to “SQL that performs at scale.” No prior database internals knowledge is assumed — every structural concept (B-Trees, cost-based optimization, execution plans, long-term index health) is built up from first principles.

Quick Start

mysql -u root -p your_database < 00_SETUP.sql   # creates every table this module uses
mysql -u root -p your_database < 01_INDEX_FUNDAMENTALS.sql

Every .sql file from 01 through 10 runs cleanly against the schema 00_SETUP.sql creates — no missing-table errors, no manual setup beyond that one file.

What This Module Covers

#	File	Focus	Diagram
00	Setup	Every table this module references, with keys, FKs, and sample data	—
01	Index Fundamentals	Full scans vs. index seeks, cost-based optimization, logical vs. physical storage	scan vs. seek
02	B-Tree Indexes	B-Tree/B+Tree structure, how MySQL/PostgreSQL/SQL Server/Oracle use them	B+Tree
03	Composite Indexes	Leftmost prefix rule, column ordering strategy	leftmost prefix
04	Unique, Primary & Foreign Key Indexes	Constraint-backed indexes and their storage implications	clustered vs. non-clustered
05	Covering Indexes	Index-only scans, included columns	covering index
06	Indexing Strategies	OLTP vs. OLAP design, star schema, when not to index	read/write trade-off
07	Query Optimization with Indexes	Predicate pushdown, selectivity, statistics, histograms	selectivity
08	EXPLAIN & Execution Plans	Reading `EXPLAIN` / `EXPLAIN ANALYZE` across engines	execution plan
09	Real-World Case Studies	11 industries, each with a genuinely distinct indexing challenge — not just relabeled examples	—
10	Index Maintenance, Redundancy & Myths	Fragmentation, bloat, VACUUM/OPTIMIZE/REBUILD, duplicate-index detection, common myths refuted	index lifecycle
11	Interview Guide	59 production interview questions, easy → hard, FAANG-style	—
12	Practice Problems	Beginner → production-grade debugging, optimization, and maintenance exercises	—
13	Solutions	Fully worked solutions to Module 12	—

Each .md file is paired with a .sql file containing the runnable, commented queries referenced in the text. SQL is written against MySQL 8.0+ syntax first, with ANSI SQL, PostgreSQL, SQL Server, and Oracle notes called out wherever behavior diverges.

The Diagrams

Every diagram in this module (12 total, one per core concept) is rendered as a standalone SVG in assets/diagrams/ and embedded directly in its corresponding file — no external image hosting, so they render correctly whether you’re reading on GitHub, cloned locally, or in any markdown viewer.

B+Tree structure Covering index

Index lifecycle Execution plan

assets/DIAGRAM_SPECS.md documents exactly what exists, what each diagram shows, and what was deliberately left out (and why) — kept accurate against the actual asset folder, not aspirational.

Build Status

✅ Complete and release-reviewed. All 14 files, their paired SQL, the resource list, and every diagram are published. This module has been through a full pre-commit engineering audit — every SQL file executes end-to-end against 00_SETUP.sql, every cross-reference resolves, and every previously-flagged technical claim has been corrected or removed.

Module Checklist

Every .sql file runs cleanly after 00_SETUP.sql — zero missing-table errors
Every file follows the documentation template, or explicitly states why it doesn’t (File 09)
Every diagram referenced in a file exists and is embedded, not just linked
Cross-engine notes (MySQL/PostgreSQL/SQL Server/Oracle) present in every content file
No unverifiable or vendor-marketing technical claims
Index maintenance, redundancy, and common myths covered as a first-class topic, not an afterthought

How to Use This Module

Run 00_SETUP.sql once, against a scratch schema.
Read the .md file for a topic before opening its .sql file — the concepts (why an index helps) matter more than the syntax (how to create one).
Run each .sql file and compare your EXPLAIN output to the one documented in the file — plans vary by data distribution, and seeing the divergence is part of the learning (see the scale note at the top of 00_SETUP.sql).
Read File 09 for how the concepts combine in real schemas, then File 10 for what happens to those indexes a year later.
Attempt Module 12’s practice problems before reading Module 13’s solutions.
Heading to an interview? Start from Module 11 — every question cross-references back to the file that covers it.

Resources

Curated further reading lives in resources/: recommended books, engineering blogs, official documentation per engine, talks and lectures, and interview prep.

This site is open source. Improve this page.