MySQL Query Plan

Query scans 2 tables, materializes 2 temp tables and sorts the result; examines ~4,008 rows to return 24 in 3.8 ms. Main finding: full scan of employees (2,000 rows).

Total time: 3.79 ms
Rows returned: 24
Rows examined: 4.01K
Operators: 15

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 dept_avg as (/*
select#2 */
select demodb.employees.dept_id AS dept_id,avg(demodb.employees.salary) AS avg_sal,count(0) AS n
from demodb.employees
group by demodb.employees.dept_id), top_paid as (/*
select#3 */
select e.id AS id,e.name AS name,e.dept_id AS dept_id,e.salary AS salary,row_number() OVER (PARTITION BY demodb.e.dept_id
ORDER BY demodb.e.salary desc ) AS rn
from demodb.employees e) /*
select#1 */
select d.name AS dept,da.avg_sal AS avg_sal,tp.name AS top_earner,tp.salary AS salary
from demodb.departments d
  join dept_avg da
  join top_paid tp
where ((tp.dept_id = d.id) and (da.dept_id = d.id) and (tp.rn <= 3))
order by da.avg_sal desc

Execution plan (bargraph)

Click a plan operator to inspect details.

Warnings (3)

WARN
Full table scan: departments (8 rows), employees (2000 rows)

Labeled: Table scan [departments] Table scan [employees]
WARN
2 temp table Learn →(s) (Materialize) — up to 2000 rows; may spill to disk

