MySQL Query Plan

Query scans 2 tables, materializes 1 temp table Learn → and sorts the result; examines ~601 rows to return 10 in 1.6 ms. Main finding: full scan of org_chart (1,000 rows).

Total time: 1.55 ms
Rows returned: 10
Rows examined: 601
Operators: 10

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

with recursive org_chart as (/*
select#2 */
select demodb.employees.id AS id,demodb.employees.name AS name,demodb.employees.manager_id AS manager_id,1 AS level
from demodb.employees
where (demodb.employees.manager_id is null)
union all /*
select#3 */
select e.id AS id,e.name AS name,e.manager_id AS manager_id,(oc.level + 1) AS oc.level + 1
from demodb.employees e
  join org_chart oc
where ((e.manager_id = oc.id) and (oc.level < 10))) /*
select#1 */
select org_chart.level AS level,count(0) AS headcount
from org_chart
group by org_chart.level
order by org_chart.level

Execution plan (tree)

Click a plan operator to inspect details.

Warnings (3)

WARN
Full table scan: <temporary> (10 rows), org_chart (1000 rows)

Labeled: Table scan [<temporary>] Table scan [org_chart]
WARN
1 temp table Learn →(s) (Materialize) — up to 1000 rows; may spill to disk

Labeled: Materialize
WARN
1 sort operation Learn →(s) — 10 rows; may use disk-based filesort Learn →

Labeled: Sort

Suggestions (3)

HIGH
Add indexes on filter/join columns to avoid full table scans
MEDIUM
Increase tmp_table_size / max_heap_table_size to keep temp tables in memory
MEDIUM
Increase sort_buffer_size or add an ordered index to avoid filesort Learn →

Glossary (8 terms)

