MariaDB: Merging Views and Unnesting Subqueries

The idea: fold the inner query in, or run it first into a temp table

A subquery in FROM (a derived table, which the book calls an inline view), a view, or a subquery in WHERE is a separate query block. Before MariaDB runs a query it tries to rewrite these blocks. It can merge the inner block into the outer one, so that one join over all the base tables is planned, and rows are pipelined from table to table. Or it can run the inner block first and materialize its rows in a temporary table, which the outer block then reads. This is the book's choice between pipelined and materialized evaluation (Elmasri & Navathe, section 19.2).

The page uses one small data set: the book's EMP, DEPT and BLDG tables from section 19.2.3, filled with the eight COMPANY employees of Figure 5.6 (a Salary column is added so that the book's query Q(SJ) runs on the same tables, and a department 7 Marketing with no employees). Pick a query and a plan, press Run Query, and watch the rows: an orange row is being read, a blue box is a row passed on to the next table, a purple row is stored in the work area (a temp table, a join buffer or a cache), and green rows are the result. Every EXPLAIN row on the canvas is real ANALYZE output of a MariaDB server for these tables, with the optimizer_switch shown above it.

Merging a derived table or view

The book's query joins EMP with an inline view V that joins DEPT and BLDG. With derived_merge=on (the default) MariaDB merges V: SHOW WARNINGS after EXPLAIN EXTENDED prints one block, from EMP E join DEPT D join BLDG B, and EXPLAIN shows three SIMPLE rows. Now the optimizer can start from E with the index on Fn and reach DEPT and BLDG by their primary keys (eq_ref). The one John row travels through the three tables and out: 3 rows read, nothing stored (Demo 1: inline view merged vs materialized).

A view is merged the same way. CREATE ALGORITHM=MERGE VIEW asks for it, ALGORITHM=TEMPTABLE forbids it, and UNDEFINED (the default) lets MariaDB merge whenever it can. It cannot merge a block with GROUP BY, aggregates, DISTINCT, UNION, LIMIT or HAVING, the same list the book gives for when merging is "invalid".

When MariaDB materializes: DERIVED, key0 and condition pushdown

