Semi-join subquery optimizations

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

Semi-join subquery optimizations are a comprehensive set of subquery optimizations and execution strategies that allow efficient processing of subqueries in this form:

SELECT ... WHERE expr IN (SELECT ... ) AND ...

that is, the subquery is an IN-subquery that is located in the WHERE in such a way that one can re-write the WHERE clause as "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.)