MySQL Query Plan

Query scans 2 tables and sorts the result; examines ~331,614 rows to return 9 in 6357 ms. Main finding: full scan of dept_emp (331,603 rows).

Total time: 6.36 s
Rows returned: 9
Rows examined: 331.61K
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

select d.dept_name AS dept_name,round(AVG(s.salary),2) AS avg_salary
from employees.salaries s
  join employees.dept_emp de
  join employees.departments d
where ((s.emp_no = de.emp_no) and (d.dept_no = de.dept_no) and (de.to_date = DATE'9999-01-01') and (s.to_date = DATE'9999-01-01'))
group by employees.d.dept_name
order by avg_salary desc

Execution plan (tree)

Click a plan operator to inspect details.

Warnings (2)

WARN
Full table scan: <temporary> (9 rows), dept_emp (331603 rows)

Labeled: Table scan [<temporary>] Table scan [dept_emp]
WARN
1 sort operation Learn →(s) — 9 rows; may use disk-based filesort Learn →

Labeled: Sort

Suggestions (2)

HIGH
Add indexes on filter/join columns to avoid full table scans
MEDIUM
Increase sort_buffer_size or add an ordered index to avoid filesort Learn →

Glossary (3 terms)

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.
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.

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

filesort

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": 6357.23,
    "rows_sent": 9,
    "rows_examined_estimate": 331614,
    "operator_count": 10,
    "max_depth": 7
  },
  "optimizer_switches": [],
  "warnings": [
    {
      "severity": "warn",
      "category": "full_scan",
      "text": "Full table scan: <temporary> (9 rows), dept_emp (331603 rows)",
      "source": "plan",
      "node_labels": [
        "Table scan [<temporary>]",
        "Table scan [dept_emp]"
      ]
    },
    {
      "severity": "warn",
      "category": "filesort",
      "text": "1 sort operation(s) — 9 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 sort_buffer_size or add an ordered index to avoid filesort",
      "source": "plan",
      "target_variable": "sort_buffer_size"
    }
  ],
  "executive_summary": "Query scans 2 tables and sorts the result; examines ~331,614 rows to return 9 in 6357 ms. Main finding: full scan of dept_emp (331,603 rows).",
  "plan_tree": {
    "node_id": "n:4b310806699e",
    "short_label": "Sort",
    "folded_label": "SORT starts=1 rows=9",
    "children": [
      {
        "node_id": "n:d2dccead858b",
        "short_label": "Table scan [<temporary>]",
        "folded_label": "TABLE SCAN [<temporary>] starts=1 rows=9",
        "children": [
          {
            "node_id": "n:36c719443bc0",
            "short_label": "Aggregate",
            "folded_label": "AGGREGATE starts=1 rows=9",
            "children": [
              {
                "node_id": "n:62bcf2b0e81f",
                "short_label": "Nested loop inner join",
                "folded_label": "NESTED LOOP INNER starts=1 rows=240124",
                "children": [
                  {
                    "node_id": "n:3f3b42eaef83",
                    "short_label": "Nested loop inner join",
                    "folded_label": "NESTED LOOP INNER starts=1 rows=240124",
                    "children": [
                      {
                        "node_id": "n:9e86647097af",
                        "short_label": "Filter: ((de.to_date = DATE'9999-01-01'))",
                        "folded_label": "FILTER ((de.to_date = DATE'9999-01-01')) starts=1 rows=240124",
                        "children": [
                          {
                            "node_id": "n:f594e5f3a9f7",
                            "short_label": "Table scan [dept_emp]",
                            "folded_label": "TABLE SCAN [dept_emp] starts=1 rows=331603",
                            "children": []
                          }
                        ]
                      },
                      {
                        "node_id": "n:d0f7a733b6e0",
                        "short_label": "Filter: ((s.to_date = DATE'9999-01-01'))",
                        "folded_label": "FILTER ((s.to_date = DATE'9999-01-01')) starts=240124 rows=1",
                        "children": [
                          {
                            "node_id": "n:9d8125b7e0a1",
                            "short_label": "Index lookup [salaries.PRIMARY]",
                            "folded_label": "INDEX LOOKUP [salaries.PRIMARY] starts=240124 rows=11",
                            "children": []
                          }
                        ]
                      }
                    ]
                  },
                  {
                    "node_id": "n:661c6ab5d2c3",
                    "short_label": "Single-row lookup [departments.PRIMARY]",
                    "folded_label": "SINGLE ROW LOOKUP [departments.PRIMARY] starts=240124 rows=1",
                    "children": []
                  }
                ]
              }
            ]
          }
        ]
      }
    ]
  },
  "query": {
    "raw": "/* select#1 */ select `d`.`dept_name` AS `dept_name`,round(AVG(s.salary),2) AS `avg_salary` from `employees`.`salaries` `s` join `employees`.`dept_emp` `de` join `employees`.`departments` `d` where ((`s`.`emp_no` = `de`.`emp_no`) and (`d`.`dept_no` = `de`.`dept_no`) and (`de`.`to_date` = DATE'9999-01-01') and (`s`.`to_date` = DATE'9999-01-01')) group by `employees`.`d`.`dept_name` order by `avg_salary` desc",
    "beautified": "select d.dept_name AS dept_name,round(AVG(s.salary),2) AS avg_salary\nfrom employees.salaries s\n  join employees.dept_emp de\n  join employees.departments d\nwhere ((s.emp_no = de.emp_no) and (d.dept_no = de.dept_no) and (de.to_date = DATE'9999-01-01') and (s.to_date = DATE'9999-01-01'))\ngroup by employees.d.dept_name\norder by avg_salary desc"
  },
  "primary_action": {
    "ref": "suggestions[0]"
  },
  "teach_hooks": [
    {
      "lesson": "filesort",
      "match": {
        "folded_label": "SORT starts=1 rows=9",
        "short_label": "Sort"
      },
      "controls": {
        "rows": 9,
        "row_size": 200
      },
      "note": "Sort: avg_salary DESC",
      "query_sql": "/* select#1 */ select `d`.`dept_name` AS `dept_name`,round(AVG(s.salary),2) AS `avg_salary` from `employees`.`salaries` `s` join `employees`.`dept_emp` `de` join `employees`.`departments` `d` where ((`s`.`emp_no` = `de`.`emp_no`) and (`d`.`dept_no` = `de`.`dept_no`) and (`de`.`to_date` = DATE'9999-01-01') and (`s`.`to_date` = DATE'9999-01-01')) group by `employees`.`d`.`dept_name` order by `avg_salary` desc"
    },
    {
      "lesson": "full_scan",
      "match": {
        "folded_label": "TABLE SCAN [<temporary>] starts=1 rows=9",
        "short_label": "Table scan [<temporary>]"
      },
      "controls": {
        "rows": 9,
        "row_size": 200,
        "selectivity": 100.0
      },
      "note": "Table scan on <temporary> (<temporary>)",
      "query_sql": "/* select#1 */ select `d`.`dept_name` AS `dept_name`,round(AVG(s.salary),2) AS `avg_salary` from `employees`.`salaries` `s` join `employees`.`dept_emp` `de` join `employees`.`departments` `d` where ((`s`.`emp_no` = `de`.`emp_no`) and (`d`.`dept_no` = `de`.`dept_no`) and (`de`.`to_date` = DATE'9999-01-01') and (`s`.`to_date` = DATE'9999-01-01')) group by `employees`.`d`.`dept_name` order by `avg_salary` desc"
    },
    {
      "lesson": "tmp",
      "match": {
        "folded_label": "AGGREGATE starts=1 rows=9",
        "short_label": "Aggregate"
      },
      "controls": {
        "rows": 9,
        "row_size": 200
      },
      "note": "Aggregate using temporary table",
      "query_sql": "/* select#1 */ select `d`.`dept_name` AS `dept_name`,round(AVG(s.salary),2) AS `avg_salary` from `employees`.`salaries` `s` join `employees`.`dept_emp` `de` join `employees`.`departments` `d` where ((`s`.`emp_no` = `de`.`emp_no`) and (`d`.`dept_no` = `de`.`dept_no`) and (`de`.`to_date` = DATE'9999-01-01') and (`s`.`to_date` = DATE'9999-01-01')) group by `employees`.`d`.`dept_name` order by `avg_salary` desc"
    },
    {
      "lesson": "nested_loop",
      "match": {
        "folded_label": "NESTED LOOP INNER starts=1 rows=240124",
        "short_label": "Nested loop inner join"
      },
      "controls": {
        "outer_rows": 240124,
        "inner_rows": 240124,
        "row_size": 200
      },
      "note": "Nested loop inner join",
      "query_sql": "/* select#1 */ select `d`.`dept_name` AS `dept_name`,round(AVG(s.salary),2) AS `avg_salary` from `employees`.`salaries` `s` join `employees`.`dept_emp` `de` join `employees`.`departments` `d` where ((`s`.`emp_no` = `de`.`emp_no`) and (`d`.`dept_no` = `de`.`dept_no`) and (`de`.`to_date` = DATE'9999-01-01') and (`s`.`to_date` = DATE'9999-01-01')) group by `employees`.`d`.`dept_name` order by `avg_salary` desc"
    },
    {
      "lesson": "filter",
      "match": {
        "folded_label": "FILTER ((de.to_date = DATE'9999-01-01')) starts=1 rows=240124",
        "short_label": "Filter: ((de.to_date = DATE'9999-01-01'))"
      },
      "controls": {
        "input_rows": 240124,
        "selectivity": 10.0
      },
      "note": "Filter: (de.to_date = DATE'9999-01-01')",
      "query_sql": "/* select#1 */ select `d`.`dept_name` AS `dept_name`,round(AVG(s.salary),2) AS `avg_salary` from `employees`.`salaries` `s` join `employees`.`dept_emp` `de` join `employees`.`departments` `d` where ((`s`.`emp_no` = `de`.`emp_no`) and (`d`.`dept_no` = `de`.`dept_no`) and (`de`.`to_date` = DATE'9999-01-01') and (`s`.`to_date` = DATE'9999-01-01')) group by `employees`.`d`.`dept_name` order by `avg_salary` desc"
    },
    {
      "lesson": "full_scan",
      "match": {
        "folded_label": "TABLE SCAN [dept_emp] starts=1 rows=331603",
        "short_label": "Table scan [dept_emp]"
      },
      "controls": {
        "rows": 331603,
        "row_size": 200,
        "selectivity": 100.0
      },
      "note": "Table scan on de (dept_emp)",
      "query_sql": "/* select#1 */ select `d`.`dept_name` AS `dept_name`,round(AVG(s.salary),2) AS `avg_salary` from `employees`.`salaries` `s` join `employees`.`dept_emp` `de` join `employees`.`departments` `d` where ((`s`.`emp_no` = `de`.`emp_no`) and (`d`.`dept_no` = `de`.`dept_no`) and (`de`.`to_date` = DATE'9999-01-01') and (`s`.`to_date` = DATE'9999-01-01')) group by `employees`.`d`.`dept_name` order by `avg_salary` desc"
    },
    {
      "lesson": "filter",
      "match": {
        "folded_label": "FILTER ((s.to_date = DATE'9999-01-01')) starts=240124 rows=1",
        "short_label": "Filter: ((s.to_date = DATE'9999-01-01'))"
      },
      "controls": {
        "input_rows": 240124,
        "selectivity": 10.0
      },
      "note": "Filter: (s.to_date = DATE'9999-01-01')",
      "query_sql": "/* select#1 */ select `d`.`dept_name` AS `dept_name`,round(AVG(s.salary),2) AS `avg_salary` from `employees`.`salaries` `s` join `employees`.`dept_emp` `de` join `employees`.`departments` `d` where ((`s`.`emp_no` = `de`.`emp_no`) and (`d`.`dept_no` = `de`.`dept_no`) and (`de`.`to_date` = DATE'9999-01-01') and (`s`.`to_date` = DATE'9999-01-01')) group by `employees`.`d`.`dept_name` order by `avg_salary` desc"
    },
    {
      "lesson": "non_unique_lookup",
      "match": {
        "folded_label": "INDEX LOOKUP [salaries.PRIMARY] starts=240124 rows=11",
        "short_label": "Index lookup [salaries.PRIMARY]"
      },
      "controls": {
        "rows": 2524737,
        "selectivity": 40.0,
        "covering": false
      },
      "note": "Index lookup on s using PRIMARY (emp_no=de.emp_no) (salaries)",
      "query_sql": "/* select#1 */ select `d`.`dept_name` AS `dept_name`,round(AVG(s.salary),2) AS `avg_salary` from `employees`.`salaries` `s` join `employees`.`dept_emp` `de` join `employees`.`departments` `d` where ((`s`.`emp_no` = `de`.`emp_no`) and (`d`.`dept_no` = `de`.`dept_no`) and (`de`.`to_date` = DATE'9999-01-01') and (`s`.`to_date` = DATE'9999-01-01')) group by `employees`.`d`.`dept_name` order by `avg_salary` desc"
    },
    {
      "lesson": "unique_lookup",
      "match": {
        "folded_label": "SINGLE ROW LOOKUP [departments.PRIMARY] starts=240124 rows=1",
        "short_label": "Single-row lookup [departments.PRIMARY]"
      },
      "controls": {
        "rows": 240124,
        "covering": false
      },
      "note": "Single-row index lookup on d using PRIMARY (dept_no=de.dept_no) (departments)",
      "query_sql": "/* select#1 */ select `d`.`dept_name` AS `dept_name`,round(AVG(s.salary),2) AS `avg_salary` from `employees`.`salaries` `s` join `employees`.`dept_emp` `de` join `employees`.`departments` `d` where ((`s`.`emp_no` = `de`.`emp_no`) and (`d`.`dept_no` = `de`.`dept_no`) and (`de`.`to_date` = DATE'9999-01-01') and (`s`.`to_date` = DATE'9999-01-01')) group by `employees`.`d`.`dept_name` order by `avg_salary` desc"
    }
  ],
  "operator_complexities": [
    {
      "node_id": "n:4b310806699e",
      "folded_label": "SORT starts=1 rows=9",
      "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:d2dccead858b",
      "folded_label": "TABLE SCAN [<temporary>] starts=1 rows=9",
      "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:62bcf2b0e81f",
      "folded_label": "NESTED LOOP INNER starts=1 rows=240124",
      "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:f594e5f3a9f7",
      "folded_label": "TABLE SCAN [dept_emp] starts=1 rows=331603",
      "short_label": "Table scan [dept_emp]",
      "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:9d8125b7e0a1",
      "folded_label": "INDEX LOOKUP [salaries.PRIMARY] starts=240124 rows=11",
      "short_label": "Index lookup [salaries.PRIMARY]",
      "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:661c6ab5d2c3",
      "folded_label": "SINGLE ROW LOOKUP [departments.PRIMARY] starts=240124 rows=1",
      "short_label": "Single-row lookup [departments.PRIMARY]",
      "complexity": {
        "big_o": "O(log n)",
        "short": "log n",
        "severity": "good",
        "rationale": "Unique-key lookup (eq_ref): one B+tree descent into the inner table per outer row — as fast as MySQL can get.",
        "confidence": "exact",
        "learn_more": "single_row_lookup"
      }
    }
  ]
}

Primary recommendation

SQL

Execution plan (tree)

Warnings (2)

Suggestions (2)

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)

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)

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)

Unique Key Lookup — single-row index lookupMySQL 8.4 • MariaDB 11.4

Parameters (single-row unique lookup)

Cost readout (single-row unique lookup)

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)