How to do it...

How it works...

The optimizer_switch variable is basically a list of flags, which shows the status of the various available optimization strategies. When we use the SET command, we can turn various individual flags off and on. Any flags that we do not name in the SET command remain as they are.

There's more...

By default, the SET command only sets the variables for our current session. If we quit the client or we are disconnected for some reason and then we reconnect, the flags will be set to what they were before the changes were made.

To make our changes until MariaDB is shut down or restarted, add GLOBAL to the command as follows:

SET GLOBAL optimizer_switch="mrr=on";

If we want to make the change permanent, so that an optimization is either on or off permanently, we need to add it to our my.cnf or my.ini file. For example, to turn on all the mrr optimizations, add the following lines of code to the end of the file (or to an existing [mysqld] section):

[mysqld]
optimizer_switch = "mrr=on, mrr_cost_based=on,mrr_sort_keys=on"

Restart MariaDB to activate the changes.

See also