← Back to all products

SQL Query Optimization Guide

$29

Query analysis techniques, execution plan reading, index strategies, partitioning patterns, and anti-pattern catalog.

📁 23 files
SQLMarkdownPostgreSQL

📄 Product Preview

Try the interactive reader and demo tools below, or get the full product with all content unlocked.

📖 Interactive Reader (Free Preview) ⚙ Try Demo Tools 📦 Download Free Sample

📁 File Structure 23 files

sql-query-optimization/ ├── LICENSE ├── README.md ├── docs/ │ ├── ANTI-PATTERNS.md │ ├── INDEX-DESIGN.md │ ├── JOIN-STRATEGIES.md │ ├── QUERY-REWRITE-PATTERNS.md │ ├── READING-EXECUTION-PLANS.md │ └── WINDOW-FUNCTIONS.md ├── examples/ │ └── sample_schema.sql ├── free-sample.zip ├── guide/ │ ├── 01_what-s-included.md │ ├── 02_quick-start-10-minutes.md │ ├── 03_how-to-use-this-kit.md │ └── 04_license.md ├── index.html └── sql/ ├── 01_explain_plan_basics.sql ├── 02_index_design.sql ├── 03_sargable_predicates.sql ├── 04_join_strategies.sql ├── 05_window_functions.sql ├── 06_query_rewrites.sql ├── 07_antipatterns.sql └── 08_pagination.sql

📖 Documentation Preview README excerpt

SQL Query Optimization

A hands-on, vendor-neutral guide to making SQL queries fast and keeping them

fast. Not a reference manual to memorize — a set of runnable .sql files and

field-tested runbooks that teach the one loop every optimization comes down to:

**read the execution plan, find the expensive step, change one thing, re-read the

plan.** Every technique is shown on a real schema with before/after plans and the

reasoning behind each rewrite.

Written for PostgreSQL 12+ and MySQL 8.0 / MariaDB 10.5+, with the dialect

differences (and there are real ones — merge joins, INCLUDE, functional indexes,

descending indexes) called out inline as you meet them. Nothing here is

ORM-specific or framework-specific; it's the SQL underneath all of them.

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.

... continues with setup instructions, usage examples, and more.

📄 Code Sample .sql preview

examples/sample_schema.sql -- ============================================================================= -- examples/sample_schema.sql -- A portable e-commerce schema + seed data for the optimization examples. -- ----------------------------------------------------------------------------- -- The optimization examples in sql/ are vendor-neutral, so this schema runs on -- BOTH PostgreSQL and MySQL/MariaDB. The DDL below is portable. Seeding bulk -- data is the one place the engines differ, so there are TWO seed blocks -- -- run the DDL, then run the ONE seed block that matches your engine. -- -- PostgreSQL: createdb sqlopt_demo -- psql -d sqlopt_demo -f examples/sample_schema.sql # edit: keep PG block -- MySQL 8.0: mysql -e "CREATE DATABASE sqlopt_demo;" -- mysql sqlopt_demo < examples/sample_schema.sql # edit: keep MySQL block -- -- By design, a couple of indexes are LEFT OUT so the examples can demonstrate -- adding them and measuring the difference. -- ============================================================================= -- ============================================================================= -- PORTABLE DDL (runs on PostgreSQL 12+, MySQL 8.0+, MariaDB 10.5+) -- ============================================================================= DROP TABLE IF EXISTS order_items; DROP TABLE IF EXISTS orders; DROP TABLE IF EXISTS products; DROP TABLE IF EXISTS customers; CREATE TABLE customers ( id INTEGER NOT NULL PRIMARY KEY, email VARCHAR(255) NOT NULL, full_name VARCHAR(120) NOT NULL, country CHAR(2) NOT NULL, created_at TIMESTAMP NOT NULL, is_active SMALLINT NOT NULL DEFAULT 1 ); CREATE TABLE products ( id INTEGER NOT NULL PRIMARY KEY, sku VARCHAR(32) NOT NULL, name VARCHAR(160) NOT NULL, category VARCHAR(32) NOT NULL, price_cents INTEGER NOT NULL ); -- orders.customer_id intentionally has NO secondary index (the examples add it). CREATE TABLE orders ( id INTEGER NOT NULL PRIMARY KEY, customer_id INTEGER NOT NULL, status VARCHAR(16) NOT NULL, total_cents INTEGER NOT NULL, # ... 105 more lines ...
Buy Now — $29 Back to Products