Semi-join materialization strategy
You are viewing an old version of this article. View
the current version here.
Semi-join Materialization is a special kind of subquery materialization that's used for Semi-join subqueries. It actually includes two strategies
- Materialization/lookup
- Materialization/scan
The idea
Let us again look at the query that finds countries that have big cities:
select * from Country where Country.code IN (select City.Country from City where City.Population > 7*1000*1000) and Country.continent='Europe'
The subquery is uncorrelated, that is, we can run it independently of the upper query. The idea of semi-join materialization is to do just that, and fill a temporary table with possible values of City.Country field of big cities, and then do a join with countries in Europe:
TODO: SJM-Scan, SJM-lookup, non-merged cases.
Example
MariaDB [world]> explain select * from Country where Country.code IN (select City.Country from City where City.Population > 7*1000*1000); +----+--------------+-------------+--------+--------------------+------------+---------+--------------------+------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------+-------------+--------+--------------------+------------+---------+--------------------+------+-----------------------+ | 1 | PRIMARY | <subquery2> | ALL | distinct_key | NULL | NULL | NULL | 15 | | | 1 | PRIMARY | Country | eq_ref | PRIMARY | PRIMARY | 3 | world.City.Country | 1 | | | 2 | MATERIALIZED | City | range | Population,Country | Population | 4 | NULL | 15 | Using index condition | +----+--------------+-------------+--------+--------------------+------------+---------+--------------------+------+-----------------------+ 3 rows in set (0.01 sec) MariaDB [world]> explain select * from Country where Country.code IN (select City.Country from City where City.Population > 7*1000*1000)\G *************************** 1. row *************************** id: 1 select_type: PRIMARY table: <subquery2> type: ALL possible_keys: distinct_key key: NULL key_len: NULL ref: NULL rows: 15 Extra: *************************** 2. row *************************** id: 1 select_type: PRIMARY table: Country type: eq_ref possible_keys: PRIMARY key: PRIMARY key_len: 3 ref: world.City.Country rows: 1 Extra: *************************** 3. row *************************** id: 2 select_type: MATERIALIZED table: City type: range possible_keys: Population,Country key: Population key_len: 4 ref: NULL rows: 15 Extra: Using index condition 3 rows in set (0.00 sec)
Comments
Comments loading...
Content reproduced on this site is the property of its respective owners,
and this content is not reviewed in advance by MariaDB. The views, information and opinions
expressed by this content do not necessarily represent those of MariaDB or any other party.