Live MariaDB 11.4 — BNL join + ORDER BY

Query scans 2 tables, joins via block nested-loop and sorts the result; examines ~11,000 rows to return 5 in 1170 ms. Main finding: join predicate wraps the column in CONCAT() — no index can be used.

Total time: 1.17 s
Rows returned: 5
Rows examined: 11.00K
Operators: 4

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

Warnings (7)

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

Labeled: Table scan [o]
WARN
Full table scan: u (3000 rows), o (8000 rows)

Labeled: Table scan [u] Table scan [o]
WARN
1 sort operation(s) — 5 rows; may use disk-based filesort

Labeled: Sort
WARN
Block Nested-Loop (BNL) join buffer detected — uses join_buffer_size (Extra: 'Using join buffer (Block Nested Loop)', type ALL/index/range)

Labeled: Table scan [o]
WARN
Filesort detected but sort_buffer_size is only 256.0 KB — the sort will likely spill to disk.
WARN
Block Nested-Loop detected but join_buffer_size is only 256.0 KB. Build/batch phase may spill to tmpdir.
WARN
optimizer_switch has mrr=off; range scans followed by filesort may read rows in random order.

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 join 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 sort_buffer_size or add an ordered index to avoid filesort
MEDIUM
Add indexes to eliminate BNL full/index scans, or increase join_buffer_size. In MySQL 8.0.20+, set block_nested_loop=off to force hash join instead
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 join_buffer_size per-session to 2M–8M (SET SESSION join_buffer_size = 8*1024*1024).

Why?

BNL scans the inner table once per outer batch that fits in the join buffer. A tiny buffer means more batches, and each batch triggers another full scan of the inner side. 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.
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.

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
demodb.orders	7,993	425,984	180,224

Schema (SHOW CREATE TABLE)

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

Glossary (12 terms)

block nested loop: MySQL is reading chunks of the outer table into memory, then scanning the entire inner table once per chunk. This is slow on big tables — an index on the join column removes the problem entirely, or turning on hash_join lets MySQL pick a better plan.; BNL: signalled by ``Using join buffer (Block Nested Loop)`` in the Extra column. MySQL fills ``join_buffer_size`` with outer rows, then scans the inner table (type=ALL/index/range) once for the whole batch. Cost grows with the outer size: O(outer/batch × inner). On MySQL 8.0.20+, set ``block_nested_loop=off`` to force a hash join instead.
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.
hash join: MySQL builds a lookup table in memory for the smaller of the two tables, then reads the bigger one once and uses the lookup table to find matches. Much faster than the alternative (Block Nested-Loop) when neither side has a usable index.; MySQL 8.0.18+ (default in 8.0.20+). Reads the smaller input, builds an in-memory hash table keyed on the join column, then scans the larger input and probes the hash. Single pass over each side (O(m + n)). Uses ``join_buffer_size``; if the build side doesn't fit, MySQL spills to tmpdir and does a multi-pass (Grace) hash join, re-reading the probe side each pass.
index lookup: MySQL uses the index to jump straight to the rows you asked for. This is the fast path — what you want for most queries.; Access type ``ref`` or ``ref_or_null``: the optimizer uses a secondary index to fetch all rows matching an equality predicate on the indexed column. Cost is O(matching_rows). Ideal access path when the filter column is indexed.
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.
join buffer size: The memory MySQL uses when it has to join tables without a handy index. If this is too small for a big join, MySQL spills to disk and slows down.; Per-join allocation, freed after the join completes. Feeds the build side of a hash join or the outer-row batches of a BNL. If the build side of a hash join doesn't fit, MySQL spills to a multi-pass (Grace) hash join. An index on the join column is almost always a bigger win than raising this.
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.
sargable: A filter or join condition MySQL can answer using an index. Comparing a plain column to a value is sargable. Wrapping the column in a function (like CONCAT or DATE) makes it non-sargable, which means MySQL has to evaluate the function on every row — no index can help.; Short for *Search ARGument-able*: a WHERE/ON predicate is sargable if it compares the bare column to a value or another bare column (``a.id = b.user_id``, ``created_at > '2024-01-01'``). Wrapping the column in a function (``CONCAT(a.id)``, ``DATE(created_at)``, ``LOWER(email)``) breaks sargability because the optimizer would need a functional index on that exact expression. Non-sargable predicates force a per-row evaluation in the server layer and defeat every regular index.
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.

Raw sidecar (JSON)

