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
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)
-
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... -
WARN
Full table scan: orders (8000 rows), users (3000 rows)
Labeled:
Table scan [orders]Table scan [users] -
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... -
WARN
1 sort operationLearn →(s) — 5 rows; may use disk-based filesortLearn →
Labeled:
Sort -
WARN
FilesortLearn → detected with sort_buffer_size=256.0 KB. Whether it spills depends on the sort's row count and record size.
-
WARN
Hash joinLearn → detected but join_buffer_size is only 256.0 KB. Build/batch phase may spill to tmpdir.
-
WARN
Table users has no index covering (status).
Suggestions (7)
-
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.
-
HIGH
Add indexes on filter/join columns to avoid full table scans
-
MEDIUM
Increase join_buffer_size if hash joins are slow or spill to disk
-
MEDIUM
Increase sort_buffer_size or add an ordered index to avoid filesortLearn →
-
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.
-
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.
-
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
| version | 8.4.11 |
|---|---|
| innodb_buffer_pool_size | 128.0 MB |
| sort_buffer_size | 256.0 KB |
| join_buffer_size | 256.0 KB |
| tmp_table_size | 16.0 MB |
| max_heap_table_size | 16.0 MB |
| innodb_flush_log_at_trx_commit | 1 |
| 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.orders
Columns (5)
idintuser_idintstatusvarchar(20)amountdecimal(10,2)created_atdatetime
Indexes (2)
- INDEX
(unnamed)(id) - INDEX
idx_user(user_id)
demodb.users
Columns (5)
idintnamevarchar(80)countryvarchar(2)statusvarchar(20)created_atdatetime
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:
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"
}
}
]
}