🗄️ Database Administration — SQL Query Optimization Demo

• **PostgreSQL 12+** *or* **MySQL 8.0 / MariaDB 10.5+**, and its standard CLI
• A scratch database you can create and drop. The examples build and seed
• Comfort reading SQL. This is intermediate material: it assumes you know
• **Window functions (`sql/05`) require MySQL 8.0+ / MariaDB 10.2+** — not MySQL
• **`sql/01` + `READING-EXECUTION-PLANS.md`** — how to read a plan in both engines,
• **`sql/02` + `INDEX-DESIGN.md`** — selectivity (will an index even help?), the

# --- 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;"

# --- 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