{
  "schema_version": "1.0",
  "generated_at": "2026-04-10T20:15:29Z",
  "myflames_version": "1.2.0",
  "source": {
    "type": "live",
    "engine": "mariadb",
    "engine_version": "11.4.10-MariaDB-ubu2404"
  },
  "plan_summary": {
    "total_time_ms": 1169.935,
    "rows_sent": 5,
    "rows_examined_estimate": 11000,
    "operator_count": 4,
    "max_depth": 3
  },
  "optimizer_switches": [
    {
      "name": "block_nested_loop",
      "value": "on",
      "explanation": "Buffers a batch of outer rows in a join buffer and scans the inner table once per batch instead of once per row. Inner still does a full/index/range scan — a missing index on the join column is the root cause.",
      "node_labels": [
        "Table scan [o]"
      ]
    }
  ],
  "warnings": [
    {
      "severity": "error",
      "category": "nonsargable_join",
      "text": "Non-sargable join predicate: CONCAT(...) in concat('u',o.user_id) = concat('u',u.`id`) — a function wrapped around the join column prevents index use.",
      "source": "plan",
      "node_labels": [
        "Table scan [o]"
      ]
    },
    {
      "severity": "warn",
      "category": "full_scan",
      "text": "Full table scan: u (3000 rows), o (8000 rows)",
      "source": "plan",
      "node_labels": [
        "Table scan [u]",
        "Table scan [o]"
      ]
    },
    {
      "severity": "warn",
      "category": "filesort",
      "text": "1 sort operation(s) — 5 rows; may use disk-based filesort",
      "source": "plan",
      "node_labels": [
        "Sort"
      ]
    },
    {
      "severity": "warn",
      "category": "bnl",
      "text": "Block Nested-Loop (BNL) join buffer detected — uses join_buffer_size (Extra: 'Using join buffer (Block Nested Loop)', type ALL/index/range)",
      "source": "plan",
      "node_labels": [
        "Table scan [o]"
      ]
    },
    {
      "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": "Block Nested-Loop detected but join_buffer_size is only 256.0 KB. Build/batch phase may spill to tmpdir.",
      "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"
    }
  ],
  "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 sort_buffer_size or add an ordered index to avoid filesort",
      "source": "plan",
      "target_variable": "sort_buffer_size"
    },
    {
      "severity": "medium",
      "category": "tuning_variable",
      "action": "Add indexes to eliminate BNL full/index scans, or increase join_buffer_size. In MySQL 8.0.20+, set block_nested_loop=off to force hash join instead",
      "source": "plan",
      "target_variable": "join_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 join_buffer_size per-session to 2M–8M (SET SESSION join_buffer_size = 8*1024*1024).",
      "source": "environment",
      "why": "BNL scans the inner table once per outer batch that fits in the join buffer. A tiny buffer means more batches, and each batch triggers another full scan of the inner side. 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": "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."
    }
  ],
  "executive_summary": "Query scans 2 tables, joins via block nested-loop and sorts the result; examines ~11,000 rows to return 5 in 1170 ms. Main finding: join predicate wraps the column in CONCAT() — no index can be used.",
  "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": "u",
      "columns": [
        "status"
      ],
      "ddl": "CREATE INDEX idx_u_status ON u (status);",
      "reason": "Full scan on u with filter on (status)"
    },
    {
      "table": "o",
      "columns": [
        "user_id"
      ],
      "ddl": "CREATE INDEX idx_o_user_id ON o (user_id);",
      "reason": "Full scan on o with filter on (user_id)"
    }
  ],
  "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"
      },
      "demodb.orders": {
        "table_schema": "demodb",
        "table_name": "orders",
        "table_rows": 7993,
        "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(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"
      },
      "demodb.orders": {
        "ddl": "CREATE TABLE `orders` (\n  `id` int(11) NOT NULL AUTO_INCREMENT,\n  `user_id` int(11) 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_uca1400_ai_ci",
        "table_name": "orders",
        "columns": [
          {
            "name": "id",
            "type": "int(11)",
            "rest": "NOT NULL AUTO_INCREMENT"
          },
          {
            "name": "user_id",
            "type": "int(11)",
            "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"
            ]
          },
          {
            "kind": "KEY",
            "name": "idx_user",
            "columns": [
              "user_id"
            ]
          }
        ],
        "engine": "InnoDB",
        "charset": "utf8mb4"
      }
    }
  }
}

Summary

SQL