•Materialize the subquery into an indexed temporary table that is used to perform a join, where the index is used to remove duplicates. The index might also be used later for lookups when joining the temporary table with the outer tables; if not, the table is scanned. For more information about materialization, see Section 8.2.2.2, “Optimizing Subqueries with Materialization” . 

Each of these strategies can be enabled or disabled using the following optimizer_switch  system variable flags: 

•The semijoin  flag controls whether semijoins are used. Starting with MySQL 8.0.17, this also applies to antijoins. 

•If semijoin  is enabled, the firstmatch , loosescan , duplicateweedout , and materialization flags enable finer control over the permitted semijoin strategies. 

•If the duplicateweedout  semijoin strategy is disabled, it is not used unless all other applicable strategies are also disabled. 

•If duplicateweedout  is disabled, on occasion the optimizer may generate a query plan that is far from optimal. This occurs due to heuristic pruning during greedy search, which can be avoided by setting optimizer_prune_level=0 . 

These flags are enabled by default. See Section 8.9.2, “Switchable Optimizations” . 

The optimizer minimizes differences in handling of views and derived tables. This affects queries that use the STRAIGHT_JOIN  modifier and a view with an IN  subquery that can be converted to a semijoin. The following query illustrates this because the change in processing causes a change in transformation, and thus a different execution strategy: 

CREATE VIEW v AS SELECT * FROM t1 WHERE a IN (SELECT b FROM t2); SELECT STRAIGHT_JOIN * FROM t3 JOIN v ON t3.x = v.a; 

The optimizer first looks at the view and converts the IN  subquery to a semijoin, then checks whether it is possible to merge the view into the outer query. Because the STRAIGHT_JOIN  modifier in the outer query prevents semijoin, the optimizer refuses the merge, causing derived table evaluation using a materialized table. 

EXPLAIN  output indicates the use of semijoin strategies as follows: 

•For extended EXPLAIN  output, the text displayed by a following SHOW WARNINGS  shows the rewritten query, which displays the semijoin structure. (See Section 8.8.3, “Extended EXPLAIN Output Format” .) From this you can get an idea about which tables were pulled out of the semijoin. If a subquery was converted to a semijoin, you should see that the subquery predicate is gone and its tables and WHERE clause were merged into the outer query join list and WHERE  clause. 

•Temporary table use for Duplicate Weedout is indicated by Start temporary  and End temporary in the Extra  column. Tables that were not pulled out and are in the range of EXPLAIN  output rows covered by Start temporary  and End temporary  have their rowid  in the temporary table. 

• FirstMatch( tbl_name )  in the Extra  column indicates join shortcutting. 

• LooseScan( m .. n )  in the Extra  column indicates use of the LooseScan strategy. m  and n  are key part numbers. 

