Semi-join subquery optimizations
Semi-join subquery optimizations provide efficient handling of subqueries that that follow the form of:
SELECT ... WHERE expr IN (SELECT ... ) AND ...
that is, the subquery must be an IN-subquery and must be located in the WHERE clause in such a way that the WHERE clause can be rewritten into the form of subquery AND everything_else
. Our research shows it is the most common form of subquery.
Semi-join subquery optimizations are disabled by default. To enable all of them, you need to turn on their optimizer_switch flags like so:
SET optimizer_switch='semijoin=on,firstmatch=on,materialization=on,loosescan=on'
Subsequent sections describe the new subquery optimization strategies in greater detail.
Background: MySQL's subquery optimizer
TODO (Note by Timour: this has to be a separate section at a level above this one. It will make it natural to describe the non-semi-join optimizations.)
Semi-join subquery optimizations
TODO