MySQL Query Plan

Query scans 2 tables and hash-joins 1 pair; returns 12,000 rows in 8.2 ms. Main finding: hash join Learn → uses join_buffer_size; an index on the join column would be faster.

Total time: 8.25 ms
Rows returned: 12.00K
Rows examined: 15.00K
Operators: 3

Fix first

Primary recommendation

Add indexes on filter/join columns to avoid full table scans

Why does this help?

An index turns a full-table scan into a direct lookup — MySQL jumps straight to the matching rows instead of reading every row and discarding the ones that don't match. For joins, the benefit compounds: a nested loop over N outer rows that had to scan the inner table becomes N fast lookups.

SQL

select u.name AS name, o.total AS total
from testdb.users u
  join testdb.orders o on (o.user_id = u.id)

Execution plan (tree)

Click a plan operator to inspect details.

Warnings (2)

WARN
Full table scan: users (3000 rows), orders (12000 rows)

Labeled: Table scan [users] Table scan [orders]
WARN
1 hash join Learn →(s) — uses join_buffer_size (~12000 rows in build phase)

Labeled: Inner hash join (orders.user_id = users.id)

Suggestions (2)

HIGH
Add indexes on filter/join columns to avoid full table scans
MEDIUM
Increase join_buffer_size if hash joins are slow or spill to disk

Glossary (3 terms)

full table scan: MySQL is reading every single row in this table, one by one, to check if it matches your WHERE clause. This is slow on big tables — an index on the filter column lets MySQL jump straight to the matching rows instead.; Access type ``ALL``: the storage engine returns every row of the table, applying the WHERE clause in the server layer. Cost is roughly (rows × row_evaluate_cost) + pages_read × io_block_read_cost. Signal: ``type=ALL`` in EXPLAIN, ``Handler_read_rnd_next`` climbs by the table row count. Almost always a sign of a missing index on the WHERE or join column.
hash join: MySQL builds a lookup table in memory for the smaller of the two tables, then reads the bigger one once and uses the lookup table to find matches. Much faster than the alternative (Block Nested-Loop) when neither side has a usable index.; MySQL 8.0.18+ (default in 8.0.20+). Reads the smaller input, builds an in-memory hash table keyed on the join column, then scans the larger input and probes the hash. Single pass over each side (O(m + n)). Uses ``join_buffer_size``; if the build side doesn't fit, MySQL spills to tmpdir and does a multi-pass (Grace) hash join, re-reading the probe side each pass.
join buffer size: The memory MySQL uses when it has to join tables without a handy index. If this is too small for a big join, MySQL spills to disk and slows down.; Per-join allocation, freed after the join completes. Feeds the build side of a hash join or the outer-row batches of a BNL. If the build side of a hash join doesn't fit, MySQL spills to a multi-pass (Grace) hash join. An index on the join column is almost always a bigger win than raising this.

myteach — interactive algorithm catalog

Every operator shown above has a hands-on lesson with a cost-model slider and an animated walk-through. The catalog is the central index — bookmark it once, revisit per query:

Open the full catalog →

Lessons relevant to this plan

hash join

Raw sidecar (JSON)

