Semi-join subquery optimizations

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

Note: This page describes features in the source repository for MariaDB 5.3

There are currently no official packages or binaries available for download which contain the features. If you want to try out any of the new features described here you will need to get and compile the code yourself.

Semi-join subquery optimizations are a comprehensive set of subquery optimizations and execution strategies that allow efficient processing of subqueries of 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.)