Join Algorithms, Subquery Optimizations, Derived Tables, Semijoins, and Materialization

Learning outcomes

Two SQL statements return the same set of customers: one uses EXISTS, the other joins to a distinct list of work-order customer IDs. A developer assumes the first must execute the subquery once per customer because it is written as a subquery. MariaDB’s optimizer can transform qualifying IN/EXISTS forms into semijoins and choose among several strategies. Likewise, a derived table can be merged into the outer query or materialized as a temporary result depending on semantics and estimated cost.

1. Nested-loop reasoning remains the baseline mental model

In a nested loop, MariaDB obtains rows from an outer input and finds matching rows in an inner input. If the inner side has a useful index, repeated key lookups can be efficient. If it does not, repeated scans can be catastrophic. Block-based algorithms buffer outer rows so they can process the inner side in batches; Batch Key Access (BKA) batches index lookups, while hashed variants can accelerate equality matching under applicable settings.

USE servicehub_optimizer_lab;SELECT @@join_cache_level,@@join_buffer_size,@@join_buffer_space_limit,@@optimizer_switch\GANALYZE FORMAT=JSONSELECT c.customer_id,c.region_code,w.work_order_id,w.statusFROM customers cJOIN work_orders w ON w.customer_id=c.customer_idWHERE c.region_code='TBZ' AND w.status='open';

Do not tune join_buffer_size by folklore. It is per-query/per-join working memory with concurrency implications, and index design often matters more. The plan and runtime row flow should tell you whether the inner lookup pattern is the problem before you allocate more join memory.

2. Semijoin means existence without duplicate multiplication

EXPLAIN FORMAT=JSONSELECT c.customer_id,c.tierFROM customers cWHERE EXISTS (  SELECT 1 FROM work_orders w  WHERE w.customer_id=c.customer_id    AND w.status='escalated');EXPLAIN FORMAT=JSONSELECT c.customer_id,c.tierFROM customers cWHERE c.customer_id IN (  SELECT w.customer_id FROM work_orders w  WHERE w.status='escalated');

A semijoin asks whether at least one inner match exists; it does not need to emit one outer row per matching inner row. MariaDB documents five semijoin strategies: table pullout, FirstMatch, Materialization, LooseScan and DuplicateWeedout. The optimizer chooses among applicable strategies based on query shape and cost. The strategy names are less important than their row-flow implication: avoid redundant work when only existence matters.

3. Materialization is a trade: build once, reuse many times

Materialization computes an intermediate result and stores it in a temporary structure. That adds startup work and memory/disk risk, but it can avoid repeatedly executing an expensive subquery or derived expression. Derived-table merge does the opposite: when semantics permit, MariaDB can fold a derived table into the outer query so predicates and indexes participate in one optimization problem.

EXPLAIN FORMAT=JSONSELECT d.customer_id,d.open_valueFROM (  SELECT customer_id,SUM(total_cents) AS open_value  FROM work_orders  WHERE status='open'  GROUP BY customer_id) AS dWHERE d.open_value > 100000;SET optimizer_trace='enabled=on';SELECT d.customer_id,d.open_valueFROM (  SELECT customer_id,SUM(total_cents) AS open_value  FROM work_orders  WHERE status='open'  GROUP BY customer_id) AS dWHERE d.open_value > 100000;SELECT TRACE FROM information_schema.OPTIMIZER_TRACE\GSET optimizer_trace='enabled=off';

Aggregation often creates a natural materialization boundary because the grouped result has different cardinality and semantics. MariaDB also supports condition pushdown into eligible derived tables and split-materialized optimization. Optimizer trace can show which transformations were considered and why. Do not assume a derived table is always a physical temporary table merely because SQL text contains parentheses.

4. Controlled optimizer_switch experiment

SET @saved_optimizer_switch=@@optimizer_switch;SET SESSION optimizer_switch='semijoin=off';EXPLAIN FORMAT=JSONSELECT c.customer_idFROM customers cWHERE c.customer_id IN (  SELECT w.customer_id FROM work_orders w WHERE w.status='escalated');SET SESSION optimizer_switch=@saved_optimizer_switch;SELECT @@optimizer_switch\G

This is a diagnostic experiment, not a recommended production setting. The goal is to compare plan shape and runtime when a family of transformations is unavailable. Restore the session value immediately. If disabling a transformation improves one query, ask why the default estimate preferred the other plan: statistics, correlation, indexes, parameter distribution and version behavior remain the durable causes to investigate.

5. Deliberately wrong: memorize “hash join beats nested loop”

Join algorithms are workload-dependent. An indexed nested loop can be excellent when the outer input is small and inner lookups are selective. A block/hash strategy can help when repeated scanning dominates, but it consumes join-buffer memory and has applicability constraints. MariaDB’s block algorithms are also controlled by join_cache_level and switches such as join_cache_bka, join_cache_hashed and join_cache_incremental. The default join_cache_level is 2, so merely seeing the switches ON does not mean every block algorithm is eligible.

Repair the reasoning by measuring row flow and candidate costs. Ask: how many outer rows, how many inner probes, is there a supporting index, how much duplication does existence semantics avoid, does materialization reduce repetition, and what temporary-memory/I/O work appears? Then verify with runtime evidence.

6. Join memory and algorithm choice are concurrency decisions

Block-based joins use join buffers, which means a query-level improvement can become a server-level memory problem when multiplied by concurrent sessions. join_buffer_size is therefore not analogous to a single shared cache. A workload with many concurrent joins can allocate substantial working memory, and larger buffers do not guarantee a better plan. MariaDB also exposes join_buffer_space_limit to bound total join-buffer space for a query.

Batch Key Access is useful to understand because it changes the order and batching of index lookups rather than changing relational semantics. It can reduce random access by grouping lookup work, but it relies on applicable indexes and Multi-Range Read behavior. A hash/block strategy can help an equijoin without a useful indexed lookup path, but its build/probe work and memory still depend on input cardinality. These are reasons to inspect runtime rows before changing join-cache policy.

7. Semijoin names are not tuning goals

FirstMatch, LooseScan, Materialization, DuplicateWeedout and table pullout describe execution strategies for qualifying subqueries. Do not write an operational objective such as “make every IN query use LooseScan.” The right strategy depends on duplicates, available indexes, outer/inner cardinality, grouping properties and cost. Instead, define the row-flow problem and let the optimizer choose from accurate inputs; use trace or a scoped switch only when you need to understand or test a specific alternative.

This distinction matters during upgrades. A newer MariaDB version may select a different semijoin strategy because the cost model or transformation logic changed. A different strategy name is not a regression by itself. Compare correctness, runtime rows, latency and resource use under the same workload.

8. Production judgment and bridge

1. Write the relational intent first: ordinary join, existence test, aggregation boundary or reusable derived result.
2. Use indexes/statistics so the optimizer has a good candidate set.
3. Inspect EXPLAIN/ANALYZE for actual strategy and row flow.
4. Use optimizer trace when you need to understand a rejected transformation.
5. Run switch/hint experiments at session/query scope, never as unexplained global policy.
6. Re-test after server upgrades because optimizer behavior is version-sensitive.

Lesson 5 closes the chapter by turning all these tools into a regression-control workflow and showing when hints or ignored indexes are appropriate safeguards rather than permanent substitutes for understanding.

Authoritative references

• MariaDB Documentation — Semi-join Subquery Optimizations
• MariaDB Documentation — Block-Based Join Algorithms
• MariaDB Documentation — optimizer_switch
• MariaDB Documentation — Optimizer Trace Overview