MySQL Query Plan

Query scans 1 table; examines ~2,001 rows to return 49 in 0.35 ms. Main finding: no index covers (y, x) on t2.

Total time: 352 µs
Rows returned: 49
Rows examined: 2.00K
Operators: 5

Fix first

Primary recommendation

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

Candidate DDL (copy & run)

CREATE INDEX idx_t2_y_x ON t2 (y, x);

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 testdb.t1.id AS id,testdb.t1.a AS a,testdb.t1.b AS b,testdb.t1.c AS c,testdb.t1.d AS d
from testdb.t1 semi
  join (testdb.t2)
where ((testdb.t1.id = testdb.t2.x) and (testdb.t2.y < 100))

Execution plan (flamegraph)

Click a plan operator to inspect details.

Warnings (1)

WARN
Full table scan: t2 (2000 rows)

Labeled: Table scan [t2]

Suggestions (1)

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

Glossary (7 terms)

duplicate weedout: MySQL ran the join normally, then used a temporary table to throw away duplicate rows. Not the fastest semijoin strategy, but the most general.; Semijoin strategy: the subquery is run as a plain inner join, and a temporary table keyed on the outer row IDs removes duplicates at the end. The fallback strategy when FirstMatch and LooseScan can't be used.
firstmatch: MySQL stops looking as soon as it finds one matching row for your IN/EXISTS clause. Efficient for 'does any match exist?' style queries.; Semijoin strategy: as soon as a match is found for the current outer row, skip to the next one. Cheapest semijoin when the inner predicate has a selective index.
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.
loosescan: MySQL walks the inner table's index once, cleverly skipping past duplicates.; Semijoin strategy: scans an index on the inner table exactly once, using index key boundaries to skip over duplicate values instead of emitting them. Relies on the inner side having an index with the semijoin column as a leading key.
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.
semijoin: Instead of running the subquery once per outer row, MySQL turned your IN/EXISTS clause into a join, then de-duplicated the results so each outer row appears once.; The optimizer transforms ``WHERE col IN (SELECT …)`` or EXISTS subqueries into a join against the outer query, then picks one of four strategies (FirstMatch, LooseScan, DuplicateWeedout, Materialization) to make sure each outer row appears at most once. Controlled by ``semijoin`` + per-strategy switches.
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.

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:27Z",
  "myflames_version": "1.5.0",
  "source": {
    "type": "file"
  },
  "plan_summary": {
    "total_time_ms": 0.352,
    "rows_sent": 49,
    "rows_examined_estimate": 2001,
    "operator_count": 5,
    "max_depth": 4
  },
  "optimizer_switches": [
    {
      "name": "semijoin",
      "value": "on",
      "explanation": "IN / EXISTS subqueries are rewritten into a semi-join with the outer query and then resolved via one of the semijoin strategies (firstmatch, loosescan, duplicateweedout, or materialization).",
      "node_labels": [
        "Remove duplicate t1 rows using temporary table (we..."
      ]
    },
    {
      "name": "duplicateweedout",
      "value": "on",
      "explanation": "Semijoin strategy: runs the subquery as a normal inner join and then removes duplicate outer rows using a temporary table keyed on their row IDs.",
      "node_labels": [
        "Remove duplicate t1 rows using temporary table (we..."
      ]
    }
  ],
  "warnings": [
    {
      "severity": "warn",
      "category": "full_scan",
      "text": "Full table scan: t2 (2000 rows)",
      "source": "plan",
      "node_labels": [
        "Table scan [t2]"
      ]
    }
  ],
  "suggestions": [
    {
      "severity": "high",
      "category": "index",
      "action": "Add indexes on filter/join columns to avoid full table scans",
      "source": "plan"
    }
  ],
  "executive_summary": "Query scans 1 table; examines ~2,001 rows to return 49 in 0.35 ms. Main finding: no index covers (y, x) on t2.",
  "plan_tree": {
    "node_id": "n:0fc991f45412",
    "short_label": "Remove duplicate t1 rows using temporary table (we...",
    "folded_label": "Remove duplicate t1 rows using temporary table (weedout) starts=1 rows=49",
    "children": [
      {
        "node_id": "n:e32c3e0bcf9b",
        "short_label": "Nested loop inner join",
        "folded_label": "NESTED LOOP INNER starts=1 rows=49",
        "children": [
          {
            "node_id": "n:215e07533821",
            "short_label": "Filter: (((t2.y < 100) and (t2.x is not null)))",
            "folded_label": "FILTER (((t2.y < 100) and (t2.x is not null))) starts=1 rows=49",
            "children": [
              {
                "node_id": "n:39a4125027ad",
                "short_label": "Table scan [t2]",
                "folded_label": "TABLE SCAN [t2] starts=1 rows=2000",
                "children": []
              }
            ]
          },
          {
            "node_id": "n:5c9e8b01da7e",
            "short_label": "Single-row lookup [t1.PRIMARY]",
            "folded_label": "SINGLE ROW LOOKUP [t1.PRIMARY] starts=49 rows=1",
            "children": []
          }
        ]
      }
    ]
  },
  "query": {
    "raw": "/* select#1 */ select `testdb`.`t1`.`id` AS `id`,`testdb`.`t1`.`a` AS `a`,`testdb`.`t1`.`b` AS `b`,`testdb`.`t1`.`c` AS `c`,`testdb`.`t1`.`d` AS `d` from `testdb`.`t1` semi join (`testdb`.`t2`) where ((`testdb`.`t1`.`id` = `testdb`.`t2`.`x`) and (`testdb`.`t2`.`y` < 100))",
    "beautified": "select testdb.t1.id AS id,testdb.t1.a AS a,testdb.t1.b AS b,testdb.t1.c AS c,testdb.t1.d AS d\nfrom testdb.t1 semi\n  join (testdb.t2)\nwhere ((testdb.t1.id = testdb.t2.x) and (testdb.t2.y < 100))"
  },
  "index_suggestions": [
    {
      "table": "t2",
      "columns": [
        "y",
        "x"
      ],
      "ddl": "CREATE INDEX idx_t2_y_x ON t2 (y, x);",
      "reason": "Full scan on t2 with filter on (y, x)"
    }
  ],
  "primary_action": {
    "ref": "suggestions[0]"
  },
  "teach_hooks": [
    {
      "lesson": "semijoin_weedout",
      "match": {
        "folded_label": "Remove duplicate t1 rows using temporary table (weedout) starts=1 rows=49",
        "short_label": "Remove duplicate t1 rows using temporary table (we..."
      },
      "controls": {
        "outer_rows": 49,
        "inner_matches": 5,
        "row_size": 200
      },
      "note": "Remove duplicate t1 rows using temporary table (weedout)",
      "query_sql": "/* select#1 */ select `testdb`.`t1`.`id` AS `id`,`testdb`.`t1`.`a` AS `a`,`testdb`.`t1`.`b` AS `b`,`testdb`.`t1`.`c` AS `c`,`testdb`.`t1`.`d` AS `d` from `testdb`.`t1` semi join (`testdb`.`t2`) where ((`testdb`.`t1`.`id` = `testdb`.`t2`.`x`) and (`testdb`.`t2`.`y` < 100))"
    },
    {
      "lesson": "nested_loop",
      "match": {
        "folded_label": "NESTED LOOP INNER starts=1 rows=49",
        "short_label": "Nested loop inner join"
      },
      "controls": {
        "outer_rows": 49,
        "inner_rows": 49,
        "row_size": 200
      },
      "note": "Nested loop inner join",
      "query_sql": "/* select#1 */ select `testdb`.`t1`.`id` AS `id`,`testdb`.`t1`.`a` AS `a`,`testdb`.`t1`.`b` AS `b`,`testdb`.`t1`.`c` AS `c`,`testdb`.`t1`.`d` AS `d` from `testdb`.`t1` semi join (`testdb`.`t2`) where ((`testdb`.`t1`.`id` = `testdb`.`t2`.`x`) and (`testdb`.`t2`.`y` < 100))"
    },
    {
      "lesson": "filter",
      "match": {
        "folded_label": "FILTER (((t2.y < 100) and (t2.x is not null))) starts=1 rows=49",
        "short_label": "Filter: (((t2.y < 100) and (t2.x is not null)))"
      },
      "controls": {
        "input_rows": 49,
        "selectivity": 7.4
      },
      "note": "Filter: ((t2.y < 100) and (t2.x is not null))",
      "query_sql": "/* select#1 */ select `testdb`.`t1`.`id` AS `id`,`testdb`.`t1`.`a` AS `a`,`testdb`.`t1`.`b` AS `b`,`testdb`.`t1`.`c` AS `c`,`testdb`.`t1`.`d` AS `d` from `testdb`.`t1` semi join (`testdb`.`t2`) where ((`testdb`.`t1`.`id` = `testdb`.`t2`.`x`) and (`testdb`.`t2`.`y` < 100))"
    },
    {
      "lesson": "full_scan",
      "match": {
        "folded_label": "TABLE SCAN [t2] starts=1 rows=2000",
        "short_label": "Table scan [t2]"
      },
      "controls": {
        "rows": 2000,
        "row_size": 200,
        "selectivity": 100.0
      },
      "note": "Table scan on t2 (t2)",
      "query_sql": "/* select#1 */ select `testdb`.`t1`.`id` AS `id`,`testdb`.`t1`.`a` AS `a`,`testdb`.`t1`.`b` AS `b`,`testdb`.`t1`.`c` AS `c`,`testdb`.`t1`.`d` AS `d` from `testdb`.`t1` semi join (`testdb`.`t2`) where ((`testdb`.`t1`.`id` = `testdb`.`t2`.`x`) and (`testdb`.`t2`.`y` < 100))"
    },
    {
      "lesson": "unique_lookup",
      "match": {
        "folded_label": "SINGLE ROW LOOKUP [t1.PRIMARY] starts=49 rows=1",
        "short_label": "Single-row lookup [t1.PRIMARY]"
      },
      "controls": {
        "rows": 49,
        "covering": false
      },
      "note": "Single-row index lookup on t1 using PRIMARY (id=t2.x) (t1)",
      "query_sql": "/* select#1 */ select `testdb`.`t1`.`id` AS `id`,`testdb`.`t1`.`a` AS `a`,`testdb`.`t1`.`b` AS `b`,`testdb`.`t1`.`c` AS `c`,`testdb`.`t1`.`d` AS `d` from `testdb`.`t1` semi join (`testdb`.`t2`) where ((`testdb`.`t1`.`id` = `testdb`.`t2`.`x`) and (`testdb`.`t2`.`y` < 100))"
    }
  ],
  "operator_complexities": [
    {
      "node_id": "n:0fc991f45412",
      "folded_label": "Remove duplicate t1 rows using temporary table (weedout) starts=1 rows=49",
      "short_label": "Remove duplicate t1 rows using temporary table (we...",
      "complexity": {
        "big_o": "O(n log n)",
        "short": "n log n",
        "severity": "medium",
        "rationale": "DuplicateWeedout semijoin: runs as a plain inner join, then drops duplicates using a temp-table index on the outer row IDs.",
        "confidence": "typical",
        "learn_more": "duplicate_weedout"
      }
    },
    {
      "node_id": "n:e32c3e0bcf9b",
      "folded_label": "NESTED LOOP INNER starts=1 rows=49",
      "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:39a4125027ad",
      "folded_label": "TABLE SCAN [t2] starts=1 rows=2000",
      "short_label": "Table scan [t2]",
      "complexity": {
        "big_o": "O(n)",
        "short": "n",
        "severity": "medium",
        "rationale": "Full table scan: the storage engine returns every row; cost scales with the table size.",
        "confidence": "exact",
        "learn_more": "full_table_scan"
      }
    },
    {
      "node_id": "n:5c9e8b01da7e",
      "folded_label": "SINGLE ROW LOOKUP [t1.PRIMARY] starts=49 rows=1",
      "short_label": "Single-row lookup [t1.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 (flamegraph)

Warnings (1)

Suggestions (1)

myteach — interactive algorithm catalog

Lessons relevant to this plan

Raw sidecar (JSON)

Semijoin Duplicate Weedout — dedup via temp tableMySQL 8.4 • MariaDB 11.4

Parameters (Semijoin Duplicate Weedout)

Cost readout (Semijoin Duplicate Weedout)

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)

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

Parameters (full table scan cost model)

Cost readout (full table scan)

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 (flamegraph)

Warnings (1)

Suggestions (1)

myteach — interactive algorithm catalog

Lessons relevant to this plan

Raw sidecar (JSON)