Semi-join in MySQL

1. Background information
What is Semi-join?
The so-called Semi-join refers to the semi-join subquery. When a table finds a matching record in another table, the semi-join (semi-jion) returns the records from the first table. In contrast to conditional joins, even if several matching records are found in the right node, the table on the left node returns only one record. Also, the table of the right node does not return a record. A semi-connection usually uses in or EXISTS as the connection condition. The subquery has the following structure:

SELECT  from WHERE inch (SELECT from and ...

that is, the subquery in the "in" of the Where condition.
This query is characterized by our concern for records that match Semi-join in outer_table.
In other words, the final result set is in Outer_tables, and Semi-join's role is to filter only the records in Outer_tables. This is also the basis for our semi-join optimization, which means that we only need to get the minimum amount of information from the Semi-join to be sufficient to filter the Outer_tables records.
takes the following statement as an example:

 select  *    country  where   Country.code  in   ( select   city.country  from city  where  City.populatio N>  1  *  1000  *  Span style= "color: #800000; Font-weight:bold; " >1000 ); 

Among the Semi-join: "

Select city.country                      from City                     where City.population>1*+*

"The result set that may be returned is as follows: China (Beijin), China (Shanghai), France (Paris) ...
We can see that there are 2 China, each to 2 city records Beijin and Shanghai, but in fact we only need 1 China is enough for outer_table

The country is filtered. So we need to go heavy.

2.Mysql supported Semi-join policies
There are 5 main semi-join strategies supported by MySQL, namely:
1. Duplicateweedout: Use temporary tables to semi-join the resulting set of results. Duplicate Weedout:run The Semi-join as if it was a join and remove Duplicate records using a temporary table.

The corresponding matching conditions are:


2. Firstmatch: Select only the 1th record that matches the appearance of the internal table. Firstmatch:when scanning the inner tables for row combinations and there is multiple instances of a given value group, C Hoose one rather than returning them all. This "shortcuts" scanning and eliminates production of unnecessary rows.

The corresponding matching conditions are:



3. Loosescan: The inner-table data is grouped based on the index, and the first data of each group is matched. Loosescan:scan A subquery table using an index This enables a single value to being chosen from each subquery ' s value gr Oup.

The corresponding matching conditions are:




4. Materializelookup: The inner-table is solidified into a temporary table, traversing the outer-table, and then looking for a match on the curing table. Materialize the subquery into a temporary table with an index and use the temporary table to perform a join. The index is used to remove duplicates. The index might also is used later for lookups when joining the temporary table with the outer tables; If not, the table is scanned.
Corresponding matching criteria:



5. Materializescan: The inner-table is solidified into a temporary table, traversing the curing table, and then looking for a match on the outer-table.

The corresponding conditions:
Each of these strategies except Duplicate weedout can be enabled or disabled using the optimizer_switchSystem variable. The semijoinFlag controls whether Semi-joins is used. If It is a set to on, the firstmatch, loosescan, and materializationFlags enable finer control over the permitted semi-join strategies. These flags are onBy default.

The use of Semi-join strategies are indicated in EXPLAIN output as follows:

• semi-joined tables show up in the outer select. EXPLAIN EXTENDEDplus SHOW WARNINGS shows the rewritten query, which displays the semi-join structure. From this can get a idea about which tables were pulled out of the semi-join. If a subquery was converted to a semi-join, you'll see that the subquery predicate are gone and its tables and WHERE Clau Se were merged into the outer query join list and WHERE clause.

• Temporary table use for Duplicate weedout are indicated by and in the Start temporary End temporary Extra column. Tables that were not pulled out and is in the range of EXPLAIN output rows covered by and would have Start temporary End temporary 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 is key part numbers.

• As of MySQL 5.6.7, temporary table use for materialization are indicated by rows with a select_type value of and MATERIALIZED rows with A table value of <subqueryN> .

  Before MySQL 5.6.7, temporary table use for materialization are indicated in the Extra column by Materialize if a single table is Used, or by Start materialize and End materialize if multiple tables is used. If Scan is present, no temporary table index is used for table reads. Otherwise, an index lookup is used.

Mysql> SELECT @ @optimizer_switch\g*************************** 1. Row***************************@ @optimizer_switch: Index_merge= on, index_merge_union= on, Index_merge_sort_union= on, Index_merge_intersection= on, Engine_condition_pushdown= on, Index_condition_pushdown= on, MRR= on, mrr_cost_based= on, Block_nested_loop= on, batched_key_access=off, materialization= on, Semijoin= on, Loosescan= on, Firstmatch= on, subquery_materialization_cost_based= on, Use_index_extensions= on

Reference: http://blog.itpub.net/22664653/viewspace-672772/http://blog.sina.com.cn/s/blog_4673e60301011qvx.htmlhttp:// Dev.mysql.com/doc/refman/5.6/en/subquery-optimization.html