covering index: The index has all the columns the query needs, so MySQL never has to open the main table at all. This is the fastest form of SELECT on a large table.; The optimizer finds every column referenced by the query (SELECT list, WHERE, ORDER BY) in the secondary index itself, so no row fetch from the clustered index is needed. Reported in MySQL with ``Using index`` in the Extra column. In InnoDB, a secondary index always includes the primary key columns, which is why ``use_index_extensions=on`` enables more covering reads.
derived table: A query-inside-a-query that MySQL treats like a temporary table. MySQL often rewrites it to be part of the outer query for speed, but sometimes it can't and has to materialize it.; ``SELECT … FROM (SELECT …) AS t``: the inner SELECT is a derived table. With ``derived_merge=on`` (default) the optimizer folds it into the outer query when possible; otherwise it's materialized. With ``derived_condition_pushdown=on``, outer predicates can be pushed inside the derived query block to reduce its row count.
filesort: MySQL has to sort the rows by hand because the data isn't already in the order you asked for. Small sorts stay in memory; big ones use disk and get slow.; MySQL sorts the result in the server layer using ``sort_buffer_size``. If the sort set fits, it stays in memory (fast quicksort). If not, MySQL writes sorted runs to tmpdir and k-way merges them — disk I/O plus CPU for the merge. An index in the ``ORDER BY`` order eliminates the filesort entirely.
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.
materialization: MySQL ran your subquery or derived table once, stored the result in a temporary table, and then used that temp table instead of re-running the subquery for every outer row.; The subquery result is materialized into an internal temp table (MEMORY or on-disk InnoDB depending on size). The outer query reads from the temp table. Controlled by ``tmp_table_size`` and ``max_heap_table_size`` — MySQL always uses the smaller of the two. If the temp table exceeds that limit, MySQL converts to an on-disk temp table and performance drops sharply.
sort buffer size: The memory MySQL gets per connection to sort rows. If your sort is bigger than this, MySQL falls back to using disk, which is slower.; Allocated lazily per connection, freed after the sort. When a filesort's data exceeds this buffer, MySQL writes sorted runs to tmpdir and k-way merges them back — disk I/O. Because it's per-connection, setting this high globally multiplies by max_connections; prefer ``SET SESSION sort_buffer_size = …`` for individual big queries.
temp table: MySQL built a scratch table in memory to help process your query. If the scratch table gets too big, it moves to disk and slows down a lot.; Used for materialized subqueries, GROUP BY with no suitable index, DISTINCT, and some ORDER BY cases. MEMORY/TempTable engine while it fits in ``min(tmp_table_size, max_heap_table_size)``, converted to on-disk InnoDB when it doesn't. The on-disk version is typically 10–100× slower.
tmp table size: How big MySQL's scratch tables can get before they move from RAM to disk. If your query builds bigger scratch tables than this, expect a big slowdown — and you have to raise ``max_heap_table_size`` at the same time, otherwise it does nothing.; The effective limit is ``min(tmp_table_size, max_heap_table_size)``. Internal temp tables (from materialized subqueries, GROUP BY, DISTINCT, etc.) are MEMORY-engine until they exceed this, at which point MySQL converts them to on-disk InnoDB temp tables (10–100× slower for scans). Both variables must be raised together — raising only one has no effect.

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:26Z",
  "myflames_version": "1.5.0",
  "source": {
    "type": "file"
  },
  "plan_summary": {
    "total_time_ms": 1.551,
    "rows_sent": 10,
    "rows_examined_estimate": 601,
    "operator_count": 10,
    "max_depth": 8
  },
  "optimizer_switches": [
    {
      "name": "index_condition_pushdown",
      "value": "on",
      "explanation": "Parts of the WHERE clause are evaluated inside the storage engine using index columns before fetching the full row — reduces rows handed to the server layer.",
      "node_labels": [
        "Index lookup [employees.idx_mgr]"
      ]
    },
    {
      "name": "materialization",
      "value": "on",
      "explanation": "A subquery or derived table is executed once and its result stored in a temporary table for reuse. Watch tmp_table_size / max_heap_table_size — it may spill to disk.",
      "node_labels": [
        "Materialize"
      ]
    }
  ],
  "warnings": [
    {
      "severity": "warn",
      "category": "full_scan",
      "text": "Full table scan: <temporary> (10 rows), org_chart (1000 rows)",
      "source": "plan",
      "node_labels": [
        "Table scan [<temporary>]",
        "Table scan [org_chart]"
      ]
    },
    {
      "severity": "warn",
      "category": "temp_table",
      "text": "1 temp table(s) (Materialize) — up to 1000 rows; may spill to disk",
      "source": "plan",
      "node_labels": [
        "Materialize"
      ]
    },
    {
      "severity": "warn",
      "category": "filesort",
      "text": "1 sort operation(s) — 10 rows; may use disk-based filesort",
      "source": "plan",
      "node_labels": [
        "Sort"
      ]
    }
  ],
  "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 tmp_table_size / max_heap_table_size to keep temp tables in memory",
      "source": "plan",
      "target_variable": "tmp_table_size"
    },
    {
      "severity": "medium",
      "category": "tuning_variable",
      "action": "Increase sort_buffer_size or add an ordered index to avoid filesort",
      "source": "plan",
      "target_variable": "sort_buffer_size"
    }
  ],
  "executive_summary": "Query scans 2 tables, materializes 1 temp table and sorts the result; examines ~601 rows to return 10 in 1.6 ms. Main finding: full scan of org_chart (1,000 rows).",
  "plan_tree": {
    "node_id": "n:347eeced0b3c",
    "short_label": "Sort",
    "folded_label": "SORT starts=1 rows=10",
    "children": [
      {
        "node_id": "n:ca439ce5f715",
        "short_label": "Table scan [<temporary>]",
        "folded_label": "TABLE SCAN [<temporary>] starts=1 rows=10",
        "children": [
          {
            "node_id": "n:604ff20c53db",
            "short_label": "Aggregate",
            "folded_label": "AGGREGATE starts=1 rows=10",
            "children": [
              {
                "node_id": "n:23956a72ce60",
                "short_label": "Table scan [org_chart]",
                "folded_label": "TABLE SCAN [org_chart] starts=1 rows=1000",
                "children": [
                  {
                    "node_id": "n:0d118da99553",
                    "short_label": "Materialize",
                    "folded_label": "MATERIALIZE starts=1 rows=1000",
                    "children": [
                      {
                        "node_id": "n:690d9483b0a5",
                        "short_label": "Index lookup [employees.idx_mgr]",
                        "folded_label": "INDEX LOOKUP [employees.idx_mgr] starts=1 rows=100",
                        "children": []
                      },
                      {
                        "node_id": "n:b76c70582649",
                        "short_label": "Nested loop inner join",
                        "folded_label": "NESTED LOOP INNER starts=2 rows=450",
                        "children": [
                          {
                            "node_id": "n:e0f25f0beb5a",
                            "short_label": "Filter: (((oc.level < 10) and (oc.id is not null)))",
                            "folded_label": "FILTER (((oc.level < 10) and (oc.id is not null))) starts=2 rows=450",
                            "children": [
                              {
                                "node_id": "n:621a4de55271",
                                "short_label": "Scan new records on oc",
                                "folded_label": "Scan new records on oc starts=2 rows=500",
                                "children": []
                              }
                            ]
                          },
                          {
                            "node_id": "n:911d971460cb",
                            "short_label": "Index lookup [employees.idx_mgr]",
                            "folded_label": "INDEX LOOKUP [employees.idx_mgr] starts=900 rows=1",
                            "children": []
                          }
                        ]
                      }
                    ]
                  }
                ]
              }
            ]
          }
        ]
      }
    ]
  },
  "query": {
    "raw": "with recursive `org_chart` as (/* select#2 */ select `demodb`.`employees`.`id` AS `id`,`demodb`.`employees`.`name` AS `name`,`demodb`.`employees`.`manager_id` AS `manager_id`,1 AS `level` from `demodb`.`employees` where (`demodb`.`employees`.`manager_id` is null) union all /* select#3 */ select `e`.`id` AS `id`,`e`.`name` AS `name`,`e`.`manager_id` AS `manager_id`,(`oc`.`level` + 1) AS `oc.level + 1` from `demodb`.`employees` `e` join `org_chart` `oc` where ((`e`.`manager_id` = `oc`.`id`) and (`oc`.`level` < 10))) /* select#1 */ select `org_chart`.`level` AS `level`,count(0) AS `headcount` from `org_chart` group by `org_chart`.`level` order by `org_chart`.`level`",
    "beautified": "with recursive org_chart as (/*\nselect#2 */\nselect demodb.employees.id AS id,demodb.employees.name AS name,demodb.employees.manager_id AS manager_id,1 AS level\nfrom demodb.employees\nwhere (demodb.employees.manager_id is null)\nunion all /*\nselect#3 */\nselect e.id AS id,e.name AS name,e.manager_id AS manager_id,(oc.level + 1) AS oc.level + 1\nfrom demodb.employees e\n  join org_chart oc\nwhere ((e.manager_id = oc.id) and (oc.level < 10))) /*\nselect#1 */\nselect org_chart.level AS level,count(0) AS headcount\nfrom org_chart\ngroup by org_chart.level\norder by org_chart.level"
  },
  "primary_action": {
    "ref": "suggestions[0]"
  },
  "teach_hooks": [
    {
      "lesson": "filesort",
      "match": {
        "folded_label": "SORT starts=1 rows=10",
        "short_label": "Sort"
      },
      "controls": {
        "rows": 10,
        "row_size": 200
      },
      "note": "Sort: org_chart.`level`",
      "query_sql": "with recursive `org_chart` as (/* select#2 */ select `demodb`.`employees`.`id` AS `id`,`demodb`.`employees`.`name` AS `name`,`demodb`.`employees`.`manager_id` AS `manager_id`,1 AS `level` from `demodb`.`employees` where (`demodb`.`employees`.`manager_id` is null) union all /* select#3 */ select `e`.`id` AS `id`,`e`.`name` AS `name`,`e`.`manager_id` AS `manager_id`,(`oc`.`level` + 1) AS `oc.level + 1` from `demodb`.`employees` `e` join `org_chart` `oc` where ((`e`.`manager_id` = `oc`.`id`) and (`oc`.`level` < 10))) /* select#1 */ select `org_chart`.`level` AS `level`,count(0) AS `headcount` from `org_chart` group by `org_chart`.`level` order by `org_chart`.`level`"
    },
    {
      "lesson": "full_scan",
      "match": {
        "folded_label": "TABLE SCAN [<temporary>] starts=1 rows=10",
        "short_label": "Table scan [<temporary>]"
      },
      "controls": {
        "rows": 10,
        "row_size": 200,
        "selectivity": 100.0
      },
      "note": "Table scan on <temporary> (<temporary>)",
      "query_sql": "with recursive `org_chart` as (/* select#2 */ select `demodb`.`employees`.`id` AS `id`,`demodb`.`employees`.`name` AS `name`,`demodb`.`employees`.`manager_id` AS `manager_id`,1 AS `level` from `demodb`.`employees` where (`demodb`.`employees`.`manager_id` is null) union all /* select#3 */ select `e`.`id` AS `id`,`e`.`name` AS `name`,`e`.`manager_id` AS `manager_id`,(`oc`.`level` + 1) AS `oc.level + 1` from `demodb`.`employees` `e` join `org_chart` `oc` where ((`e`.`manager_id` = `oc`.`id`) and (`oc`.`level` < 10))) /* select#1 */ select `org_chart`.`level` AS `level`,count(0) AS `headcount` from `org_chart` group by `org_chart`.`level` order by `org_chart`.`level`"
    },
    {
      "lesson": "tmp",
      "match": {
        "folded_label": "AGGREGATE starts=1 rows=10",
        "short_label": "Aggregate"
      },
      "controls": {
        "rows": 10,
        "row_size": 200
      },
      "note": "Aggregate using temporary table",
      "query_sql": "with recursive `org_chart` as (/* select#2 */ select `demodb`.`employees`.`id` AS `id`,`demodb`.`employees`.`name` AS `name`,`demodb`.`employees`.`manager_id` AS `manager_id`,1 AS `level` from `demodb`.`employees` where (`demodb`.`employees`.`manager_id` is null) union all /* select#3 */ select `e`.`id` AS `id`,`e`.`name` AS `name`,`e`.`manager_id` AS `manager_id`,(`oc`.`level` + 1) AS `oc.level + 1` from `demodb`.`employees` `e` join `org_chart` `oc` where ((`e`.`manager_id` = `oc`.`id`) and (`oc`.`level` < 10))) /* select#1 */ select `org_chart`.`level` AS `level`,count(0) AS `headcount` from `org_chart` group by `org_chart`.`level` order by `org_chart`.`level`"
    },
    {
      "lesson": "full_scan",
      "match": {
        "folded_label": "TABLE SCAN [org_chart] starts=1 rows=1000",
        "short_label": "Table scan [org_chart]"
      },
      "controls": {
        "rows": 1000,
        "row_size": 200,
        "selectivity": 100.0
      },
      "note": "Table scan on org_chart (org_chart)",
      "query_sql": "with recursive `org_chart` as (/* select#2 */ select `demodb`.`employees`.`id` AS `id`,`demodb`.`employees`.`name` AS `name`,`demodb`.`employees`.`manager_id` AS `manager_id`,1 AS `level` from `demodb`.`employees` where (`demodb`.`employees`.`manager_id` is null) union all /* select#3 */ select `e`.`id` AS `id`,`e`.`name` AS `name`,`e`.`manager_id` AS `manager_id`,(`oc`.`level` + 1) AS `oc.level + 1` from `demodb`.`employees` `e` join `org_chart` `oc` where ((`e`.`manager_id` = `oc`.`id`) and (`oc`.`level` < 10))) /* select#1 */ select `org_chart`.`level` AS `level`,count(0) AS `headcount` from `org_chart` group by `org_chart`.`level` order by `org_chart`.`level`"
    },
    {
      "lesson": "tmp",
      "match": {
        "folded_label": "MATERIALIZE starts=1 rows=1000",
        "short_label": "Materialize"
      },
      "controls": {
        "rows": 1000,
        "row_size": 200
      },
      "note": "Materialize recursive CTE org_chart",
      "query_sql": "with recursive `org_chart` as (/* select#2 */ select `demodb`.`employees`.`id` AS `id`,`demodb`.`employees`.`name` AS `name`,`demodb`.`employees`.`manager_id` AS `manager_id`,1 AS `level` from `demodb`.`employees` where (`demodb`.`employees`.`manager_id` is null) union all /* select#3 */ select `e`.`id` AS `id`,`e`.`name` AS `name`,`e`.`manager_id` AS `manager_id`,(`oc`.`level` + 1) AS `oc.level + 1` from `demodb`.`employees` `e` join `org_chart` `oc` where ((`e`.`manager_id` = `oc`.`id`) and (`oc`.`level` < 10))) /* select#1 */ select `org_chart`.`level` AS `level`,count(0) AS `headcount` from `org_chart` group by `org_chart`.`level` order by `org_chart`.`level`"
    },
    {
      "lesson": "non_unique_lookup",
      "match": {
        "folded_label": "INDEX LOOKUP [employees.idx_mgr] starts=1 rows=100",
        "short_label": "Index lookup [employees.idx_mgr]"
      },
      "controls": {
        "rows": 100,
        "selectivity": 40.0,
        "covering": false
      },
      "note": "Index lookup on employees using idx_mgr (manager_id=NULL), with index condition: (employees.manager_id is null) (employees)",
      "query_sql": "with recursive `org_chart` as (/* select#2 */ select `demodb`.`employees`.`id` AS `id`,`demodb`.`employees`.`name` AS `name`,`demodb`.`employees`.`manager_id` AS `manager_id`,1 AS `level` from `demodb`.`employees` where (`demodb`.`employees`.`manager_id` is null) union all /* select#3 */ select `e`.`id` AS `id`,`e`.`name` AS `name`,`e`.`manager_id` AS `manager_id`,(`oc`.`level` + 1) AS `oc.level + 1` from `demodb`.`employees` `e` join `org_chart` `oc` where ((`e`.`manager_id` = `oc`.`id`) and (`oc`.`level` < 10))) /* select#1 */ select `org_chart`.`level` AS `level`,count(0) AS `headcount` from `org_chart` group by `org_chart`.`level` order by `org_chart`.`level`"
    },
    {
      "lesson": "nested_loop",
      "match": {
        "folded_label": "NESTED LOOP INNER starts=2 rows=450",
        "short_label": "Nested loop inner join"
      },
      "controls": {
        "outer_rows": 900,
        "inner_rows": 900,
        "row_size": 200
      },
      "note": "Nested loop inner join",
      "query_sql": "with recursive `org_chart` as (/* select#2 */ select `demodb`.`employees`.`id` AS `id`,`demodb`.`employees`.`name` AS `name`,`demodb`.`employees`.`manager_id` AS `manager_id`,1 AS `level` from `demodb`.`employees` where (`demodb`.`employees`.`manager_id` is null) union all /* select#3 */ select `e`.`id` AS `id`,`e`.`name` AS `name`,`e`.`manager_id` AS `manager_id`,(`oc`.`level` + 1) AS `oc.level + 1` from `demodb`.`employees` `e` join `org_chart` `oc` where ((`e`.`manager_id` = `oc`.`id`) and (`oc`.`level` < 10))) /* select#1 */ select `org_chart`.`level` AS `level`,count(0) AS `headcount` from `org_chart` group by `org_chart`.`level` order by `org_chart`.`level`"
    },
    {
      "lesson": "filter",
      "match": {
        "folded_label": "FILTER (((oc.level < 10) and (oc.id is not null))) starts=2 rows=450",
        "short_label": "Filter: (((oc.level < 10) and (oc.id is not null)))"
      },
      "controls": {
        "input_rows": 900,
        "selectivity": 10.0
      },
      "note": "Filter: ((oc.`level` < 10) and (oc.id is not null))",
      "query_sql": "with recursive `org_chart` as (/* select#2 */ select `demodb`.`employees`.`id` AS `id`,`demodb`.`employees`.`name` AS `name`,`demodb`.`employees`.`manager_id` AS `manager_id`,1 AS `level` from `demodb`.`employees` where (`demodb`.`employees`.`manager_id` is null) union all /* select#3 */ select `e`.`id` AS `id`,`e`.`name` AS `name`,`e`.`manager_id` AS `manager_id`,(`oc`.`level` + 1) AS `oc.level + 1` from `demodb`.`employees` `e` join `org_chart` `oc` where ((`e`.`manager_id` = `oc`.`id`) and (`oc`.`level` < 10))) /* select#1 */ select `org_chart`.`level` AS `level`,count(0) AS `headcount` from `org_chart` group by `org_chart`.`level` order by `org_chart`.`level`"
    },
    {
      "lesson": "non_unique_lookup",
      "match": {
        "folded_label": "INDEX LOOKUP [employees.idx_mgr] starts=900 rows=1",
        "short_label": "Index lookup [employees.idx_mgr]"
      },
      "controls": {
        "rows": 900,
        "selectivity": 40.0,
        "covering": false
      },
      "note": "Index lookup on e using idx_mgr (manager_id=oc.id) (employees)",
      "query_sql": "with recursive `org_chart` as (/* select#2 */ select `demodb`.`employees`.`id` AS `id`,`demodb`.`employees`.`name` AS `name`,`demodb`.`employees`.`manager_id` AS `manager_id`,1 AS `level` from `demodb`.`employees` where (`demodb`.`employees`.`manager_id` is null) union all /* select#3 */ select `e`.`id` AS `id`,`e`.`name` AS `name`,`e`.`manager_id` AS `manager_id`,(`oc`.`level` + 1) AS `oc.level + 1` from `demodb`.`employees` `e` join `org_chart` `oc` where ((`e`.`manager_id` = `oc`.`id`) and (`oc`.`level` < 10))) /* select#1 */ select `org_chart`.`level` AS `level`,count(0) AS `headcount` from `org_chart` group by `org_chart`.`level` order by `org_chart`.`level`"
    }
  ],
  "operator_complexities": [
    {
      "node_id": "n:347eeced0b3c",
      "folded_label": "SORT starts=1 rows=10",
      "short_label": "Sort",
      "complexity": {
        "big_o": "O(n log n)",
        "short": "n log n",
        "severity": "medium",
        "rationale": "Filesort: MySQL sorts the output in the server layer. Priority-queue filesort (LIMIT) is not detected today — we report the upper bound.",
        "confidence": "worst_case",
        "learn_more": "filesort"
      }
    },
    {
      "node_id": "n:ca439ce5f715",
      "folded_label": "TABLE SCAN [<temporary>] starts=1 rows=10",
      "short_label": "Table scan [<temporary>]",
      "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:23956a72ce60",
      "folded_label": "TABLE SCAN [org_chart] starts=1 rows=1000",
      "short_label": "Table scan [org_chart]",
      "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:0d118da99553",
      "folded_label": "MATERIALIZE starts=1 rows=1000",
      "short_label": "Materialize",
      "complexity": {
        "big_o": "build: O(log n + k)  •  scan: O(1) per probe, O(rows) to fetch all",
        "short": "build+scan",
        "severity": "good",
        "rationale": "Materialize is a two-phase operator. The build pays the inner subquery's cost ONCE; each scan of the temp table is effectively constant time. Don't compound build cost by loops.",
        "confidence": "exact",
        "learn_more": "materialization",
        "build_complexity": {
          "big_o": "O(log n + k)",
          "short": "log n + k",
          "severity": "good",
          "rationale": "Index lookup (ref): one B+tree descent then a sequential walk over k matching index entries. k = matching rows for the indexed predicate.",
          "confidence": "exact",
          "learn_more": "index_lookup"
        },
        "scan_complexity": {
          "big_o": "O(1) per probe, O(rows) to fetch all",
          "short": "O(1)/probe",
          "severity": "good",
          "rationale": "Materialize scan: reads the pre-computed temp table. Each probe is constant time once the table is in memory.",
          "confidence": "typical",
          "learn_more": "materialization"
        }
      }
    },
    {
      "node_id": "n:690d9483b0a5",
      "folded_label": "INDEX LOOKUP [employees.idx_mgr] starts=1 rows=100",
      "short_label": "Index lookup [employees.idx_mgr]",
      "complexity": {
        "big_o": "O(log n + k)",
        "short": "log n + k",
        "severity": "good",
        "rationale": "Index lookup (ref): one B+tree descent then a sequential walk over k matching index entries. k = matching rows for the indexed predicate.",
        "confidence": "exact",
        "learn_more": "index_lookup"
      }
    },
    {
      "node_id": "n:b76c70582649",
      "folded_label": "NESTED LOOP INNER starts=2 rows=450",
      "short_label": "Nested loop inner join",
      "complexity": {
        "big_o": "O(n · log m)",
        "short": "n · log m",
        "severity": "medium",
        "rationale": "Indexed nested loop: each outer row probes the inner table via an index descent.",
        "confidence": "exact",
        "learn_more": "nested_loop_join"
      }
    },
    {
      "node_id": "n:911d971460cb",
      "folded_label": "INDEX LOOKUP [employees.idx_mgr] starts=900 rows=1",
      "short_label": "Index lookup [employees.idx_mgr]",
      "complexity": {
        "big_o": "O(log n + k)",
        "short": "log n + k",
        "severity": "good",
        "rationale": "Index lookup (ref): one B+tree descent then a sequential walk over k matching index entries. k = matching rows for the indexed predicate.",
        "confidence": "exact",
        "learn_more": "index_lookup"
      }
    }
  ]
}

Primary recommendation

SQL

Execution plan (tree)

Warnings (3)

Suggestions (3)

myteach — interactive algorithm catalog

Lessons relevant to this plan

Raw sidecar (JSON)

Filesort — how MySQL sorts without an indexMySQL 8.4 • MariaDB 11.4

Parameters (MySQL 8.4 / MariaDB 11.4 filesort)

Cost readout (filesort)

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

Parameters (full table scan cost model)

Cost readout (full table scan)

Temporary tables — MEMORY to on-disk conversionMySQL 8.4

Parameters (MySQL 8.4 internal temporary tables)

Cost readout (internal temporary tables)

Non-Unique Key Lookup — index hits that return many rowsMySQL 8.4 • MariaDB 11.4

Parameters (non-unique lookup)

Cost readout (B+tree traversal + leaf scan + row-id fetch)

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

Join shape controls

Nested loop cost model

Filter operator — WHERE predicate row-by-rowMySQL 8.4 • MariaDB 11.4

Parameters (filter stage)

Cost readout (filter operator)

Teach: Operator deep dive

Summary

SQL

Execution plan (tree)

Warnings (3)

Suggestions (3)

myteach — interactive algorithm catalog

Lessons relevant to this plan

Raw sidecar (JSON)