Live MySQL 8.4 — derived table + filesort

Query scans 1 table, materializes 1 temp table and sorts the result; examines ~3,000 rows to return 5 in 3.7 ms. Main finding: the sort will likely spill to disk because sort_buffer_size is too small.

Total time: 3.72 ms
Rows returned: 5
Rows examined: 3.00K
Operators: 6

Fix first

Primary recommendation

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

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)

Warnings (5)

WARN
Full table scan: t (5 rows)

Labeled: Table scan [t]
WARN
1 temp table(s) (Materialize) — up to 5 rows; may spill to disk

Labeled: Materialize
WARN
1 sort operation(s) — 5 rows; may use disk-based filesort

Labeled: Sort
WARN
Filesort detected but sort_buffer_size is only 256.0 KB — the sort will likely spill to disk.
WARN
Query materializes 1 temp table(s) but tmp_table_size/max_heap_table_size is capped at 16.0 MB — temp tables will spill to InnoDB disk tables.

Suggestions (5)

HIGH
Add indexes on filter/join columns to avoid full table scans
MEDIUM
Increase tmp_table_size / max_heap_table_size to keep temp tables in memory
MEDIUM
Increase sort_buffer_size or add an ordered index to avoid filesort
MEDIUM
Raise sort_buffer_size to 2M–8M for this session (SET SESSION sort_buffer_size = 8*1024*1024).

Why?

when the sort set does not fit in sort_buffer_size, MySQL writes multiple sorted runs to tmpdir and merges them back — that is real disk I/O plus a k-way merge. A buffer big enough to hold the result stays in memory and uses the faster in-RAM sort. Caveat: sort_buffer_size is allocated per-connection, so set it per-session rather than globally to avoid multiplying RAM cost across all threads.
MEDIUM
Raise tmp_table_size AND max_heap_table_size together to the same value (64M–256M).

Why?

once a materialized temp table exceeds min(tmp_table_size, max_heap_table_size), MySQL converts the in-memory MEMORY/TempTable engine to an on-disk InnoDB temp table — scans over the disk version are typically 10–100× slower and generate real I/O. Both variables must be raised together because MySQL always picks the smaller of the two; raising only one has no effect.

Collected environment

Session variables

version	`8.4.8`
innodb_buffer_pool_size	`134217728`
sort_buffer_size	`262144`
join_buffer_size	`262144`
tmp_table_size	`16777216`
max_heap_table_size	`16777216`
innodb_flush_log_at_trx_commit	`1`
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

Table stats (information_schema.tables)

Table	Rows	Data	Index
demodb.users	3,000	180,224	81,920

Schema (SHOW CREATE TABLE)

demodb.users — 5 columns · indexes: PRIMARY, idx_country

Glossary (7 terms)

derived table: A query-inside-a-query that MySQL treats like a temporary table. MySQL often rewrites it to be part of the outer query for speed, but sometimes it can't and has to materialize it.; ``SELECT … FROM (SELECT …) AS t``: the inner SELECT is a derived table. With ``derived_merge=on`` (default) the optimizer folds it into the outer query when possible; otherwise it's materialized. With ``derived_condition_pushdown=on``, outer predicates can be pushed inside the derived query block to reduce its row count.
filesort: MySQL has to sort the rows by hand because the data isn't already in the order you asked for. Small sorts stay in memory; big ones use disk and get slow.; MySQL sorts the result in the server layer using ``sort_buffer_size``. If the sort set fits, it stays in memory (fast quicksort). If not, MySQL writes sorted runs to tmpdir and k-way merges them — disk I/O plus CPU for the merge. An index in the ``ORDER BY`` order eliminates the filesort entirely.
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.
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.
sort buffer size: The memory MySQL gets per connection to sort rows. If your sort is bigger than this, MySQL falls back to using disk, which is slower.; Allocated lazily per connection, freed after the sort. When a filesort's data exceeds this buffer, MySQL writes sorted runs to tmpdir and k-way merges them back — disk I/O. Because it's per-connection, setting this high globally multiplies by max_connections; prefer ``SET SESSION sort_buffer_size = …`` for individual big queries.
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.
tmp table size: How big MySQL's scratch tables can get before they move from RAM to disk. If your query builds bigger scratch tables than this, expect a big slowdown — and you have to raise ``max_heap_table_size`` at the same time, otherwise it does nothing.; The effective limit is ``min(tmp_table_size, max_heap_table_size)``. Internal temp tables (from materialized subqueries, GROUP BY, DISTINCT, etc.) are MEMORY-engine until they exceed this, at which point MySQL converts them to on-disk InnoDB temp tables (10–100× slower for scans). Both variables must be raised together — raising only one has no effect.

