Semi-join subquery optimizations

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

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.

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.)