What's included

This chapter covers the core features and capabilities of SQL Query Optimization Guide.

What's included

sql-query-optimization/
├── README.md
├── LICENSE
├── sql/                                  # runnable, heavily-commented lessons
│   ├── 01_explain_plan_basics.sql              Read a plan in both engines
│   ├── 02_index_design.sql                     Selectivity, composites, covering
│   ├── 03_sargable_predicates.sql              WHERE clauses an index can seek
│   ├── 04_join_strategies.sql                  Nested loop vs hash vs merge
│   ├── 05_window_functions.sql                 Replace self-joins & correlated subqueries
│   ├── 06_query_rewrites.sql                   Equivalent shapes that run 100x faster
│   ├── 07_antipatterns.sql                     The recurring mistakes + fixes
│   └── 08_pagination.sql                       Keyset pagination vs deep OFFSET
├── docs/                                 # the runbooks (the "why")
│   ├── READING-EXECUTION-PLANS.md              EXPLAIN/ANALYZE, estimate vs actual
│   ├── INDEX-DESIGN.md                         The index decision framework
│   ├── JOIN-STRATEGIES.md                      Which join, when, and how to steer it
│   ├── WINDOW-FUNCTIONS.md                     Pattern catalogue (ranking, running totals)
│   ├── ANTI-PATTERNS.md                        Field guide: smell → why → fix
│   └── QUERY-REWRITE-PATTERNS.md               Result-preserving rewrites
└── examples/
    └── sample_schema.sql                       Portable e-commerce schema + seed data

Each sql/0X file pairs with a docs/ runbook: the .sql is the runnable

lesson you paste into a session; the .md is the deeper explanation, decision

tables, and FAQ.

Prerequisites

• PostgreSQL 12+ *or* MySQL 8.0 / MariaDB 10.5+, and its standard CLI

(psql or mysql). Everything runs in a plain SQL session — no extensions

required for the core lessons (a couple of optional notes mention pg_trgm /

FULLTEXT for substring search).

• A scratch database you can create and drop. The examples build and seed

~320,000 rows so plans behave like production, not like a 10-row toy.

• Comfort reading SQL. This is intermediate material: it assumes you know

JOIN/GROUP BY and want to know *why* one form outruns another.

• Window functions (sql/05) require MySQL 8.0+ / MariaDB 10.2+ — not MySQL

5.7. Every other lesson works on 5.7 with the inline notes.

One note on EXPLAIN ANALYZE: plain EXPLAIN never runs your query and is

always safe. EXPLAIN ANALYZE *executes* it — fine for the SELECTs here, but

remember it really runs DML if you ever point it at an INSERT/UPDATE/DELETE.

Quick start (10 minutes)

Follow this guide to get SQL Query Optimization Guide up and running in your environment.

Quick start (10 minutes)

# --- PostgreSQL ---
createdb sqlopt_demo
# Open examples/sample_schema.sql, keep the PostgreSQL seed block (Block A), then:
psql -d sqlopt_demo -f examples/sample_schema.sql

# Read your first plan: a full scan before any index on customer_id.
psql -d sqlopt_demo -c "EXPLAIN (ANALYZE, BUFFERS) \
  SELECT id, status, total_cents FROM orders WHERE customer_id = 12345;"

# Work through the lessons in order (they reference the same schema):
psql -d sqlopt_demo -f sql/01_explain_plan_basics.sql

# --- MySQL / MariaDB ---
mysql -e "CREATE DATABASE sqlopt_demo;"
# Open examples/sample_schema.sql, keep the MySQL seed block (Block B), then:
mysql sqlopt_demo < examples/sample_schema.sql

mysql sqlopt_demo -e "EXPLAIN \
  SELECT id, status, total_cents FROM orders WHERE customer_id = 12345;"

Then read docs/READING-EXECUTION-PLANS.md alongside sql/01 — that pairing is

the foundation everything else builds on.

File-by-file guide

Start here

• sql/01 + READING-EXECUTION-PLANS.md — how to read a plan in both engines,

the estimate-vs-actual mental model, the loops multiplier everyone misreads,

and the five questions to ask every plan. Don't skip this; the rest assumes it.

The two levers that fix most queries

• sql/02 + INDEX-DESIGN.md — selectivity (will an index even help?), the

leftmost-prefix rule, composite column ordering, covering/index-only scans, and

partial/expression indexes. The decision framework, not a list of CREATE INDEX

statements.

• sql/03 (sargability) — the unforgiving rule that a wrapped column can't be

sought, and the rewrite for each way people accidentally break it: functions,

arithmetic, implicit casts, leading wildcards, OR, and the NOT IN/NULL trap.

Joins, windows, rewrites

• sql/04 + JOIN-STRATEGIES.md — the three physical joins, when each is

*correct* (a hash join on a big aggregate is right, not a bug), and how to steer

the optimizer with stats and indexes rather than hints.

• sql/05 + WINDOW-FUNCTIONS.md — running totals, ranking, top-N-per-group,

dedup, and LAG/LEAD, each replacing an O(n²) self-join or correlated

subquery.

• sql/06 + QUERY-REWRITE-PATTERNS.md — logically-identical rewrites that

run far cheaper: correlated subquery → join, IN/EXISTS/JOIN, UNION →

UNION ALL, predicate push-down, and the CTE materialization trap.

Mistakes and pagination

• sql/07 + ANTI-PATTERNS.md — the field guide. Smell → why it hurts → fix,

with a one-screen quick-reference table to scan during a review.

• sql/08 (pagination) — why deep OFFSET melts under load and how keyset /

seek pagination makes every page cost the same, including the index that makes

it O(1) and how to handle total counts cheaply.

The test bed

• examples/sample_schema.sql — a portable e-commerce schema (~20k customers,

~100k orders, ~200k line items) with separate PostgreSQL and MySQL seed blocks.

A couple of indexes are deliberately *left out* so the lessons can add them and

measure the difference.