MySQL Query Plan

Query scans 2 tables and joins via block nested-loop Learn →; returns 150 rows in 16 ms. Main finding: Block Nested-Loop Learn → join — each batch of outer rows triggers a full scan of the inner table.

Total time: 15.50 ms
Rows returned: 150
Rows examined: 250
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 t1.id, t1.name, t2.amount
from testdb.t1
  join testdb.t2

Execution plan (diagram)

Click a plan operator to inspect details.

Warnings (2)

WARN
Full table scan: t1 (100 rows), t2 (150 rows)

Labeled: Table scan [t1] Table scan [t2]
WARN
Block Nested-Loop Learn → (BNL Learn →) join buffer detected — uses join_buffer_size (Extra: 'Using join buffer (Block Nested Loop)Learn →', type ALL/index/range)

Labeled: Table scan [t2]

Suggestions (2)

HIGH
Add indexes on filter/join columns to avoid full table scans
MEDIUM
Add indexes to eliminate BNL Learn → full/index scans, or increase join_buffer_size. In MySQL 8.0.20+, set block_nested_loop Learn →=off to force hash join Learn → instead

Glossary (5 terms)

block nested loop: MySQL has no index to look up matching rows, so it reads a chunk of the first table into memory and scans the second table once per chunk. This is slow on big tables. The real fix is almost always to add an index on the join column — that converts the join into a direct lookup and the chunking goes away.; BNL: signalled by ``Using join buffer (Block Nested Loop)`` in the Extra column. MySQL fills ``join_buffer_size`` with outer rows, then scans the inner table (type=ALL/index/range) once for the whole batch. Cost grows with the outer size: O(outer/batch × inner). On MySQL 8.0.20+ the executor **rewrites this BNL to a hash join at runtime** (sql/sql_executor.cc), so the label is slightly misleading — you are usually already running hash. The ``hash_join`` optimizer_switch is defined but never checked by the planner and is effectively a no-op; setting ``block_nested_loop=off`` would kill the BNL→hash rewrite, not enable it. The real fix is an index on the join column (converts the join into eq_ref / ref) or a bigger ``join_buffer_size`` so the hash build side fits. MariaDB uses ``join_cache_hashed`` + ``join_cache_level >= 3`` to select a hashed join buffer.
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.
index range scan: MySQL is reading a chunk of the index that matches your range filter (like a date range) instead of the whole thing. The narrower the range, the faster.; Access type ``range``: reads a bounded key range of a secondary index (e.g. ``WHERE created_at BETWEEN a AND b``). Fast when the range is narrow; degrades to a full scan when the range covers most of the index. With ``mrr=on`` the row-ID fetches can be reordered to turn random I/O into sequential.
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

Raw sidecar (JSON)

