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;Launch the
mysqlcommand-line client application and connect to our MariaDB server as the root user or as a user with theSUPERprivilege.Run the following command to enable the
exists_to_inoptimization:SET GLOBAL optimizer_switch='exists_to_in=on';Make the change permanent by adding the following lines of code to the end of our
my.cnformy.inifile (or by adding it to an existing[mysqld]section):[mysqld] optimizer_switch = 'exists_to_in=on';