MySQL Query Plan

Query scans 1 table and sorts the result; examines ~2,000 rows to return 50 in 0.49 ms. Main finding: no index covers (dept_id) on employees.

Total time: 494 µs
Rows returned: 50
Rows examined: 2.00K
Operators: 4

Fix first

Primary recommendation

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

Candidate DDL (copy & run)

CREATE INDEX idx_employees_dept_id ON employees (dept_id);

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 e.name AS name,e.salary AS salary,(/*
select#2 */
select max(sh.salary)
from demodb.salary_history sh
where (sh.employee_id = e.id)) AS max_hist,(/*
select#3 */
select count(0)
from demodb.salary_history sh
where (sh.employee_id = e.id)) AS hist_count
from demodb.employees e
where (e.dept_id in (1,2,3))
order by e.salary desc
limit 50

Execution plan (tree)

Click a plan operator to inspect details.

Warnings (2)

WARN
Full table scan: employees (2000 rows)

Labeled: Table scan [employees]
WARN
1 sort operation Learn →(s) — 50 rows; may use disk-based filesort Learn →

Labeled: Sort (limit 50)

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:25Z",
  "myflames_version": "1.5.0",
  "source": {
    "type": "file"
  },
  "plan_summary": {
    "total_time_ms": 0.494,
    "rows_sent": 50,
    "rows_examined_estimate": 2000,
    "operator_count": 4,
    "max_depth": 4
  },
  "optimizer_switches": [],
  "warnings": [
    {
      "severity": "warn",
      "category": "full_scan",
      "text": "Full table scan: employees (2000 rows)",
      "source": "plan",
      "node_labels": [
        "Table scan [employees]"
      ]
    },
    {
      "severity": "warn",
      "category": "filesort",
      "text": "1 sort operation(s) — 50 rows; may use disk-based filesort",
      "source": "plan",
      "node_labels": [
        "Sort (limit 50)"
      ]
    }
  ],
  "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 1 table and sorts the result; examines ~2,000 rows to return 50 in 0.49 ms. Main finding: no index covers (dept_id) on employees.",
  "plan_tree": {
    "node_id": "n:91695df69435",
    "short_label": "Limit: 50 rows",
    "folded_label": "LIMIT starts=1 rows=50",
    "children": [
      {
        "node_id": "n:d0fc793b4e18",
        "short_label": "Sort (limit 50)",
        "folded_label": "SORT starts=1 rows=50",
        "children": [
          {
            "node_id": "n:46e026049db1",
            "short_label": "Filter: ((e.dept_id in (1,2,3)))",
            "folded_label": "FILTER ((e.dept_id in (1,2,3))) starts=1 rows=750",
            "children": [
              {
                "node_id": "n:5a93cf1ee86a",
                "short_label": "Table scan [employees]",
                "folded_label": "TABLE SCAN [employees] starts=1 rows=2000",
                "children": []
              }
            ]
          }
        ]
      }
    ]
  },
  "query": {
    "raw": "/* select#1 */ select `e`.`name` AS `name`,`e`.`salary` AS `salary`,(/* select#2 */ select max(`sh`.`salary`) from `demodb`.`salary_history` `sh` where (`sh`.`employee_id` = `e`.`id`)) AS `max_hist`,(/* select#3 */ select count(0) from `demodb`.`salary_history` `sh` where (`sh`.`employee_id` = `e`.`id`)) AS `hist_count` from `demodb`.`employees` `e` where (`e`.`dept_id` in (1,2,3)) order by `e`.`salary` desc limit 50",
    "beautified": "select e.name AS name,e.salary AS salary,(/*\nselect#2 */\nselect max(sh.salary)\nfrom demodb.salary_history sh\nwhere (sh.employee_id = e.id)) AS max_hist,(/*\nselect#3 */\nselect count(0)\nfrom demodb.salary_history sh\nwhere (sh.employee_id = e.id)) AS hist_count\nfrom demodb.employees e\nwhere (e.dept_id in (1,2,3))\norder by e.salary desc\nlimit 50"
  },
  "index_suggestions": [
    {
      "table": "employees",
      "columns": [
        "dept_id"
      ],
      "ddl": "CREATE INDEX idx_employees_dept_id ON employees (dept_id);",
      "reason": "Full scan on employees with filter on (dept_id)"
    }
  ],
  "primary_action": {
    "ref": "suggestions[0]"
  },
  "teach_hooks": [
    {
      "lesson": "filesort",
      "match": {
        "folded_label": "SORT starts=1 rows=50",
        "short_label": "Sort (limit 50)"
      },
      "controls": {
        "rows": 50,
        "row_size": 200
      },
      "note": "Sort: e.salary DESC, limit input to 50 row(s) per chunk",
      "query_sql": "/* select#1 */ select `e`.`name` AS `name`,`e`.`salary` AS `salary`,(/* select#2 */ select max(`sh`.`salary`) from `demodb`.`salary_history` `sh` where (`sh`.`employee_id` = `e`.`id`)) AS `max_hist`,(/* select#3 */ select count(0) from `demodb`.`salary_history` `sh` where (`sh`.`employee_id` = `e`.`id`)) AS `hist_count` from `demodb`.`employees` `e` where (`e`.`dept_id` in (1,2,3)) order by `e`.`salary` desc limit 50"
    },
    {
      "lesson": "filter",
      "match": {
        "folded_label": "FILTER ((e.dept_id in (1,2,3))) starts=1 rows=750",
        "short_label": "Filter: ((e.dept_id in (1,2,3)))"
      },
      "controls": {
        "input_rows": 750,
        "selectivity": 37.5
      },
      "note": "Filter: (e.dept_id in (1,2,3))",
      "query_sql": "/* select#1 */ select `e`.`name` AS `name`,`e`.`salary` AS `salary`,(/* select#2 */ select max(`sh`.`salary`) from `demodb`.`salary_history` `sh` where (`sh`.`employee_id` = `e`.`id`)) AS `max_hist`,(/* select#3 */ select count(0) from `demodb`.`salary_history` `sh` where (`sh`.`employee_id` = `e`.`id`)) AS `hist_count` from `demodb`.`employees` `e` where (`e`.`dept_id` in (1,2,3)) order by `e`.`salary` desc limit 50"
    },
    {
      "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": "/* select#1 */ select `e`.`name` AS `name`,`e`.`salary` AS `salary`,(/* select#2 */ select max(`sh`.`salary`) from `demodb`.`salary_history` `sh` where (`sh`.`employee_id` = `e`.`id`)) AS `max_hist`,(/* select#3 */ select count(0) from `demodb`.`salary_history` `sh` where (`sh`.`employee_id` = `e`.`id`)) AS `hist_count` from `demodb`.`employees` `e` where (`e`.`dept_id` in (1,2,3)) order by `e`.`salary` desc limit 50"
    }
  ],
  "operator_complexities": [
    {
      "node_id": "n:d0fc793b4e18",
      "folded_label": "SORT starts=1 rows=50",
      "short_label": "Sort (limit 50)",
      "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:5a93cf1ee86a",
      "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"
      }
    }
  ]
}

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)

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

Parameters (filter stage)

Cost readout (filter operator)

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

Parameters (full table scan cost model)

Cost readout (full table scan)

Teach: Operator deep dive

Summary

SQL

Execution plan (tree)

Warnings (2)

Suggestions (2)

myteach — interactive algorithm catalog

Lessons relevant to this plan

Raw sidecar (JSON)