Live MariaDB 11.4 — derived table + filesort

Query scans 1 table, materializes 1 temp table and sorts the result; examines ~3,000 rows to return 5 in 0.57 ms. Main finding: no index covers (status) on users.

Total time: 568 µs
Rows returned: 5
Rows examined: 3.00K
Operators: 4

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

WARN
Full table scan: users (3000 rows)

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

Labeled: Materialize
WARN
2 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.
WARN
optimizer_switch has mrr=off; range scans followed by filesort may read rows in random order.
WARN
Table users has no index covering (status).

Suggestions (7)

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.
MEDIUM
Consider SET SESSION optimizer_switch='mrr=on,mrr_cost_based=on';.

Why?

Multi-Range Read collects row IDs from a secondary-index range scan, sorts them by primary key, and then fetches rows in (mostly) physical order. Without MRR, InnoDB has to dereference each secondary-index row in secondary-index order, which translates to random I/O on the clustered index and hurts both disk and buffer-pool cache locality.

HIGH

CREATE INDEX idx_users_status ON users (status);

Collected environment

Session variables

version	`11.4.10-MariaDB-ubu2404`
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,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

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 (11 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.
index range scan: MySQL is reading a chunk of the index that matches your range filter (like a date range) instead of the whole thing. The narrower the range, the faster.; Access type ``range``: reads a bounded key range of a secondary index (e.g. ``WHERE created_at BETWEEN a AND b``). Fast when the range is narrow; degrades to a full scan when the range covers most of the index. With ``mrr=on`` the row-ID fetches can be reordered to turn random I/O into sequential.
innodb buffer pool size: The amount of RAM InnoDB uses to cache your data. Bigger = more of your data stays in memory = faster repeat queries. If this is smaller than your working set, every query hits the disk.; The central caching tunable for InnoDB. Pages are loaded into the buffer pool on demand and evicted by LRU. Every page miss is a physical disk read. Rule of thumb: size it to ~70–80% of system RAM on a dedicated database server, or at least to the working set of your hottest tables. Requires restart.
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.
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.
optimizer switch: A list of on/off switches for specific optimizer tricks. Most are on by default; turning the wrong ones off can hurt performance a lot.; Server/session variable that enables or disables individual optimizer transformations (hash_join, index_merge, skip_scan, semijoin strategies, etc.). Session-tunable: ``SET SESSION optimizer_switch='hash_join=on'``. The default differs between MySQL and MariaDB and between minor versions.
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:41Z",
  "myflames_version": "1.2.0",
  "source": {
    "type": "live",
    "engine": "mariadb",
    "engine_version": "11.4.10-MariaDB-ubu2404"
  },
  "plan_summary": {
    "total_time_ms": 0.568,
    "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. 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: 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": "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"
    },
    {
      "severity": "warn",
      "category": "env",
      "text": "optimizer_switch has mrr=off; range scans followed by filesort may read rows in random order.",
      "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": "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"
    },
    {
      "severity": "medium",
      "category": "optimizer_switch",
      "action": "Consider SET SESSION optimizer_switch='mrr=on,mrr_cost_based=on';.",
      "source": "environment",
      "why": "Multi-Range Read collects row IDs from a secondary-index range scan, sorts them by primary key, and then fetches rows in (mostly) physical order. Without MRR, InnoDB has to dereference each secondary-index row in secondary-index order, which translates to random I/O on the clustered index and hurts both disk and buffer-pool cache locality."
    },
    {
      "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 0.57 ms. Main finding: no index covers (status) on users.",
  "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)"
    }
  ],
  "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_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": "262144",
      "table_open_cache": "2000",
      "thread_cache_size": "151",
      "tmp_table_size": "16777216",
      "version": "11.4.10-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"
            ]
          },
          {
            "kind": "KEY",
            "name": "idx_country",
            "columns": [
              "country"
            ]
          }
        ],
        "engine": "InnoDB",
        "charset": "utf8mb4"
      }
    }
  }
}

Summary

SQL