MySQL Query Plan

Query scans 1 table; returns 24,950 rows in 19 ms. Main finding: no index covers (y, x) on t2.

Total time: 18.81 ms
Rows returned: 24.95K
Rows examined: 14.47K
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.

SQL

select straight_join 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.t2
  join testdb.t1
where ((testdb.t1.a = testdb.t2.x) and (testdb.t2.y < 1000))

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 (4 terms)

batched key access: BKA is an optimization that can reorder inner-table fetches to reduce random disk I/O — but it is **off by default** in MySQL 8, and it relies on MRR also being enabled. If you see this term in myflames output, it means you (or a DBA) turned it on explicitly and the optimizer picked it for this plan.; BKA: collects keys from the outer into the join buffer, sorts them, then uses Multi-Range Read on the inner side to fetch rows in (mostly) clustered-index order. Cuts random I/O on large indexed joins. BKA is **off by default** on MySQL 8.0 / 8.4 (verified in sql/sys_vars.cc — OPTIMIZER_SWITCH_BKA is not in the OPTIMIZER_SWITCH_DEFAULT mask) and depends on MRR being enabled; enable both via ``optimizer_switch='batched_key_access=on,mrr_cost_based=off'``. myflames only flags BKA when you've explicitly enabled it and the plan actually picked it.
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.
join buffer size: The memory MySQL uses when it has to join tables without a handy index. If this is too small for a big join, MySQL spills to disk and slows down.; Per-join allocation, freed after the join completes. Feeds the build side of a hash join or the outer-row batches of a BNL. If the build side of a hash join doesn't fit, MySQL spills to a multi-pass (Grace) hash join. An index on the join column is almost always a bigger win than raising this.
mrr: Instead of fetching rows one at a time in index order (which causes random jumps on disk), MySQL collects the row locations first, sorts them, and fetches them in the order they're physically stored. Much faster on disk-bound queries.; Multi-Range Read: the optimizer collects all row IDs from a secondary-index range scan, sorts them by primary key, and then fetches rows in (mostly) physical order. This converts random I/O on the clustered index into sequential I/O, which matters most on spinning disks but still helps SSDs via better cache locality.

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

