Optimizing semijoin subqueries

MariaDB includes several optimizations specifically targeted at semijoin subqueries. Semijoin subqueries are primarily the ones in which the subquery is an IN subquery located in the WHERE clause of our SQL statement. An example will be something like the following from the popular DBT3 benchmarking dataset:

SELECT * FROM part
WHERE p_partkey IN
    (SELECT l_partkey FROM lineitem
    WHERE l_shipdate between '1997-01-01' and '1997-02-01')
ORDER BY p_retailprice DESC LIMIT 10;

How to do it...

1. Launch the mysql command-line client application and connect to our MariaDB server as the root user or as a user with the SUPER privilege.

2. Run the following command to enable the exists_to_in optimization:

   SET GLOBAL optimizer_switch='exists_to_in=on';
   

3. Make the change permanent by adding the following lines of code to the end of our my.cnf or my.ini file (or by adding it to an existing [mysqld] section):

   [mysqld]
   optimizer_switch = 'exists_to_in=on';

How it works...

MariaDB has...