Summary

Query scans 2 tables, hash-joins 1 pair and sorts the result; examines ~11,000 rows to return 5 in 8.5 ms. Main finding: join predicate wraps the column in CONCAT() — no regular index can be used (only a functional index on that exact expression).

Total time
8.53 ms
Rows returned
5
Rows examined
11.00K
Operators
7
Fix first

Primary recommendation

Rewrite the join condition to compare the bare column on both sides (e.g. 'a.id = b.other_id' instead of 'CONCAT(a.id)=CONCAT(b.other_id)').

Why does this help?

wrapping a column in a function means the optimizer cannot use any index on that column — every row-pair is evaluated in the server layer, making the cost O(outer × inner). Dropping the function lets MySQL/MariaDB pick an index lookup or hash joinLearn → with real selectivity, typically 100–1000× faster on mid-sized tables.

SQL

SELECT u.country, SUM(o.amount) AS total
FROM users u
  JOIN orders o ON CONCAT('u', o.user_id) = CONCAT('u', u.id)
WHERE u.status='active'
GROUP BY u.country
ORDER BY total DESC

Execution plan (flamegraph)

Click a plan operator to inspect details.

Warnings (7)

  1. ERROR

    Non-sargable join predicate: CONCAT(...) in Inner hash joinLearn → (concat('u',o.user_id) = concat('u',u.id)) — a function wrapped around the join column prevents index use.

    Labeled: Inner hash join (concat('u',o.user_id) = concat('u...

  2. WARN

    Full table scan: orders (8000 rows), users (3000 rows)

    Labeled: Table scan [orders] Table scan [users]

  3. WARN

    1 hash joinLearn →(s) — uses join_buffer_size (~6859 rows in build phase)

    Labeled: Inner hash join (concat('u',o.user_id) = concat('u...

  4. WARN

    1 sort operationLearn →(s) — 5 rows; may use disk-based filesortLearn →

    Labeled: Sort

  5. WARN

    FilesortLearn → detected with sort_buffer_size=256.0 KB. Whether it spills depends on the sort's row count and record size.

  6. WARN

    Hash joinLearn → detected but join_buffer_size is only 256.0 KB. Build/batch phase may spill to tmpdir.

  7. WARN

    Table users has no index covering (status).

Suggestions (7)

  1. HIGH

    Rewrite the join condition to compare the bare column on both sides (e.g. 'a.id = b.other_id' instead of 'CONCAT(a.id)=CONCAT(b.other_id)').

    Why?

    wrapping a column in a function means the optimizer cannot use any index on that column — every row-pair is evaluated in the server layer, making the cost O(outer × inner). Dropping the function lets MySQL/MariaDB pick an index lookup or hash joinLearn → with real selectivity, typically 100–1000× faster on mid-sized tables.

  2. HIGH

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

  3. MEDIUM

    Increase join_buffer_size if hash joins are slow or spill to disk

  4. MEDIUM

    Increase sort_buffer_size or add an ordered index to avoid filesortLearn →

  5. MEDIUM

    Check sort merge passes and query timing before increasing sort_buffer_size for this session.

    Why?

    when sort records exceed the buffer, MySQL can write sorted runs and merge them. A larger buffer may avoid that work; small sorts may already fit in memory. Test session-level changes and account for concurrent sorting sessions before changing the global default.

  6. MEDIUM

    Raise join_buffer_size per-session to 2M–8M (SET SESSION join_buffer_size = 8*1024*1024).

    Why?

    hash joinLearn → builds an in-memory hash table on the smaller input and probes from the other side. If the build side does not fit in join_buffer_size, MySQL spills to tmpdir and does a multi-pass (Grace) hash joinLearn → — each extra pass re-reads the probe side, so total I/O roughly multiplies. Bigger win: add an index on the join column — that removes the need for the join buffer entirely by turning the join into an indexed lookup.

  7. HIGH
    CREATE INDEX idx_users_status ON users (status);

Agent-ready · token savings

Feeding this plan to an AI as the myflames digest instead of raw EXPLAIN JSON cuts the prompt by 1.2× (19.6% fewer tokens).

Raw plan
1,125 tokens
myflames digest
905 tokens
Saved
220 tokens
Per 1,000 queries
~$0.66 saved

Estimate (heuristic estimate (offline)). Prices as of 2026-05-26 (Sonnet 4.6 input). Generate the digest with myflames digest.

Collected environment

Session variables

version8.4.11
innodb_buffer_pool_size128.0 MB
sort_buffer_size256.0 KB
join_buffer_size256.0 KB
tmp_table_size16.0 MB
max_heap_table_size16.0 MB
innodb_flush_log_at_trx_commit1
optimizer_switch
27 flags (click to expand)
index_merge=onindex_merge_union=onindex_merge_sort_union=onindex_merge_intersection=onengine_condition_pushdown=onindex_condition_pushdown=onmrr=onmrr_cost_based=onblock_nested_loop=onbatched_key_access=offmaterialization=onsemijoin=onloosescan=onfirstmatch=onduplicateweedout=onsubquery_materialization_cost_based=onuse_index_extensions=oncondition_fanout_filter=onderived_merge=onuse_invisible_indexes=offskip_scan=onhash_join=onsubquery_to_derived=offprefer_ordering_index=onhypergraph_optimizer=offderived_condition_pushdown=onhash_set_operations=on

Tables (click a name to expand)

demodb.ordersrows7,862data416.0 KBindexes176.0 KB

Columns (5)

  • id int
  • user_id int
  • status varchar(20)
  • amount decimal(10,2)
  • created_at datetime

Indexes (2)

  • INDEX (unnamed) (id)
  • INDEX idx_user (user_id)
demodb.usersrows3,000data176.0 KBindexes80.0 KB

Columns (5)

  • id int
  • name varchar(80)
  • country varchar(2)
  • status varchar(20)
  • created_at datetime

Indexes (2)

  • INDEX (unnamed) (id)
  • INDEX idx_country (country)

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-10-15T19:55:07Z",
  "myflames_version": "2.3.0",
  "source": {
    "type": "live",
    "engine": "mysql",
    "engine_version": "8.4.11"
  },
  "plan_summary": {
    "total_time_ms": 8.53,
    "rows_sent": 5,
    "rows_examined_estimate": 11000,
    "operator_count": 7,
    "max_depth": 6
  },
  "optimizer_switches": [
    {
      "name": "hash_join",
      "value": "on",
      "explanation": "Builds an in-memory hash table on the smaller (build) input and probes it from the other side. Uses join_buffer_size; spills to a tmp file (and tmpdir) if the build side does not fit.",
      "node_labels": [
        "Inner hash join (concat('u',o.user_id) = concat('u..."
      ]
    }
  ],
  "warnings": [
    {
      "severity": "error",
      "category": "nonsargable_join",
      "text": "Non-sargable join predicate: CONCAT(...) in Inner hash join (concat('u',o.user_id) = concat('u',u.id)) — a function wrapped around the join column prevents index use.",
      "source": "plan",
      "node_labels": [
        "Inner hash join (concat('u',o.user_id) = concat('u..."
      ]
    },
    {
      "severity": "warn",
      "category": "full_scan",
      "text": "Full table scan: orders (8000 rows), users (3000 rows)",
      "source": "plan",
      "node_labels": [
        "Table scan [orders]",
        "Table scan [users]"
      ]
    },
    {
      "severity": "warn",
      "category": "hash_join",
      "text": "1 hash join(s) — uses join_buffer_size (~6859 rows in build phase)",
      "source": "plan",
      "node_labels": [
        "Inner hash join (concat('u',o.user_id) = concat('u..."
      ]
    },
    {
      "severity": "warn",
      "category": "filesort",
      "text": "1 sort operation(s) — 5 rows; may use disk-based filesort",
      "source": "plan",
      "node_labels": [
        "Sort"
      ]
    },
    {
      "severity": "warn",
      "category": "env",
      "text": "Filesort detected with sort_buffer_size=256.0 KB. Whether it spills depends on the sort's row count and record size.",
      "source": "environment"
    },
    {
      "severity": "warn",
      "category": "env",
      "text": "Hash join detected but join_buffer_size is only 256.0 KB. Build/batch phase may spill to tmpdir.",
      "source": "environment"
    },
    {
      "severity": "warn",
      "category": "env",
      "text": "Table users has no index covering (status).",
      "source": "environment"
    }
  ],
  "suggestions": [
    {
      "severity": "high",
      "category": "rewrite",
      "action": "Rewrite the join condition to compare the bare column on both sides (e.g. 'a.id = b.other_id' instead of 'CONCAT(a.id)=CONCAT(b.other_id)').",
      "source": "plan",
      "why": "wrapping a column in a function means the optimizer cannot use any index on that column — every row-pair is evaluated in the server layer, making the cost O(outer × inner). Dropping the function lets MySQL/MariaDB pick an index lookup or hash join with real selectivity, typically 100–1000× faster on mid-sized tables."
    },
    {
      "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 join_buffer_size if hash joins are slow or spill to disk",
      "source": "plan",
      "target_variable": "join_buffer_size"
    },
    {
      "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"
    },
    {
      "severity": "medium",
      "category": "tuning_variable",
      "action": "Check sort merge passes and query timing before increasing sort_buffer_size for this session.",
      "source": "environment",
      "why": "when sort records exceed the buffer, MySQL can write sorted runs and merge them. A larger buffer may avoid that work; small sorts may already fit in memory. Test session-level changes and account for concurrent sorting sessions before changing the global default.",
      "target_variable": "sort_buffer_size"
    },
    {
      "severity": "medium",
      "category": "tuning_variable",
      "action": "Raise join_buffer_size per-session to 2M–8M (SET SESSION join_buffer_size = 8*1024*1024).",
      "source": "environment",
      "why": "hash join builds an in-memory hash table on the smaller input and probes from the other side. If the build side does not fit in join_buffer_size, MySQL spills to tmpdir and does a multi-pass (Grace) hash join — each extra pass re-reads the probe side, so total I/O roughly multiplies. Bigger win: add an index on the join column — that removes the need for the join buffer entirely by turning the join into an indexed lookup.",
      "target_variable": "join_buffer_size"
    },
    {
      "severity": "high",
      "category": "index",
      "action": "CREATE INDEX idx_users_status ON users (status);",
      "source": "environment"
    }
  ],
  "executive_summary": "Query scans 2 tables, hash-joins 1 pair and sorts the result; examines ~11,000 rows to return 5 in 8.5 ms. Main finding: join predicate wraps the column in CONCAT() — no regular index can be used (only a functional index on that exact expression).",
  "plan_tree": {
    "node_id": "n:6a06df7aace7",
    "short_label": "Sort",
    "folded_label": "SORT starts=1 rows=5",
    "children": [
      {
        "node_id": "n:fe47e746b576",
        "short_label": "Table scan [<temporary>]",
        "folded_label": "TABLE SCAN [<temporary>] starts=1 rows=5",
        "children": [
          {
            "node_id": "n:288184b1ac9b",
            "short_label": "Aggregate",
            "folded_label": "AGGREGATE starts=1 rows=5",
            "children": [
              {
                "node_id": "n:5a3c13968281",
                "short_label": "Inner hash join (concat('u',o.user_id) = concat('u...",
                "folded_label": "Inner hash join (concat('u',o.user_id) = concat('u',u.id)) starts=1 rows=6859",
                "children": [
                  {
                    "node_id": "n:13f9abc9fc00",
                    "short_label": "Table scan [orders]",
                    "folded_label": "TABLE SCAN [orders] starts=1 rows=8000",
                    "children": []
                  },
                  {
                    "node_id": "n:f52549d0242a",
                    "short_label": "Filter: ((u.status = 'active'))",
                    "folded_label": "FILTER ((u.status = 'active')) starts=1 rows=2572",
                    "children": [
                      {
                        "node_id": "n:3faf9eb683af",
                        "short_label": "Table scan [users]",
                        "folded_label": "TABLE SCAN [users] starts=1 rows=3000",
                        "children": []
                      }
                    ]
                  }
                ]
              }
            ]
          }
        ]
      }
    ]
  },
  "query": {
    "raw": "SELECT u.country, SUM(o.amount) AS total FROM users u JOIN orders o ON CONCAT('u', o.user_id) = CONCAT('u', u.id) WHERE u.status='active' GROUP BY u.country ORDER BY total DESC",
    "beautified": "SELECT u.country, SUM(o.amount) AS total\nFROM users u\n  JOIN orders o ON CONCAT('u', o.user_id) = CONCAT('u', u.id)\nWHERE u.status='active'\nGROUP BY u.country\nORDER BY total DESC"
  },
  "index_suggestions": [
    {
      "table": "users",
      "columns": [
        "status"
      ],
      "ddl": "CREATE INDEX idx_users_status ON users (status);",
      "reason": "Full scan on users with filter on (status)"
    }
  ],
  "primary_action": {
    "ref": "suggestions[0]"
  },
  "collected": {
    "variables": {
      "bulk_insert_buffer_size": "8388608",
      "character_set_connection": "utf8mb4",
      "character_set_server": "utf8mb4",
      "collation_connection": "utf8mb4_0900_ai_ci",
      "collation_server": "utf8mb4_0900_ai_ci",
      "eq_range_index_dive_limit": "200",
      "innodb_buffer_pool_instances": "1",
      "innodb_buffer_pool_size": "134217728",
      "innodb_file_per_table": "ON",
      "innodb_flush_log_at_trx_commit": "1",
      "innodb_flush_method": "O_DIRECT",
      "innodb_io_capacity": "10000",
      "innodb_log_buffer_size": "67108864",
      "innodb_log_file_size": "50331648",
      "internal_tmp_mem_storage_engine": "TempTable",
      "join_buffer_size": "262144",
      "max_allowed_packet": "67108864",
      "max_connections": "151",
      "max_heap_table_size": "16777216",
      "optimizer_prune_level": "1",
      "optimizer_search_depth": "62",
      "optimizer_switch": "index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,use_invisible_indexes=off,skip_scan=on,hash_join=on,subquery_to_derived=off,prefer_ordering_index=on,hypergraph_optimizer=off,derived_condition_pushdown=on,hash_set_operations=on",
      "range_optimizer_max_mem_size": "8388608",
      "read_buffer_size": "131072",
      "read_rnd_buffer_size": "262144",
      "sort_buffer_size": "262144",
      "table_open_cache": "4000",
      "thread_cache_size": "9",
      "tmp_table_size": "16777216",
      "version": "8.4.11",
      "version_comment": "MySQL Community Server - GPL",
      "version_compile_os": "Linux"
    },
    "stats": {
      "demodb.users": {
        "table_schema": "demodb",
        "table_name": "users",
        "table_rows": 3000,
        "data_length": 180224,
        "index_length": 81920,
        "data_free": 0,
        "auto_increment": 4096,
        "engine": "InnoDB",
        "row_format": "Dynamic"
      },
      "demodb.orders": {
        "table_schema": "demodb",
        "table_name": "orders",
        "table_rows": 7862,
        "data_length": 425984,
        "index_length": 180224,
        "data_free": 0,
        "auto_increment": 8192,
        "engine": "InnoDB",
        "row_format": "Dynamic"
      }
    },
    "schema": {
      "demodb.users": {
        "ddl": "CREATE TABLE `users` (\n  `id` int NOT NULL AUTO_INCREMENT,\n  `name` varchar(80) DEFAULT NULL,\n  `country` varchar(2) DEFAULT NULL,\n  `status` varchar(20) DEFAULT NULL,\n  `created_at` datetime DEFAULT NULL,\n  PRIMARY KEY (`id`),\n  KEY `idx_country` (`country`)\n) ENGINE=InnoDB AUTO_INCREMENT=4096 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci",
        "table_name": "users",
        "columns": [
          {
            "name": "id",
            "type": "int",
            "rest": "NOT NULL AUTO_INCREMENT"
          },
          {
            "name": "name",
            "type": "varchar(80)",
            "rest": "DEFAULT NULL"
          },
          {
            "name": "country",
            "type": "varchar(2)",
            "rest": "DEFAULT NULL"
          },
          {
            "name": "status",
            "type": "varchar(20)",
            "rest": "DEFAULT NULL"
          },
          {
            "name": "created_at",
            "type": "datetime",
            "rest": "DEFAULT NULL"
          }
        ],
        "indexes": [
          {
            "kind": "PRIMARY KEY",
            "name": null,
            "columns": [
              "id"
            ],
            "parts": [
              {
                "name": "id",
                "length": null,
                "desc": false,
                "expression": false
              }
            ],
            "using": null
          },
          {
            "kind": "KEY",
            "name": "idx_country",
            "columns": [
              "country"
            ],
            "parts": [
              {
                "name": "country",
                "length": null,
                "desc": false,
                "expression": false
              }
            ],
            "using": null
          }
        ],
        "engine": "InnoDB",
        "charset": "utf8mb4"
      },
      "demodb.orders": {
        "ddl": "CREATE TABLE `orders` (\n  `id` int NOT NULL AUTO_INCREMENT,\n  `user_id` int DEFAULT NULL,\n  `status` varchar(20) DEFAULT NULL,\n  `amount` decimal(10,2) DEFAULT NULL,\n  `created_at` datetime DEFAULT NULL,\n  PRIMARY KEY (`id`),\n  KEY `idx_user` (`user_id`)\n) ENGINE=InnoDB AUTO_INCREMENT=8192 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci",
        "table_name": "orders",
        "columns": [
          {
            "name": "id",
            "type": "int",
            "rest": "NOT NULL AUTO_INCREMENT"
          },
          {
            "name": "user_id",
            "type": "int",
            "rest": "DEFAULT NULL"
          },
          {
            "name": "status",
            "type": "varchar(20)",
            "rest": "DEFAULT NULL"
          },
          {
            "name": "amount",
            "type": "decimal(10,2)",
            "rest": "DEFAULT NULL"
          },
          {
            "name": "created_at",
            "type": "datetime",
            "rest": "DEFAULT NULL"
          }
        ],
        "indexes": [
          {
            "kind": "PRIMARY KEY",
            "name": null,
            "columns": [
              "id"
            ],
            "parts": [
              {
                "name": "id",
                "length": null,
                "desc": false,
                "expression": false
              }
            ],
            "using": null
          },
          {
            "kind": "KEY",
            "name": "idx_user",
            "columns": [
              "user_id"
            ],
            "parts": [
              {
                "name": "user_id",
                "length": null,
                "desc": false,
                "expression": false
              }
            ],
            "using": null
          }
        ],
        "engine": "InnoDB",
        "charset": "utf8mb4"
      }
    }
  },
  "teach_hooks": [
    {
      "lesson": "filesort",
      "match": {
        "folded_label": "SORT starts=1 rows=5",
        "short_label": "Sort"
      },
      "controls": {
        "rows": 5,
        "row_size": 200,
        "sbs": 262144
      },
      "note": "Sort: total DESC",
      "query_sql": "SELECT u.country, SUM(o.amount) AS total FROM users u JOIN orders o ON CONCAT('u', o.user_id) = CONCAT('u', u.id) WHERE u.status='active' GROUP BY u.country ORDER BY total DESC"
    },
    {
      "lesson": "full_scan",
      "match": {
        "folded_label": "TABLE SCAN [<temporary>] starts=1 rows=5",
        "short_label": "Table scan [<temporary>]"
      },
      "controls": {
        "rows": 5,
        "row_size": 200,
        "selectivity": 100.0
      },
      "note": "Table scan on <temporary> (<temporary>)",
      "query_sql": "SELECT u.country, SUM(o.amount) AS total FROM users u JOIN orders o ON CONCAT('u', o.user_id) = CONCAT('u', u.id) WHERE u.status='active' GROUP BY u.country ORDER BY total DESC"
    },
    {
      "lesson": "tmp",
      "match": {
        "folded_label": "AGGREGATE starts=1 rows=5",
        "short_label": "Aggregate"
      },
      "controls": {
        "rows": 5,
        "row_size": 200,
        "tmp_size": 16777216,
        "max_heap": 16777216,
        "engine": "temptable"
      },
      "note": "Aggregate using temporary table",
      "query_sql": "SELECT u.country, SUM(o.amount) AS total FROM users u JOIN orders o ON CONCAT('u', o.user_id) = CONCAT('u', u.id) WHERE u.status='active' GROUP BY u.country ORDER BY total DESC"
    },
    {
      "lesson": "hash",
      "match": {
        "folded_label": "Inner hash join (concat('u',o.user_id) = concat('u',u.id)) starts=1 rows=6859",
        "short_label": "Inner hash join (concat('u',o.user_id) = concat('u..."
      },
      "controls": {
        "build_rows": 2572,
        "probe_rows": 8000,
        "row_size": 200,
        "jbs": 262144
      },
      "note": "Inner hash join (concat('u',o.user_id) = concat('u',u.id))",
      "query_sql": "SELECT u.country, SUM(o.amount) AS total FROM users u JOIN orders o ON CONCAT('u', o.user_id) = CONCAT('u', u.id) WHERE u.status='active' GROUP BY u.country ORDER BY total DESC"
    },
    {
      "lesson": "full_scan",
      "match": {
        "folded_label": "TABLE SCAN [orders] starts=1 rows=8000",
        "short_label": "Table scan [orders]"
      },
      "controls": {
        "rows": 8000,
        "row_size": 200,
        "selectivity": 100.0
      },
      "note": "Table scan on o (orders)",
      "query_sql": "SELECT u.country, SUM(o.amount) AS total FROM users u JOIN orders o ON CONCAT('u', o.user_id) = CONCAT('u', u.id) WHERE u.status='active' GROUP BY u.country ORDER BY total DESC"
    },
    {
      "lesson": "filter",
      "match": {
        "folded_label": "FILTER ((u.status = 'active')) starts=1 rows=2572",
        "short_label": "Filter: ((u.status = 'active'))"
      },
      "controls": {
        "input_rows": 2572,
        "selectivity": 10.0
      },
      "note": "Filter: (u.`status` = 'active')",
      "query_sql": "SELECT u.country, SUM(o.amount) AS total FROM users u JOIN orders o ON CONCAT('u', o.user_id) = CONCAT('u', u.id) WHERE u.status='active' GROUP BY u.country ORDER BY total DESC"
    },
    {
      "lesson": "full_scan",
      "match": {
        "folded_label": "TABLE SCAN [users] starts=1 rows=3000",
        "short_label": "Table scan [users]"
      },
      "controls": {
        "rows": 3000,
        "row_size": 200,
        "selectivity": 100.0
      },
      "note": "Table scan on u (users)",
      "query_sql": "SELECT u.country, SUM(o.amount) AS total FROM users u JOIN orders o ON CONCAT('u', o.user_id) = CONCAT('u', u.id) WHERE u.status='active' GROUP BY u.country ORDER BY total DESC"
    }
  ],
  "operator_complexities": [
    {
      "node_id": "n:6a06df7aace7",
      "folded_label": "SORT starts=1 rows=5",
      "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:fe47e746b576",
      "folded_label": "TABLE SCAN [<temporary>] starts=1 rows=5",
      "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:5a3c13968281",
      "folded_label": "Inner hash join (concat('u',o.user_id) = concat('u',u.id)) starts=1 rows=6859",
      "short_label": "Inner hash join (concat('u',o.user_id) = concat('u...",
      "complexity": {
        "big_o": "O(n + m)",
        "short": "n + m",
        "severity": "good",
        "rationale": "Hash join: builds a hash on one side (O(m)), streams the other past it (O(n)). Disk-spill does NOT change the asymptotic class — only the constants.",
        "confidence": "exact",
        "learn_more": "hash_join"
      }
    },
    {
      "node_id": "n:13f9abc9fc00",
      "folded_label": "TABLE SCAN [orders] starts=1 rows=8000",
      "short_label": "Table scan [orders]",
      "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:3faf9eb683af",
      "folded_label": "TABLE SCAN [users] starts=1 rows=3000",
      "short_label": "Table scan [users]",
      "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"
      }
    }
  ]
}

Teach: Operator deep dive