With derived_merge=off, or a TEMPTABLE view, the inner block runs first. EXPLAIN shows it as select_type = DERIVED and the outer block reads a table named <derived2> (2 is the inner block's id). The book points out that such a temp table has no index, so an index-based join on V.Dno is not possible. MariaDB differs here: derived_with_keys=on builds an index key0 on the temp table, so <derived2> is read by ref. Only with derived_with_keys=off as well do you get the book's case, a scan of the whole temp table with a block nested-loop join (Demo 2: temp table with no index).

A GROUP BY view is always materialized: MariaDB does not do the book's GROUP-BY view merging. It gets much of the same benefit another way. condition_pushdown_for_derived=on copies a condition of the outer query that only uses the view's grouping columns into the view's own WHERE. In the page's query, V.Dno = 5 is pushed into DEPT_PAY, so the view groups 4 EMP rows into one group instead of 8 rows into three groups of which two are thrown away (Demo 3: GROUP BY view and condition pushdown). ANALYZE shows it as r_rows 4 instead of 8 inside the view.

switch / clause	default	effect
derived_merge	on	merge mergeable derived tables and views into the outer block
ALGORITHM=MERGE | TEMPTABLE | UNDEFINED	UNDEFINED	per view: force merging, force materializing, or let MariaDB pick
derived_with_keys	on	add an index (key0) on a materialized derived table
condition_pushdown_for_derived	on	push outer conditions into a materialized derived table or view

IN subqueries become semi-joins

The book's Q(SJ) counts departments that have an employee earning more than a threshold (200 000 in the book, 35 000 here, since Figure 5.6 stops at 55 000). Run once per DEPT row, the subquery is slow. The book unnests it into a semi-join, and so does MariaDB: the optimizer trace says converted_to_semi_join, and SHOW WARNINGS prints from DEPT D semi join (EMP E). Both EXPLAIN rows then have id = 1, one block.

Why not a plain inner join? Research has two employees above 35 000 (Wong and Narayan). FROM DEPT D, EMP E would return Research twice and count 4. A semi-join returns each DEPT row at most once, so it can never return more rows than DEPT has: 3.

A semi-join is not a join algorithm by itself, so MariaDB picks a strategy by cost. The page shows three:

strategy	how	EXPLAIN shows	demo
FirstMatch	for each outer row, stop at the first inner match (the book's definition)	FirstMatch(D) in Extra	Demo 4
SJ-Materialization	run the subquery once into a temp table with a unique key, then look each outer row up in it	<subquery2> eq_ref distinct_key, select_type MATERIALIZED	Demo 5
DuplicateWeedout	run an ordinary inner join, then drop repeats with a temp table of outer rowids	Start temporary … End temporary	Demo 6

On these tiny tables FirstMatch wins; the other two were made visible by switching strategies off (firstmatch=off, …), as shown above the EXPLAIN. In Demo 4 the DEPT rows wait in a join buffer and EMP is read once; each buffered row is marked at its first match, and a second match for Research is skipped. A DEPT row is read in the order of index Bldg_id (1, 5, 4, 7), because that small index also holds Dno and covers the query. The book's other rewrite, an EXISTS subquery whose inner side is unique, is also turned into a semi-join (exists_to_in) and can end as a plain join (table pullout).

NOT IN and NOT EXISTS: no anti-join

The book turns NOT IN into an anti-join. MariaDB does not: NOT IN and NOT EXISTS stay subqueries, and MariaDB chooses by cost between two ways to run them (Demo 7: NOT IN, run per row vs materialize once):

• IN-to-EXISTS (in_to_exists): add D.Dno = E.Dno to the subquery and run it again for every outer row, stopping at the first match. EXPLAIN says DEPENDENT SUBQUERY. Here EMP is read 12 times for 4 departments; ANALYZE reports r_rows = 3.00, the average per run. Answers are kept in a subquery cache (<expr_cache>, subquery_cache=on), which helps only when an outer value repeats.
• Materialization (materialization): run the subquery once into a temp table with a unique key and probe it for each outer row. EXPLAIN says MATERIALIZED. This is the book's "temporary result table" technique; 8 rows read instead of 16.

The anti-join itself exists in MariaDB only when you write it as an outer join: DEPT D LEFT JOIN EMP E ON E.Dno = D.Dno AND E.Salary > 35000 WHERE E.Ssn IS NULL shows Not exists in Extra, meaning "stop at the first match and drop the row". All three forms count 1 (Marketing).

Reading EXPLAIN for all of this

you see	it means
all rows SIMPLE, one id	views and derived tables were merged; IN subqueries became semi-joins
DERIVED + table <derivedN>	a derived table or view was materialized; key0 is the index MariaDB added
MATERIALIZED + table <subqueryN>	a subquery result was stored once with a unique distinct_key
DEPENDENT SUBQUERY	the subquery runs again for each outer row
FirstMatch(t), Start/End temporary, LooseScan	which semi-join strategy was chosen

How MariaDB then runs the joins it is left with is on MySQL JOIN, GROUP BY and ORDER BY and Nested Loop vs Hash Join vs Merge Join; how a cost-based optimizer prices plans is on How a Query Planner Picks a Plan.

What the page leaves out

Materialized views (MariaDB has none) and GROUP-BY view merging (MariaDB never merges a GROUP BY view). The other two semi-join strategies, LooseScan and table pullout, and split materialization (LATERAL DERIVED, which pushes a join condition into a GROUP BY view per outer row). Pushdown into HAVING and into IN subqueries, subquery cache hits, the cost numbers behind each choice, and how the plans change on large tables: on a 10 000-row EMPLOYEE table the same IN query picks SJ-Materialization by default.

References

Derived Table Merge Optimization (MariaDB Documentation)

Condition Pushdown into Derived Table Optimization (MariaDB Documentation)

Semi-join Subquery Optimizations (MariaDB Documentation)

Non-semi-join Subquery Optimizations (MariaDB Documentation)

Elmasri & Navathe, Fundamentals of Database Systems, 7th ed., section 19.2 Choice of Query Execution Plans.