Raw sidecar (JSON)

{
  "schema_version": "1.0",
  "generated_at": "2026-04-10T20:15:24Z",
  "myflames_version": "1.2.0",
  "source": {
    "type": "live",
    "engine": "mysql",
    "engine_version": "8.4.8"
  },
  "plan_summary": {
    "total_time_ms": 3.716,
    "rows_sent": 5,
    "rows_examined_estimate": 3000,
    "operator_count": 6,
    "max_depth": 6
  },
  "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. Watch tmp_table_size / max_heap_table_size — it may spill to disk.",
      "node_labels": [
        "Materialize"
      ]
    }
  ],
  "warnings": [
    {
      "severity": "warn",
      "category": "full_scan",
      "text": "Full table scan: t (5 rows)",
      "source": "plan",
      "node_labels": [
        "Table scan [t]"
      ]
    },
    {
      "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": "1 sort operation(s) — 5 rows; may use disk-based filesort",
      "source": "plan",
      "node_labels": [
        "Sort"
      ]
    },
    {
      "severity": "warn",
      "category": "env",
      "text": "Filesort detected but sort_buffer_size is only 256.0 KB — the sort will likely spill to disk.",
      "source": "environment"
    },
    {
      "severity": "warn",
      "category": "env",
      "text": "Query materializes 1 temp table(s) but tmp_table_size/max_heap_table_size is capped at 16.0 MB — temp tables will spill to InnoDB disk tables.",
      "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": "Increase tmp_table_size / max_heap_table_size to keep temp tables in memory",
      "source": "plan",
      "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": "Raise sort_buffer_size to 2M–8M for this session (SET SESSION sort_buffer_size = 8*1024*1024).",
      "source": "environment",
      "why": "when the sort set does not fit in sort_buffer_size, MySQL writes multiple sorted runs to tmpdir and merges them back — that is real disk I/O plus a k-way merge. A buffer big enough to hold the result stays in memory and uses the faster in-RAM sort. Caveat: sort_buffer_size is allocated per-connection, so set it per-session rather than globally to avoid multiplying RAM cost across all threads.",
      "target_variable": "sort_buffer_size"
    },
    {
      "severity": "medium",
      "category": "tuning_variable",
      "action": "Raise tmp_table_size AND max_heap_table_size together to the same value (64M–256M).",
      "source": "environment",
      "why": "once a materialized temp table exceeds min(tmp_table_size, max_heap_table_size), MySQL converts the in-memory MEMORY/TempTable engine to an on-disk InnoDB temp table — scans over the disk version are typically 10–100× slower and generate real I/O. Both variables must be raised together because MySQL always picks the smaller of the two; raising only one has no effect.",
      "target_variable": "tmp_table_size"
    }
  ],
  "executive_summary": "Query scans 1 table, materializes 1 temp table and sorts the result; examines ~3,000 rows to return 5 in 3.7 ms. Main finding: the sort will likely spill to disk because sort_buffer_size is too small.",
  "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"
  },
  "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",
      "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.8",
      "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"
      }
    },
    "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"
            ]
          },
          {
            "kind": "KEY",
            "name": "idx_country",
            "columns": [
              "country"
            ]
          }
        ],
        "engine": "InnoDB",
        "charset": "utf8mb4"
      }
    }
  }
}

Summary

SQL