Summary
Query scans 1 table, materializes 1 temp tableLearn → and sorts the result; examines ~3,000 rows to return 5 in 1.0 ms. Main finding: no index covers (status) on users.
- Total time
- 1.01 ms
- Rows returned
- 5
- Rows examined
- 3.00K
- Operators
- 4
Primary recommendation
Add indexes on filter/join columns to avoid full table scans
Candidate DDL (copy & run)
CREATE INDEX idx_users_status ON users (status);
CREATE INDEX idx_users_country ON users (country);
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 t.country, t.cnt
FROM (
SELECT country, COUNT(*) cnt
FROM users
WHERE status='active'
GROUP BY country) t
ORDER BY t.cnt DESC
Execution plan (flamegraph)
Click a plan operator to inspect details.
Warnings (5)
-
WARN
Full table scan: users (3000 rows)
Labeled:
Table scan [users] -
WARN
1 temp tableLearn →(s) (Materialize) — up to 5 rows; may spill to disk
Labeled:
Materialize -
WARN
2 sort operationLearn →(s) — 5 rows; may use disk-based filesortLearn →
Labeled:
Sort -
WARN
Query materializes 1 temp tableLearn →(s); tmp_table_size/max_heap_table_size is 16.0 MB. This limit alone does not show whether a table spilled to disk.
-
WARN
Table users has no index covering (status).
Suggestions (5)
-
HIGH
Add indexes on filter/join columns to avoid full table scans
-
MEDIUM
Check temporary-tableLearn → disk usage before raising tmp_table_size together with max_heap_table_size.
Why?
MariaDB keeps an internal temporary tableLearn → in memory only up to the smaller of the two, then converts it to an on-disk Aria or MyISAM table. A temporary tableLearn → in the plan does not by itself show that it spilled to disk.
-
MEDIUM
Increase sort_buffer_size or add an ordered index to avoid filesortLearn →
-
MEDIUM
Check temporary-tableLearn → disk usage before increasing tmp_table_size and max_heap_table_size to the same value for this session.
Why?
MEMORY internal tables and MariaDB use the smaller of these two limits; exceeding it can convert a table to on-disk storage. Account for concurrent sessions before increasing the limits.
-
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.5× (33.5% fewer tokens).
- Raw plan
- 794 tokens
- myflames digest
- 528 tokens
- Saved
- 266 tokens
- Per 1,000 queries
- ~$0.80 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 | 11.4.13-MariaDB-ubu2404 |
|---|---|
| innodb_buffer_pool_size | 128.0 MB |
| sort_buffer_size | 2.0 MB |
| 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 | 38 flags (click to expand)index_merge=onindex_merge_union=onindex_merge_sort_union=onindex_merge_intersection=onindex_merge_sort_intersection=offindex_condition_pushdown=onderived_merge=onderived_with_keys=onfirstmatch=onloosescan=onmaterialization=onin_to_exists=onsemijoin=onpartial_match_rowid_merge=onpartial_match_table_scan=onsubquery_cache=onmrr=offmrr_cost_based=offmrr_sort_keys=offouter_join_with_cache=onsemijoin_with_cache=onjoin_cache_incremental=onjoin_cache_hashed=onjoin_cache_bka=onoptimize_join_buffer_size=ontable_elimination=onextended_keys=onexists_to_in=onorderby_uses_equalities=oncondition_pushdown_for_derived=onsplit_materialized=oncondition_pushdown_for_subquery=onrowid_filter=oncondition_pushdown_from_having=onnot_null_range_scan=offhash_join_cardinality=oncset_narrowing=offsargable_casefold=on |
Tables (click a name to expand)
demodb.users
Columns (5)
idint(11)namevarchar(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:
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:09Z",
"myflames_version": "2.3.0",
"source": {
"type": "live",
"engine": "mariadb",
"engine_version": "11.4.13-MariaDB-ubu2404"
},
"plan_summary": {
"total_time_ms": 1.011,
"rows_sent": 5,
"rows_examined_estimate": 3000,
"operator_count": 4,
"max_depth": 4
},
"optimizer_switches": [
{
"name": "materialization",
"value": "on",
"explanation": "A subquery or derived table is executed once and its result stored in a temporary table for reuse. It may spill to disk past tmp_table_size (MySQL's TempTable engine) or the smaller of tmp_table_size and max_heap_table_size (MEMORY engine, MariaDB).",
"node_labels": [
"Materialize"
]
}
],
"warnings": [
{
"severity": "warn",
"category": "full_scan",
"text": "Full table scan: users (3000 rows)",
"source": "plan",
"node_labels": [
"Table scan [users]"
]
},
{
"severity": "warn",
"category": "temp_table",
"text": "1 temp table(s) (Materialize) — up to 5 rows; may spill to disk",
"source": "plan",
"node_labels": [
"Materialize"
]
},
{
"severity": "warn",
"category": "filesort",
"text": "2 sort operation(s) — 5 rows; may use disk-based filesort",
"source": "plan",
"node_labels": [
"Sort"
]
},
{
"severity": "warn",
"category": "env",
"text": "Query materializes 1 temp table(s); tmp_table_size/max_heap_table_size is 16.0 MB. This limit alone does not show whether a table spilled to disk.",
"source": "environment"
},
{
"severity": "warn",
"category": "env",
"text": "Table users has no index covering (status).",
"source": "environment"
}
],
"suggestions": [
{
"severity": "high",
"category": "index",
"action": "Add indexes on filter/join columns to avoid full table scans",
"source": "plan"
},
{
"severity": "medium",
"category": "tuning_variable",
"action": "Check temporary-table disk usage before raising tmp_table_size together with max_heap_table_size.",
"source": "plan",
"why": "MariaDB keeps an internal temporary table in memory only up to the smaller of the two, then converts it to an on-disk Aria or MyISAM table. A temporary table in the plan does not by itself show that it spilled to disk.",
"target_variable": "tmp_table_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 temporary-table disk usage before increasing tmp_table_size and max_heap_table_size to the same value for this session.",
"source": "environment",
"why": "MEMORY internal tables and MariaDB use the smaller of these two limits; exceeding it can convert a table to on-disk storage. Account for concurrent sessions before increasing the limits.",
"target_variable": "tmp_table_size"
},
{
"severity": "high",
"category": "index",
"action": "CREATE INDEX idx_users_status ON users (status);",
"source": "environment"
}
],
"executive_summary": "Query scans 1 table, materializes 1 temp table and sorts the result; examines ~3,000 rows to return 5 in 1.0 ms. Main finding: no index covers (status) on users.",
"plan_tree": {
"node_id": "n:c1019a6ed56c",
"short_label": "Sort",
"folded_label": "SORT starts=1 rows=5",
"children": [
{
"node_id": "n:8b9e7b0b1786",
"short_label": "Materialize",
"folded_label": "MATERIALIZE starts=1 rows=5",
"children": [
{
"node_id": "n:e273397ca36c",
"short_label": "Sort",
"folded_label": "SORT starts=1 rows=5",
"children": [
{
"node_id": "n:8cdc6b061297",
"short_label": "Table scan [users]",
"folded_label": "TABLE SCAN [users] starts=1 rows=3000",
"children": []
}
]
}
]
}
]
},
"query": {
"raw": "SELECT t.country, t.cnt FROM (SELECT country, COUNT(*) cnt FROM users WHERE status='active' GROUP BY country) t ORDER BY t.cnt DESC",
"beautified": "SELECT t.country, t.cnt\nFROM (\nSELECT country, COUNT(*) cnt\nFROM users\nWHERE status='active'\nGROUP BY country) t\nORDER BY t.cnt 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)"
},
{
"table": "users",
"columns": [
"country"
],
"ddl": "CREATE INDEX idx_users_country ON users (country);",
"reason": "ORDER BY on users triggers a filesort; an index on the ORDER BY columns (country) lets the engine read rows already sorted, skipping the O(n log n) filesort."
}
],
"primary_action": {
"ref": "suggestions[0]"
},
"collected": {
"variables": {
"bulk_insert_buffer_size": "8388608",
"character_set_connection": "utf8mb4",
"character_set_server": "utf8mb4",
"collation_connection": "utf8mb4_uca1400_ai_ci",
"collation_server": "utf8mb4_uca1400_ai_ci",
"eq_range_index_dive_limit": "200",
"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": "200",
"innodb_log_buffer_size": "16777216",
"innodb_log_file_size": "100663296",
"join_buffer_size": "262144",
"max_allowed_packet": "16777216",
"max_connections": "151",
"max_heap_table_size": "16777216",
"optimizer_prune_level": "2",
"optimizer_search_depth": "62",
"optimizer_switch": "index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on,condition_pushdown_from_having=on,not_null_range_scan=off,hash_join_cardinality=on,cset_narrowing=off,sargable_casefold=on",
"read_buffer_size": "131072",
"read_rnd_buffer_size": "262144",
"sort_buffer_size": "2097152",
"table_open_cache": "2000",
"thread_cache_size": "151",
"tmp_table_size": "16777216",
"version": "11.4.13-MariaDB-ubu2404",
"version_comment": "mariadb.org binary distribution",
"version_compile_os": "debian-linux-gnu"
},
"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"
}
},
"schema": {
"demodb.users": {
"ddl": "CREATE TABLE `users` (\n `id` int(11) 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_uca1400_ai_ci",
"table_name": "users",
"columns": [
{
"name": "id",
"type": "int(11)",
"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"
}
}
},
"teach_hooks": [
{
"lesson": "filesort",
"match": {
"folded_label": "SORT starts=1 rows=5",
"short_label": "Sort"
},
"controls": {
"rows": 5,
"row_size": 200,
"sbs": 2097152
},
"note": "Sort: t.cnt desc",
"query_sql": "SELECT t.country, t.cnt FROM (SELECT country, COUNT(*) cnt FROM users WHERE status='active' GROUP BY country) t ORDER BY t.cnt DESC"
},
{
"lesson": "tmp",
"match": {
"folded_label": "MATERIALIZE starts=1 rows=5",
"short_label": "Materialize"
},
"controls": {
"rows": 5,
"row_size": 200,
"tmp_size": 16777216,
"max_heap": 16777216,
"engine": "mariadb"
},
"note": "Materialize (<derived2>)",
"query_sql": "SELECT t.country, t.cnt FROM (SELECT country, COUNT(*) cnt FROM users WHERE status='active' GROUP BY country) t ORDER BY t.cnt 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": 10.0
},
"note": "Table scan on users (users)",
"query_sql": "SELECT t.country, t.cnt FROM (SELECT country, COUNT(*) cnt FROM users WHERE status='active' GROUP BY country) t ORDER BY t.cnt DESC"
}
],
"operator_complexities": [
{
"node_id": "n:c1019a6ed56c",
"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:8b9e7b0b1786",
"folded_label": "MATERIALIZE starts=1 rows=5",
"short_label": "Materialize",
"complexity": {
"big_o": "build: O(n log n) • scan: O(1) per probe, O(rows) to fetch all",
"short": "build+scan",
"severity": "medium",
"rationale": "Materialize is a two-phase operator. The build pays the inner subquery's cost ONCE; each scan of the temp table is effectively constant time. Don't compound build cost by loops.",
"confidence": "worst_case",
"learn_more": "materialization",
"build_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"
},
"scan_complexity": {
"big_o": "O(1) per probe, O(rows) to fetch all",
"short": "O(1)/probe",
"severity": "good",
"rationale": "Materialize scan: reads the pre-computed temp table. Each probe is constant time once the table is in memory.",
"confidence": "typical",
"learn_more": "materialization"
}
}
},
{
"node_id": "n:e273397ca36c",
"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:8cdc6b061297",
"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"
}
}
]
}