Labeled: Materialize
WARN
2 sort operation Learn →(s) — 2000 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 (7 terms)

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:25Z",
  "myflames_version": "1.5.0",
  "source": {
    "type": "file"
  },
  "plan_summary": {
    "total_time_ms": 3.79,
    "rows_sent": 24,
    "rows_examined_estimate": 4008,
    "operator_count": 15,
    "max_depth": 10
  },
  "optimizer_switches": [
    {
      "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: departments (8 rows), employees (2000 rows)",
      "source": "plan",
      "node_labels": [
        "Table scan [departments]",
        "Table scan [employees]"
      ]
    },
    {
      "severity": "warn",
      "category": "temp_table",
      "text": "2 temp table(s) (Materialize) — up to 2000 rows; may spill to disk",
      "source": "plan",
      "node_labels": [
        "Materialize"
      ]
    },
    {
      "severity": "warn",
      "category": "filesort",
      "text": "2 sort operation(s) — 2000 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 2 temp tables and sorts the result; examines ~4,008 rows to return 24 in 3.8 ms. Main finding: full scan of employees (2,000 rows).",
  "plan_tree": {
    "node_id": "n:1f1e4988985d",
    "short_label": "Sort",
    "folded_label": "SORT starts=1 rows=24",
    "children": [
      {
        "node_id": "n:8415bc449e4d",
        "short_label": "Stream results",
        "folded_label": "STREAM starts=1 rows=24",
        "children": [
          {
            "node_id": "n:e09d45a7f9ab",
            "short_label": "Nested loop inner join",
            "folded_label": "NESTED LOOP INNER starts=1 rows=24",
            "children": [
              {
                "node_id": "n:e93f3abd5175",
                "short_label": "Nested loop inner join",
                "folded_label": "NESTED LOOP INNER starts=1 rows=24",
                "children": [
                  {
                    "node_id": "n:b953fadc011c",
                    "short_label": "Table scan [departments]",
                    "folded_label": "TABLE SCAN [departments] starts=1 rows=8",
                    "children": []
                  },
                  {
                    "node_id": "n:28b9826d22a5",
                    "short_label": "Filter: ((tp.rn <= 3))",
                    "folded_label": "FILTER ((tp.rn <= 3)) starts=8 rows=3",
                    "children": [
                      {
                        "node_id": "n:60bd34bb1b68",
                        "short_label": "Index lookup [tp.<auto_key0>]",
                        "folded_label": "INDEX LOOKUP [tp.<auto_key0>] starts=8 rows=250",
                        "children": [
                          {
                            "node_id": "n:3d82bcbcdc50",
                            "short_label": "Materialize",
                            "folded_label": "MATERIALIZE starts=1 rows=2000",
                            "children": [
                              {
                                "node_id": "n:ba04ee36d04e",
                                "short_label": "Window aggregate: row_number() OVER (PARTITION BY ...",
                                "folded_label": "Window aggregate: row_number() OVER (PARTITION BY e.dept_id .. starts=1 rows=2000",
                                "children": [
                                  {
                                    "node_id": "n:4fab1d622697",
                                    "short_label": "Sort",
                                    "folded_label": "SORT starts=1 rows=2000",
                                    "children": [
                                      {
                                        "node_id": "n:98ed0d4e2f31",
                                        "short_label": "Table scan [employees]",
                                        "folded_label": "TABLE SCAN [employees] starts=1 rows=2000",
                                        "children": []
                                      }
                                    ]
                                  }
                                ]
                              }
                            ]
                          }
                        ]
                      }
                    ]
                  }
                ]
              },
              {
                "node_id": "n:c390875b730f",
                "short_label": "Index lookup [da.<auto_key0>]",
                "folded_label": "INDEX LOOKUP [da.<auto_key0>] starts=24 rows=1",
                "children": [
                  {
                    "node_id": "n:b55f5202484b",
                    "short_label": "Materialize",
                    "folded_label": "MATERIALIZE starts=1 rows=8",
                    "children": [
                      {
                        "node_id": "n:dff6f45b7a36",
                        "short_label": "Group",
                        "folded_label": "GROUP starts=1 rows=8",
                        "children": [
                          {
                            "node_id": "n:90028dbcb362",
                            "short_label": "Index scan [employees.idx_dept]",
                            "folded_label": "INDEX SCAN [employees.idx_dept] starts=1 rows=2000",
                            "children": []
                          }
                        ]
                      }
                    ]
                  }
                ]
              }
            ]
          }
        ]
      }
    ]
  },
  "query": {
    "raw": "with `dept_avg` as (/* select#2 */ select `demodb`.`employees`.`dept_id` AS `dept_id`,avg(`demodb`.`employees`.`salary`) AS `avg_sal`,count(0) AS `n` from `demodb`.`employees` group by `demodb`.`employees`.`dept_id`), `top_paid` as (/* select#3 */ select `e`.`id` AS `id`,`e`.`name` AS `name`,`e`.`dept_id` AS `dept_id`,`e`.`salary` AS `salary`,row_number() OVER (PARTITION BY `demodb`.`e`.`dept_id` ORDER BY `demodb`.`e`.`salary` desc )  AS `rn` from `demodb`.`employees` `e`) /* select#1 */ select `d`.`name` AS `dept`,`da`.`avg_sal` AS `avg_sal`,`tp`.`name` AS `top_earner`,`tp`.`salary` AS `salary` from `demodb`.`departments` `d` join `dept_avg` `da` join `top_paid` `tp` where ((`tp`.`dept_id` = `d`.`id`) and (`da`.`dept_id` = `d`.`id`) and (`tp`.`rn` <= 3)) order by `da`.`avg_sal` desc",
    "beautified": "with dept_avg as (/*\nselect#2 */\nselect demodb.employees.dept_id AS dept_id,avg(demodb.employees.salary) AS avg_sal,count(0) AS n\nfrom demodb.employees\ngroup by demodb.employees.dept_id), top_paid as (/*\nselect#3 */\nselect e.id AS id,e.name AS name,e.dept_id AS dept_id,e.salary AS salary,row_number() OVER (PARTITION BY demodb.e.dept_id\nORDER BY demodb.e.salary desc ) AS rn\nfrom demodb.employees e) /*\nselect#1 */\nselect d.name AS dept,da.avg_sal AS avg_sal,tp.name AS top_earner,tp.salary AS salary\nfrom demodb.departments d\n  join dept_avg da\n  join top_paid tp\nwhere ((tp.dept_id = d.id) and (da.dept_id = d.id) and (tp.rn <= 3))\norder by da.avg_sal desc"
  },
  "primary_action": {
    "ref": "suggestions[0]"
  },
  "teach_hooks": [
    {
      "lesson": "filesort",
      "match": {
        "folded_label": "SORT starts=1 rows=24",
        "short_label": "Sort"
      },
      "controls": {
        "rows": 24,
        "row_size": 200
      },
      "note": "Sort: da.avg_sal DESC",
      "query_sql": "with `dept_avg` as (/* select#2 */ select `demodb`.`employees`.`dept_id` AS `dept_id`,avg(`demodb`.`employees`.`salary`) AS `avg_sal`,count(0) AS `n` from `demodb`.`employees` group by `demodb`.`employees`.`dept_id`), `top_paid` as (/* select#3 */ select `e`.`id` AS `id`,`e`.`name` AS `name`,`e`.`dept_id` AS `dept_id`,`e`.`salary` AS `salary`,row_number() OVER (PARTITION BY `demodb`.`e`.`dept_id` ORDER BY `demodb`.`e`.`salary` desc )  AS `rn` from `demodb`.`employees` `e`) /* select#1 */ select `d`.`name` AS `dept`,`da`.`avg_sal` AS `avg_sal`,`tp`.`name` AS `top_earner`,`tp`.`salary` AS `salary` from `demodb`.`departments` `d` join `dept_avg` `da` join `top_paid` `tp` where ((`tp`.`dept_id` = `d`.`id`) and (`da`.`dept_id` = `d`.`id`) and (`tp`.`rn` <= 3)) order by `da`.`avg_sal` desc"
    },
    {
      "lesson": "nested_loop",
      "match": {
        "folded_label": "NESTED LOOP INNER starts=1 rows=24",
        "short_label": "Nested loop inner join"
      },
      "controls": {
        "outer_rows": 24,
        "inner_rows": 24,
        "row_size": 200
      },
      "note": "Nested loop inner join",
      "query_sql": "with `dept_avg` as (/* select#2 */ select `demodb`.`employees`.`dept_id` AS `dept_id`,avg(`demodb`.`employees`.`salary`) AS `avg_sal`,count(0) AS `n` from `demodb`.`employees` group by `demodb`.`employees`.`dept_id`), `top_paid` as (/* select#3 */ select `e`.`id` AS `id`,`e`.`name` AS `name`,`e`.`dept_id` AS `dept_id`,`e`.`salary` AS `salary`,row_number() OVER (PARTITION BY `demodb`.`e`.`dept_id` ORDER BY `demodb`.`e`.`salary` desc )  AS `rn` from `demodb`.`employees` `e`) /* select#1 */ select `d`.`name` AS `dept`,`da`.`avg_sal` AS `avg_sal`,`tp`.`name` AS `top_earner`,`tp`.`salary` AS `salary` from `demodb`.`departments` `d` join `dept_avg` `da` join `top_paid` `tp` where ((`tp`.`dept_id` = `d`.`id`) and (`da`.`dept_id` = `d`.`id`) and (`tp`.`rn` <= 3)) order by `da`.`avg_sal` desc"
    },
    {
      "lesson": "full_scan",
      "match": {
        "folded_label": "TABLE SCAN [departments] starts=1 rows=8",
        "short_label": "Table scan [departments]"
      },
      "controls": {
        "rows": 8,
        "row_size": 200,
        "selectivity": 100.0
      },
      "note": "Table scan on d (departments)",
      "query_sql": "with `dept_avg` as (/* select#2 */ select `demodb`.`employees`.`dept_id` AS `dept_id`,avg(`demodb`.`employees`.`salary`) AS `avg_sal`,count(0) AS `n` from `demodb`.`employees` group by `demodb`.`employees`.`dept_id`), `top_paid` as (/* select#3 */ select `e`.`id` AS `id`,`e`.`name` AS `name`,`e`.`dept_id` AS `dept_id`,`e`.`salary` AS `salary`,row_number() OVER (PARTITION BY `demodb`.`e`.`dept_id` ORDER BY `demodb`.`e`.`salary` desc )  AS `rn` from `demodb`.`employees` `e`) /* select#1 */ select `d`.`name` AS `dept`,`da`.`avg_sal` AS `avg_sal`,`tp`.`name` AS `top_earner`,`tp`.`salary` AS `salary` from `demodb`.`departments` `d` join `dept_avg` `da` join `top_paid` `tp` where ((`tp`.`dept_id` = `d`.`id`) and (`da`.`dept_id` = `d`.`id`) and (`tp`.`rn` <= 3)) order by `da`.`avg_sal` desc"
    },
    {
      "lesson": "filter",
      "match": {
        "folded_label": "FILTER ((tp.rn <= 3)) starts=8 rows=3",
        "short_label": "Filter: ((tp.rn <= 3))"
      },
      "controls": {
        "input_rows": 24,
        "selectivity": 42.9
      },
      "note": "Filter: (tp.rn <= 3)",
      "query_sql": "with `dept_avg` as (/* select#2 */ select `demodb`.`employees`.`dept_id` AS `dept_id`,avg(`demodb`.`employees`.`salary`) AS `avg_sal`,count(0) AS `n` from `demodb`.`employees` group by `demodb`.`employees`.`dept_id`), `top_paid` as (/* select#3 */ select `e`.`id` AS `id`,`e`.`name` AS `name`,`e`.`dept_id` AS `dept_id`,`e`.`salary` AS `salary`,row_number() OVER (PARTITION BY `demodb`.`e`.`dept_id` ORDER BY `demodb`.`e`.`salary` desc )  AS `rn` from `demodb`.`employees` `e`) /* select#1 */ select `d`.`name` AS `dept`,`da`.`avg_sal` AS `avg_sal`,`tp`.`name` AS `top_earner`,`tp`.`salary` AS `salary` from `demodb`.`departments` `d` join `dept_avg` `da` join `top_paid` `tp` where ((`tp`.`dept_id` = `d`.`id`) and (`da`.`dept_id` = `d`.`id`) and (`tp`.`rn` <= 3)) order by `da`.`avg_sal` desc"
    },
    {
      "lesson": "non_unique_lookup",
      "match": {
        "folded_label": "INDEX LOOKUP [tp.<auto_key0>] starts=8 rows=250",
        "short_label": "Index lookup [tp.<auto_key0>]"
      },
      "controls": {
        "rows": 2000,
        "selectivity": 40.0,
        "covering": false
      },
      "note": "Index lookup on tp using <auto_key0> (dept_id=d.id) (tp)",
      "query_sql": "with `dept_avg` as (/* select#2 */ select `demodb`.`employees`.`dept_id` AS `dept_id`,avg(`demodb`.`employees`.`salary`) AS `avg_sal`,count(0) AS `n` from `demodb`.`employees` group by `demodb`.`employees`.`dept_id`), `top_paid` as (/* select#3 */ select `e`.`id` AS `id`,`e`.`name` AS `name`,`e`.`dept_id` AS `dept_id`,`e`.`salary` AS `salary`,row_number() OVER (PARTITION BY `demodb`.`e`.`dept_id` ORDER BY `demodb`.`e`.`salary` desc )  AS `rn` from `demodb`.`employees` `e`) /* select#1 */ select `d`.`name` AS `dept`,`da`.`avg_sal` AS `avg_sal`,`tp`.`name` AS `top_earner`,`tp`.`salary` AS `salary` from `demodb`.`departments` `d` join `dept_avg` `da` join `top_paid` `tp` where ((`tp`.`dept_id` = `d`.`id`) and (`da`.`dept_id` = `d`.`id`) and (`tp`.`rn` <= 3)) order by `da`.`avg_sal` desc"
    },
    {
      "lesson": "tmp",
      "match": {
        "folded_label": "MATERIALIZE starts=1 rows=2000",
        "short_label": "Materialize"
      },
      "controls": {
        "rows": 2000,
        "row_size": 200
      },
      "note": "Materialize CTE top_paid",
      "query_sql": "with `dept_avg` as (/* select#2 */ select `demodb`.`employees`.`dept_id` AS `dept_id`,avg(`demodb`.`employees`.`salary`) AS `avg_sal`,count(0) AS `n` from `demodb`.`employees` group by `demodb`.`employees`.`dept_id`), `top_paid` as (/* select#3 */ select `e`.`id` AS `id`,`e`.`name` AS `name`,`e`.`dept_id` AS `dept_id`,`e`.`salary` AS `salary`,row_number() OVER (PARTITION BY `demodb`.`e`.`dept_id` ORDER BY `demodb`.`e`.`salary` desc )  AS `rn` from `demodb`.`employees` `e`) /* select#1 */ select `d`.`name` AS `dept`,`da`.`avg_sal` AS `avg_sal`,`tp`.`name` AS `top_earner`,`tp`.`salary` AS `salary` from `demodb`.`departments` `d` join `dept_avg` `da` join `top_paid` `tp` where ((`tp`.`dept_id` = `d`.`id`) and (`da`.`dept_id` = `d`.`id`) and (`tp`.`rn` <= 3)) order by `da`.`avg_sal` desc"
    },
    {
      "lesson": "filesort",
      "match": {
        "folded_label": "SORT starts=1 rows=2000",
        "short_label": "Sort"
      },
      "controls": {
        "rows": 2000,
        "row_size": 200
      },
      "note": "Sort: e.dept_id, e.salary DESC",
      "query_sql": "with `dept_avg` as (/* select#2 */ select `demodb`.`employees`.`dept_id` AS `dept_id`,avg(`demodb`.`employees`.`salary`) AS `avg_sal`,count(0) AS `n` from `demodb`.`employees` group by `demodb`.`employees`.`dept_id`), `top_paid` as (/* select#3 */ select `e`.`id` AS `id`,`e`.`name` AS `name`,`e`.`dept_id` AS `dept_id`,`e`.`salary` AS `salary`,row_number() OVER (PARTITION BY `demodb`.`e`.`dept_id` ORDER BY `demodb`.`e`.`salary` desc )  AS `rn` from `demodb`.`employees` `e`) /* select#1 */ select `d`.`name` AS `dept`,`da`.`avg_sal` AS `avg_sal`,`tp`.`name` AS `top_earner`,`tp`.`salary` AS `salary` from `demodb`.`departments` `d` join `dept_avg` `da` join `top_paid` `tp` where ((`tp`.`dept_id` = `d`.`id`) and (`da`.`dept_id` = `d`.`id`) and (`tp`.`rn` <= 3)) order by `da`.`avg_sal` desc"
    },
    {
      "lesson": "full_scan",
      "match": {
        "folded_label": "TABLE SCAN [employees] starts=1 rows=2000",
        "short_label": "Table scan [employees]"
      },
      "controls": {
        "rows": 2000,
        "row_size": 200,
        "selectivity": 100.0
      },
      "note": "Table scan on e (employees)",
      "query_sql": "with `dept_avg` as (/* select#2 */ select `demodb`.`employees`.`dept_id` AS `dept_id`,avg(`demodb`.`employees`.`salary`) AS `avg_sal`,count(0) AS `n` from `demodb`.`employees` group by `demodb`.`employees`.`dept_id`), `top_paid` as (/* select#3 */ select `e`.`id` AS `id`,`e`.`name` AS `name`,`e`.`dept_id` AS `dept_id`,`e`.`salary` AS `salary`,row_number() OVER (PARTITION BY `demodb`.`e`.`dept_id` ORDER BY `demodb`.`e`.`salary` desc )  AS `rn` from `demodb`.`employees` `e`) /* select#1 */ select `d`.`name` AS `dept`,`da`.`avg_sal` AS `avg_sal`,`tp`.`name` AS `top_earner`,`tp`.`salary` AS `salary` from `demodb`.`departments` `d` join `dept_avg` `da` join `top_paid` `tp` where ((`tp`.`dept_id` = `d`.`id`) and (`da`.`dept_id` = `d`.`id`) and (`tp`.`rn` <= 3)) order by `da`.`avg_sal` desc"
    },
    {
      "lesson": "non_unique_lookup",
      "match": {
        "folded_label": "INDEX LOOKUP [da.<auto_key0>] starts=24 rows=1",
        "short_label": "Index lookup [da.<auto_key0>]"
      },
      "controls": {
        "rows": 24,
        "selectivity": 5.0,
        "covering": false
      },
      "note": "Index lookup on da using <auto_key0> (dept_id=d.id) (da)",
      "query_sql": "with `dept_avg` as (/* select#2 */ select `demodb`.`employees`.`dept_id` AS `dept_id`,avg(`demodb`.`employees`.`salary`) AS `avg_sal`,count(0) AS `n` from `demodb`.`employees` group by `demodb`.`employees`.`dept_id`), `top_paid` as (/* select#3 */ select `e`.`id` AS `id`,`e`.`name` AS `name`,`e`.`dept_id` AS `dept_id`,`e`.`salary` AS `salary`,row_number() OVER (PARTITION BY `demodb`.`e`.`dept_id` ORDER BY `demodb`.`e`.`salary` desc )  AS `rn` from `demodb`.`employees` `e`) /* select#1 */ select `d`.`name` AS `dept`,`da`.`avg_sal` AS `avg_sal`,`tp`.`name` AS `top_earner`,`tp`.`salary` AS `salary` from `demodb`.`departments` `d` join `dept_avg` `da` join `top_paid` `tp` where ((`tp`.`dept_id` = `d`.`id`) and (`da`.`dept_id` = `d`.`id`) and (`tp`.`rn` <= 3)) order by `da`.`avg_sal` desc"
    },
    {
      "lesson": "tmp",
      "match": {
        "folded_label": "MATERIALIZE starts=1 rows=8",
        "short_label": "Materialize"
      },
      "controls": {
        "rows": 8,
        "row_size": 200
      },
      "note": "Materialize CTE dept_avg",
      "query_sql": "with `dept_avg` as (/* select#2 */ select `demodb`.`employees`.`dept_id` AS `dept_id`,avg(`demodb`.`employees`.`salary`) AS `avg_sal`,count(0) AS `n` from `demodb`.`employees` group by `demodb`.`employees`.`dept_id`), `top_paid` as (/* select#3 */ select `e`.`id` AS `id`,`e`.`name` AS `name`,`e`.`dept_id` AS `dept_id`,`e`.`salary` AS `salary`,row_number() OVER (PARTITION BY `demodb`.`e`.`dept_id` ORDER BY `demodb`.`e`.`salary` desc )  AS `rn` from `demodb`.`employees` `e`) /* select#1 */ select `d`.`name` AS `dept`,`da`.`avg_sal` AS `avg_sal`,`tp`.`name` AS `top_earner`,`tp`.`salary` AS `salary` from `demodb`.`departments` `d` join `dept_avg` `da` join `top_paid` `tp` where ((`tp`.`dept_id` = `d`.`id`) and (`da`.`dept_id` = `d`.`id`) and (`tp`.`rn` <= 3)) order by `da`.`avg_sal` desc"
    },
    {
      "lesson": "btree",
      "match": {
        "folded_label": "INDEX SCAN [employees.idx_dept] starts=1 rows=2000",
        "short_label": "Index scan [employees.idx_dept]"
      },
      "controls": {
        "rows": 2000,
        "key_size": 16,
        "page_size": 16384,
        "key_type": "secondary_noncovering"
      },
      "note": "Index scan on employees using idx_dept (employees)",
      "query_sql": "with `dept_avg` as (/* select#2 */ select `demodb`.`employees`.`dept_id` AS `dept_id`,avg(`demodb`.`employees`.`salary`) AS `avg_sal`,count(0) AS `n` from `demodb`.`employees` group by `demodb`.`employees`.`dept_id`), `top_paid` as (/* select#3 */ select `e`.`id` AS `id`,`e`.`name` AS `name`,`e`.`dept_id` AS `dept_id`,`e`.`salary` AS `salary`,row_number() OVER (PARTITION BY `demodb`.`e`.`dept_id` ORDER BY `demodb`.`e`.`salary` desc )  AS `rn` from `demodb`.`employees` `e`) /* select#1 */ select `d`.`name` AS `dept`,`da`.`avg_sal` AS `avg_sal`,`tp`.`name` AS `top_earner`,`tp`.`salary` AS `salary` from `demodb`.`departments` `d` join `dept_avg` `da` join `top_paid` `tp` where ((`tp`.`dept_id` = `d`.`id`) and (`da`.`dept_id` = `d`.`id`) and (`tp`.`rn` <= 3)) order by `da`.`avg_sal` desc"
    }
  ],
  "operator_complexities": [
    {
      "node_id": "n:1f1e4988985d",
      "folded_label": "SORT starts=1 rows=24",
      "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:e09d45a7f9ab",
      "folded_label": "NESTED LOOP INNER starts=1 rows=24",
      "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:b953fadc011c",
      "folded_label": "TABLE SCAN [departments] starts=1 rows=8",
      "short_label": "Table scan [departments]",
      "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:60bd34bb1b68",
      "folded_label": "INDEX LOOKUP [tp.<auto_key0>] starts=8 rows=250",
      "short_label": "Index lookup [tp.<auto_key0>]",
      "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:3d82bcbcdc50",
      "folded_label": "MATERIALIZE starts=1 rows=2000",
      "short_label": "Materialize",
      "complexity": {
        "big_o": "build: O(m)  •  scan: O(1) per probe, O(rows) to fetch all",
        "short": "build+scan",
        "severity": "medium",
        "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": "typical",
        "learn_more": "materialization",
        "build_complexity": {
          "big_o": "O(m)",
          "short": "m",
          "severity": "medium",
          "rationale": "Materialize build: cost = cost of the source subquery over m rows.",
          "confidence": "typical",
          "learn_more": "materialization"
        },
        "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:4fab1d622697",
      "folded_label": "SORT starts=1 rows=2000",
      "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:98ed0d4e2f31",
      "folded_label": "TABLE SCAN [employees] starts=1 rows=2000",
      "short_label": "Table scan [employees]",
      "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:c390875b730f",
      "folded_label": "INDEX LOOKUP [da.<auto_key0>] starts=24 rows=1",
      "short_label": "Index lookup [da.<auto_key0>]",
      "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:b55f5202484b",
      "folded_label": "MATERIALIZE starts=1 rows=8",
      "short_label": "Materialize",
      "complexity": {
        "big_o": "build: O(n log n)  •  scan: O(1) per probe, O(rows) to fetch all",
        "short": "build+scan",
        "severity": "medium",
        "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": "typical",
        "learn_more": "materialization",
        "build_complexity": {
          "big_o": "O(n log n)",
          "short": "n log n",
          "severity": "medium",
          "rationale": "Sort-based aggregation: MySQL sorts by the grouping key before collapsing duplicates.",
          "confidence": "typical"
        },
        "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:dff6f45b7a36",
      "folded_label": "GROUP starts=1 rows=8",
      "short_label": "Group",
      "complexity": {
        "big_o": "O(n log n)",
        "short": "n log n",
        "severity": "medium",
        "rationale": "Sort-based aggregation: MySQL sorts by the grouping key before collapsing duplicates.",
        "confidence": "typical"
      }
    },
    {
      "node_id": "n:90028dbcb362",
      "folded_label": "INDEX SCAN [employees.idx_dept] starts=1 rows=2000",
      "short_label": "Index scan [employees.idx_dept]",
      "complexity": {
        "big_o": "O(n)",
        "short": "n",
        "severity": "medium",
        "rationale": "Index scan: reads every entry of the index in key order. Cheaper than a table scan because the index is smaller, but still O(n).",
        "confidence": "exact",
        "learn_more": "index_scan"
      }
    }
  ]
}

Primary recommendation

SQL

Execution plan (bargraph)

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)

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)

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

Parameters (filter stage)

Cost readout (filter operator)

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)

Temporary tables — MEMORY to on-disk conversionMySQL 8.4

Parameters (MySQL 8.4 internal temporary tables)

Cost readout (internal temporary tables)

B+tree lookup — how InnoDB finds a rowMySQL 8.4 • MariaDB 11.4

Parameters

Cost readout (updates live)

Teach: Operator deep dive

Summary

SQL

Execution plan (bargraph)

Warnings (3)

Suggestions (3)

myteach — interactive algorithm catalog

Lessons relevant to this plan

Raw sidecar (JSON)