batched key access

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": 18.808,
    "rows_sent": 24950,
    "rows_examined_estimate": 14475,
    "operator_count": 4,
    "max_depth": 3
  },
  "optimizer_switches": [
    {
      "name": "batched_key_access",
      "value": "on",
      "explanation": "Collects keys from the outer table into the join buffer, sorts them, then uses MRR to fetch inner rows in (mostly) index order — cuts random I/O on large indexed joins.",
      "node_labels": [
        "Batched key access inner join"
      ]
    },
    {
      "name": "join_cache_bka",
      "value": "on",
      "explanation": "MariaDB variant of batched_key_access: inner rows are fetched in batches keyed from the join buffer, using MRR to reorder I/O.",
      "node_labels": [
        "Batched key access inner join"
      ]
    },
    {
      "name": "mrr",
      "value": "on",
      "explanation": "Multi-Range Read: sorts row IDs before fetching rows so InnoDB reads pages in roughly primary-key order, turning random I/O into sequential I/O on range scans.",
      "node_labels": [
        "Multi-range index lookup on t1 using idx_a (a=t2.x)"
      ]
    }
  ],
  "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; returns 24,950 rows in 19 ms. Main finding: no index covers (y, x) on t2.",
  "plan_tree": {
    "node_id": "n:a5fa70f81162",
    "short_label": "Batched key access inner join",
    "folded_label": "Batched key access inner join starts=1 rows=24950",
    "children": [
      {
        "node_id": "n:f57cd32a81a5",
        "short_label": "Filter: (((t2.y < 1000) and (t2.x is not null)))",
        "folded_label": "FILTER (((t2.y < 1000) and (t2.x is not null))) starts=1 rows=499",
        "children": [
          {
            "node_id": "n:a7a05e53c0de",
            "short_label": "Table scan [t2]",
            "folded_label": "TABLE SCAN [t2] starts=1 rows=2000",
            "children": []
          }
        ]
      },
      {
        "node_id": "n:5f37843a8944",
        "short_label": "Multi-range index lookup on t1 using idx_a (a=t2.x)",
        "folded_label": "Multi-range index lookup on t1 using idx_a (a=t2.x) starts=2 rows=12475",
        "children": []
      }
    ]
  },
  "query": {
    "raw": "/* select#1 */ select straight_join `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`.`t2` join `testdb`.`t1` where ((`testdb`.`t1`.`a` = `testdb`.`t2`.`x`) and (`testdb`.`t2`.`y` < 1000))",
    "beautified": "select straight_join 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.t2\n  join testdb.t1\nwhere ((testdb.t1.a = testdb.t2.x) and (testdb.t2.y < 1000))"
  },
  "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": "filter",
      "match": {
        "folded_label": "FILTER (((t2.y < 1000) and (t2.x is not null))) starts=1 rows=499",
        "short_label": "Filter: (((t2.y < 1000) and (t2.x is not null)))"
      },
      "controls": {
        "input_rows": 499,
        "selectivity": 74.9
      },
      "note": "Filter: ((t2.y < 1000) and (t2.x is not null))",
      "query_sql": "/* select#1 */ select straight_join `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`.`t2` join `testdb`.`t1` where ((`testdb`.`t1`.`a` = `testdb`.`t2`.`x`) and (`testdb`.`t2`.`y` < 1000))"
    },
    {
      "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 straight_join `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`.`t2` join `testdb`.`t1` where ((`testdb`.`t1`.`a` = `testdb`.`t2`.`x`) and (`testdb`.`t2`.`y` < 1000))"
    },
    {
      "lesson": "bka_join",
      "match": {
        "folded_label": "Multi-range index lookup on t1 using idx_a (a=t2.x) starts=2 rows=12475",
        "short_label": "Multi-range index lookup on t1 using idx_a (a=t2.x)"
      },
      "controls": {
        "outer_rows": 24950,
        "inner_rows": 24950,
        "row_size": 200,
        "key_size": 16
      },
      "note": "Multi-range index lookup on t1 using idx_a (a=t2.x) (t1)",
      "query_sql": "/* select#1 */ select straight_join `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`.`t2` join `testdb`.`t1` where ((`testdb`.`t1`.`a` = `testdb`.`t2`.`x`) and (`testdb`.`t2`.`y` < 1000))"
    }
  ],
  "operator_complexities": [
    {
      "node_id": "n:a5fa70f81162",
      "folded_label": "Batched key access inner join starts=1 rows=24950",
      "short_label": "Batched key access inner join",
      "complexity": {
        "big_o": "O(n · log m)",
        "short": "n · log m",
        "severity": "medium",
        "rationale": "Batched Key Access: outer keys are buffered and sorted, then the inner side is fetched via Multi-Range Read in clustered order. Cuts random I/O but keeps the index-descent cost per outer row.",
        "confidence": "typical",
        "learn_more": "batched_key_access"
      }
    },
    {
      "node_id": "n:a7a05e53c0de",
      "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:5f37843a8944",
      "folded_label": "Multi-range index lookup on t1 using idx_a (a=t2.x) starts=2 rows=12475",
      "short_label": "Multi-range index lookup on t1 using idx_a (a=t2.x)",
      "complexity": {
        "big_o": "O(log n + k)",
        "short": "log n + k",
        "severity": "good",
        "rationale": "Index range scan: one B+tree descent to the range start, then a sequential walk over k matching index entries.",
        "confidence": "exact",
        "learn_more": "index_range_scan"
      }
    }
  ]
}

Primary recommendation

SQL

Execution plan (flamegraph)

Warnings (1)

Suggestions (1)

myteach — interactive algorithm catalog

Lessons relevant to this plan

Raw sidecar (JSON)

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)

Batched Key Access (BKA) join — batch, sort, MRRMySQL 8.4 • MariaDB 11.4

Parameters (Batched Key Access join)

BKA cost model

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)