DuplicateWeedout strategy

You are viewing an old version of this article. View the current version here.

DuplicateWeedout is an execution strategy for Semi-join subqueries.

The idea

The idea is to run the semi-join as if it was a regular inner join, and then eliminate the duplicate record combinations using a temporary table.

select * 
from Country 
where 
   Country.code IN (select City.Country
                    from City 
                    where 
                      City.Population > 0.33 * Country.Population and 
                      City.Population > 1*1000*1000);

DuplicateWeedout in action

TODO

Factsheet

  • DuplicateWeedout is shown as "Start temporary/End temporary" in EXPLAIN
  • The strategy can handle correlated subqueries, but cannot be applied if the subquery has grouping or aggregate functions.
  • DuplicateWeedout allows the optimizer to freely mix subquery's tables and parent select's tables.
  • There is no separate @@optimizer_switch flag for DuplicateWeedout. The strategy can be disabled by switching off all semi-join optimizations with SET @@optimizer_switch='optimizer_semijoin=off' command.

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.