{
  "$schema": "https://myflames.dev/schemas/sidecar-v1.json",
  "schema_version": "1.3",
  "generated_at": "2026-04-25T12:51:23Z",
  "myflames_version": "1.5.0",
  "source": {
    "type": "file"
  },
  "plan_summary": {
    "total_time_ms": 15.5,
    "rows_sent": 150,
    "rows_examined_estimate": 250,
    "operator_count": 3,
    "max_depth": 2
  },
  "optimizer_switches": [
    {
      "name": "block_nested_loop",
      "value": "on",
      "explanation": "Buffers a batch of outer rows in a join buffer and scans the inner table once per batch instead of once per row. Inner still does a full/index/range scan — a missing index on the join column is the root cause.",
      "node_labels": [
        "Table scan [t2]"
      ]
    }
  ],
  "warnings": [
    {
      "severity": "warn",
      "category": "full_scan",
      "text": "Full table scan: t1 (100 rows), t2 (150 rows)",
      "source": "plan",
      "node_labels": [
        "Table scan [t1]",
        "Table scan [t2]"
      ]
    },
    {
      "severity": "warn",
      "category": "bnl",
      "text": "Block Nested-Loop (BNL) join buffer detected — uses join_buffer_size (Extra: 'Using join buffer (Block Nested Loop)', type ALL/index/range)",
      "source": "plan",
      "node_labels": [
        "Table scan [t2]"
      ]
    }
  ],
  "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": "Add indexes to eliminate BNL full/index scans, or increase join_buffer_size. In MySQL 8.0.20+, set block_nested_loop=off to force hash join instead",
      "source": "plan",
      "target_variable": "join_buffer_size"
    }
  ],
  "executive_summary": "Query scans 2 tables and joins via block nested-loop; returns 150 rows in 16 ms. Main finding: Block Nested-Loop join — each batch of outer rows triggers a full scan of the inner table.",
  "plan_tree": {
    "node_id": "n:4ee53c8ca1ad",
    "short_label": "Nested loop inner join",
    "folded_label": "NESTED LOOP INNER starts=1 rows=150",
    "children": [
      {
        "node_id": "n:aa61800acadb",
        "short_label": "Table scan [t1]",
        "folded_label": "TABLE SCAN [t1] starts=1 rows=100",
        "children": []
      },
      {
        "node_id": "n:0ac48c48fb1d",
        "short_label": "Table scan [t2]",
        "folded_label": "TABLE SCAN [t2] starts=100 rows=150",
        "children": []
      }
    ]
  },
  "query": {
    "raw": "/* select#1 */ select t1.id, t1.name, t2.amount from testdb.t1 join testdb.t2",
    "beautified": "select t1.id, t1.name, t2.amount\nfrom testdb.t1\n  join testdb.t2"
  },
  "primary_action": {
    "ref": "suggestions[0]"
  },
  "teach_hooks": [
    {
      "lesson": "nested_loop",
      "match": {
        "folded_label": "NESTED LOOP INNER starts=1 rows=150",
        "short_label": "Nested loop inner join"
      },
      "controls": {
        "outer_rows": 100,
        "inner_rows": 15000,
        "row_size": 200
      },
      "note": "Nested loop inner join",
      "query_sql": "/* select#1 */ select t1.id, t1.name, t2.amount from testdb.t1 join testdb.t2"
    },
    {
      "lesson": "full_scan",
      "match": {
        "folded_label": "TABLE SCAN [t1] starts=1 rows=100",
        "short_label": "Table scan [t1]"
      },
      "controls": {
        "rows": 100,
        "row_size": 200,
        "selectivity": 100.0
      },
      "note": "Table scan on t1 (t1)",
      "query_sql": "/* select#1 */ select t1.id, t1.name, t2.amount from testdb.t1 join testdb.t2"
    },
    {
      "lesson": "bnl",
      "match": {
        "folded_label": "TABLE SCAN [t2] starts=100 rows=150",
        "short_label": "Table scan [t2]"
      },
      "controls": {
        "outer_rows": 15000,
        "inner_rows": 15000,
        "row_size": 200
      },
      "note": "Table scan on t2 (BNL join buffer) (t2)",
      "query_sql": "/* select#1 */ select t1.id, t1.name, t2.amount from testdb.t1 join testdb.t2"
    }
  ],
  "operator_complexities": [
    {
      "node_id": "n:4ee53c8ca1ad",
      "folded_label": "NESTED LOOP INNER starts=1 rows=150",
      "short_label": "Nested loop inner join",
      "complexity": {
        "big_o": "O(n · m)",
        "short": "n · m",
        "severity": "bad",
        "rationale": "Unindexed nested loop: every outer row drives a full scan of the inner side. This is the classic O(n²)-class blow-up.",
        "confidence": "exact",
        "learn_more": "nested_loop_join"
      }
    },
    {
      "node_id": "n:aa61800acadb",
      "folded_label": "TABLE SCAN [t1] starts=1 rows=100",
      "short_label": "Table scan [t1]",
      "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:0ac48c48fb1d",
      "folded_label": "TABLE SCAN [t2] starts=100 rows=150",
      "short_label": "Table scan [t2]",
      "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 (diagram)

Warnings (2)

Suggestions (2)

myteach — interactive algorithm catalog

Lessons relevant to this plan

Raw sidecar (JSON)

Nested Loop Join — outer row drives inner probeMySQL 8.4 • MariaDB 11.4

Join shape controls

Nested loop cost model

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

Parameters (full table scan cost model)

Cost readout (full table scan)

Block Nested Loop join — MariaDB's defaultMariaDB 11.4

Parameters (MariaDB 11.x Block Nested Loop)

Cost readout (MariaDB 11.x BNL)

Teach: Operator deep dive

Summary

SQL

Execution plan (diagram)

Warnings (2)

Suggestions (2)

myteach — interactive algorithm catalog

Lessons relevant to this plan

Raw sidecar (JSON)