Re: Can we expect an enhancement for derived table merge optimization?
Posted by:
Rick James
Date: January 07, 2013 10:50PM
It sounds I don't know any more than you do. Here is my rundown.
Case 1:
FROM ( SELECT ... )
WHERE ...
* Since 4.1, the subquery would create a temp table.
* Since there won't be any INDEXes on the temp table, the WHERE clause filtering would be done on a "table scan" of the temp table.
* If the subquery had something that shrank the number of rows (GROUP BY, DISTINCT, LIMIT, etc), there sometimes could be a benefit from doing the query this way.
Case 2:
FROM ( SELECT ... )
JOIN tbl ON ...
* Since 4.1, the subquery would be evaluated and scanned first, and with no indexes. Then the JOIN to some other table would be done via "Nexted Loop Join".
* If the subselect had something that shrank the number of rows (GROUP BY, DISTINCT, LIMIT, etc), there sometimes could be a benefit from doing the query this way.
Case 3:
FROM ( SELECT ... )
JOIN ( SELECT ... ) ON ...
* If both derived tables have multiple rows, this turns into a cross-join, with filtering (ON, WHERE) later. Grossly inefficient.
* With 5.6 (specifically 5.6.7?) (soon to be GA), the optimizer will "materialize" the tables and will try all possible indexes and create the optimal one(s). Grossly efficient! Far better than simply materializing.
Case 4:
FROM tbl
LEFT JOIN ( SELECT ... ) x ON x.foo = tbl.foo
WHERE x.foo IS [NOT] NULL
* Similar comments to Case 3.
Case 5:
WHERE foo IN ( SELECT ... )
* Since 4.1, this has been one of the worst-performing constructs available. It reevaluates the subquery repeatedly.
* With 5.6 (soon to be GA) and MariaDB 5.5 (GA for some time) the subquery is evaluated only once ("materialized"). This greatly improves the construct.
Case 6:
(I can't think of a specific example)
* A "Semijoin" (I think) is where one side can have duplicates, but it doesn't care. Examples of similar situations: IN, EXISTS. 5.6 has the 'optimization' optimization; MariaDB 5.5 may have it.
Case 7:
VIEWs
* Often a VIEW performs worse than the equivalent SELECT. It is as if they only half-optimized VIEWs.
* I don't know if there are yet any optimizations in the new releases.
(Caveat: I may have mangled some of the details.)