MySQL Query Plan

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

Total time: -
Rows returned: 1
Rows examined: 1.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_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.

Execution plan (flamegraph)

Click a plan operator to inspect details.

Warnings (1)

WARN
Full table scan: t2 (1000 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:22Z",
  "myflames_version": "1.5.0",
  "source": {
    "type": "file"
  },
  "plan_summary": {
    "total_time_ms": 0.0,
    "rows_sent": 1,
    "rows_examined_estimate": 1001,
    "operator_count": 4,
    "max_depth": 3
  },
  "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 duplicates using temporary table (weedout)"
      ]
    },
    {
      "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 duplicates using temporary table (weedout)"
      ]
    }
  ],
  "warnings": [
    {
      "severity": "warn",
      "category": "full_scan",
      "text": "Full table scan: t2 (1000 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 ~1,001 rows to return 1. Main finding: no index covers (y, x) on t2.",
  "plan_tree": {
    "node_id": "n:974fce611738",
    "short_label": "Remove duplicates using temporary table (weedout)",
    "folded_label": "Remove duplicates using temporary table (weedout) starts=1 rows=1",
    "children": [
      {
        "node_id": "n:c2114456d668",
        "short_label": "Nested loop inner join",
        "folded_label": "NESTED LOOP INNER starts=1 rows=1",
        "children": [
          {
            "node_id": "n:64a037ea2b77",
            "short_label": "Table scan [t2]",
            "folded_label": "TABLE SCAN [t2] starts=1 rows=1000",
            "children": []
          },
          {
            "node_id": "n:8b0bb271f704",
            "short_label": "Index lookup [t1.PRIMARY]",
            "folded_label": "INDEX LOOKUP [t1.PRIMARY] starts=49 rows=1",
            "children": []
          }
        ]
      }
    ]
  },
  "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 duplicates using temporary table (weedout) starts=1 rows=1",
        "short_label": "Remove duplicates using temporary table (weedout)"
      },
      "controls": {
        "outer_rows": 1,
        "inner_matches": 5,
        "row_size": 200
      },
      "note": "Remove duplicates using temporary table (weedout)"
    },
    {
      "lesson": "nested_loop",
      "match": {
        "folded_label": "NESTED LOOP INNER starts=1 rows=1",
        "short_label": "Nested loop inner join"
      },
      "controls": {
        "outer_rows": 1000,
        "inner_rows": 49,
        "row_size": 200
      },
      "note": "Nested loop inner join"
    },
    {
      "lesson": "full_scan",
      "match": {
        "folded_label": "TABLE SCAN [t2] starts=1 rows=1000",
        "short_label": "Table scan [t2]"
      },
      "controls": {
        "rows": 1000,
        "row_size": 200,
        "selectivity": 10.0
      },
      "note": "Table scan on t2 (t2)"
    },
    {
      "lesson": "non_unique_lookup",
      "match": {
        "folded_label": "INDEX LOOKUP [t1.PRIMARY] starts=49 rows=1",
        "short_label": "Index lookup [t1.PRIMARY]"
      },
      "controls": {
        "rows": 49,
        "selectivity": 40.0,
        "covering": false
      },
      "note": "Index lookup on t1 using PRIMARY (t1)"
    }
  ],
  "operator_complexities": [
    {
      "node_id": "n:974fce611738",
      "folded_label": "Remove duplicates using temporary table (weedout) starts=1 rows=1",
      "short_label": "Remove duplicates using temporary table (weedout)",
      "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:c2114456d668",
      "folded_label": "NESTED LOOP INNER starts=1 rows=1",
      "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:64a037ea2b77",
      "folded_label": "TABLE SCAN [t2] starts=1 rows=1000",
      "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:8b0bb271f704",
      "folded_label": "INDEX LOOKUP [t1.PRIMARY] starts=49 rows=1",
      "short_label": "Index lookup [t1.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"
      }
    }
  ]
}

Primary recommendation

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

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

Parameters (full table scan cost model)

Cost readout (full table scan)

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)

Teach: Operator deep dive

Summary

Execution plan (flamegraph)

Warnings (1)

Suggestions (1)

myteach — interactive algorithm catalog

Lessons relevant to this plan

Raw sidecar (JSON)