{
  "$schema": "https://myflames.dev/schemas/sidecar-v1.json",
  "schema_version": "1.3",
  "generated_at": "2026-04-25T12:51:24Z",
  "myflames_version": "1.5.0",
  "source": {
    "type": "file"
  },
  "plan_summary": {
    "total_time_ms": 8.25,
    "rows_sent": 12000,
    "rows_examined_estimate": 15000,
    "operator_count": 3,
    "max_depth": 2
  },
  "optimizer_switches": [
    {
      "name": "hash_join",
      "value": "on",
      "explanation": "Builds an in-memory hash table on the smaller (build) input and probes it from the other side. Uses join_buffer_size; spills to a tmp file (and tmpdir) if the build side does not fit.",
      "node_labels": [
        "Inner hash join (orders.user_id = users.id)"
      ]
    }
  ],
  "warnings": [
    {
      "severity": "warn",
      "category": "full_scan",
      "text": "Full table scan: users (3000 rows), orders (12000 rows)",
      "source": "plan",
      "node_labels": [
        "Table scan [users]",
        "Table scan [orders]"
      ]
    },
    {
      "severity": "warn",
      "category": "hash_join",
      "text": "1 hash join(s) — uses join_buffer_size (~12000 rows in build phase)",
      "source": "plan",
      "node_labels": [
        "Inner hash join (orders.user_id = users.id)"
      ]
    }
  ],
  "suggestions": [
    {
      "severity": "high",
      "category": "index",
      "action": "Add indexes on filter/join columns to avoid full table scans",
      "source": "plan"
    },
    {
      "severity": "medium",
      "category": "tuning_variable",
      "action": "Increase join_buffer_size if hash joins are slow or spill to disk",
      "source": "plan",
      "target_variable": "join_buffer_size"
    }
  ],
  "executive_summary": "Query scans 2 tables and hash-joins 1 pair; returns 12,000 rows in 8.2 ms. Main finding: hash join uses join_buffer_size; an index on the join column would be faster.",
  "plan_tree": {
    "node_id": "n:d017511b6657",
    "short_label": "Inner hash join (orders.user_id = users.id)",
    "folded_label": "Inner hash join (orders.user_id = users.id) starts=1 rows=12000",
    "children": [
      {
        "node_id": "n:f78dc713f35d",
        "short_label": "Table scan [users]",
        "folded_label": "TABLE SCAN [users] starts=1 rows=3000",
        "children": []
      },
      {
        "node_id": "n:ec47220d7a5f",
        "short_label": "Table scan [orders]",
        "folded_label": "TABLE SCAN [orders] starts=1 rows=12000",
        "children": []
      }
    ]
  },
  "query": {
    "raw": "/* select#1 */ select `u`.`name` AS `name`, `o`.`total` AS `total` from `testdb`.`users` `u` join `testdb`.`orders` `o` on (`o`.`user_id` = `u`.`id`)",
    "beautified": "select u.name AS name, o.total AS total\nfrom testdb.users u\n  join testdb.orders o on (o.user_id = u.id)"
  },
  "primary_action": {
    "ref": "suggestions[0]"
  },
  "teach_hooks": [
    {
      "lesson": "hash",
      "match": {
        "folded_label": "Inner hash join (orders.user_id = users.id) starts=1 rows=12000",
        "short_label": "Inner hash join (orders.user_id = users.id)"
      },
      "controls": {
        "build_rows": 3000,
        "probe_rows": 12000,
        "row_size": 200
      },
      "note": "Inner hash join (orders.user_id = users.id)",
      "query_sql": "/* select#1 */ select `u`.`name` AS `name`, `o`.`total` AS `total` from `testdb`.`users` `u` join `testdb`.`orders` `o` on (`o`.`user_id` = `u`.`id`)"
    },
    {
      "lesson": "full_scan",
      "match": {
        "folded_label": "TABLE SCAN [users] starts=1 rows=3000",
        "short_label": "Table scan [users]"
      },
      "controls": {
        "rows": 3000,
        "row_size": 200,
        "selectivity": 100.0
      },
      "note": "Table scan on users (users)",
      "query_sql": "/* select#1 */ select `u`.`name` AS `name`, `o`.`total` AS `total` from `testdb`.`users` `u` join `testdb`.`orders` `o` on (`o`.`user_id` = `u`.`id`)"
    },
    {
      "lesson": "full_scan",
      "match": {
        "folded_label": "TABLE SCAN [orders] starts=1 rows=12000",
        "short_label": "Table scan [orders]"
      },
      "controls": {
        "rows": 12000,
        "row_size": 200,
        "selectivity": 100.0
      },
      "note": "Table scan on orders (hash build) (orders)",
      "query_sql": "/* select#1 */ select `u`.`name` AS `name`, `o`.`total` AS `total` from `testdb`.`users` `u` join `testdb`.`orders` `o` on (`o`.`user_id` = `u`.`id`)"
    }
  ],
  "operator_complexities": [
    {
      "node_id": "n:d017511b6657",
      "folded_label": "Inner hash join (orders.user_id = users.id) starts=1 rows=12000",
      "short_label": "Inner hash join (orders.user_id = users.id)",
      "complexity": {
        "big_o": "O(n + m)",
        "short": "n + m",
        "severity": "good",
        "rationale": "Hash join: builds a hash on one side (O(m)), streams the other past it (O(n)). Disk-spill does NOT change the asymptotic class — only the constants.",
        "confidence": "exact",
        "learn_more": "hash_join"
      }
    },
    {
      "node_id": "n:f78dc713f35d",
      "folded_label": "TABLE SCAN [users] starts=1 rows=3000",
      "short_label": "Table scan [users]",
      "complexity": {
        "big_o": "O(n)",
        "short": "n",
        "severity": "medium",
        "rationale": "Full table scan: the storage engine returns every row; cost scales with the table size.",
        "confidence": "exact",
        "learn_more": "full_table_scan"
      }
    },
    {
      "node_id": "n:ec47220d7a5f",
      "folded_label": "TABLE SCAN [orders] starts=1 rows=12000",
      "short_label": "Table scan [orders]",
      "complexity": {
        "big_o": "O(n)",
        "short": "n",
        "severity": "medium",
        "rationale": "Full table scan: the storage engine returns every row; cost scales with the table size.",
        "confidence": "exact",
        "learn_more": "full_table_scan"
      }
    }
  ]
}

Primary recommendation

SQL

Execution plan (tree)

Warnings (2)

Suggestions (2)

myteach — interactive algorithm catalog

Lessons relevant to this plan

Raw sidecar (JSON)

Hash join — build, probe, and grace-hash spillMySQL 8.4 • MariaDB 11.4

Parameters (MySQL 8.4 hash join: employees ⋈ departments)

Cost readout (MySQL 8.4 hash join)

Full table scan — why MySQL reads every rowMySQL 8.4 • MariaDB 11.4

Parameters (full table scan cost model)

Cost readout (full table scan)

Teach: Operator deep dive

Summary

SQL

Execution plan (tree)

Warnings (2)

Suggestions (2)

myteach — interactive algorithm catalog

Lessons relevant to this plan

Raw sidecar (JSON)