Controlling MariaDB optimizer strategies

Starting with MariaDB 5.3 and continuing with all major releases since, various optimizations have been introduced that improve the core performance of MariaDB. To keep upgrades as compatible and as trouble-free as possible or because it is only useful in certain limited instances, many of these optimizations are turned off by default. This recipe is about enabling optimizations which are turned off by default.

In this recipe, we will enable the Multi-Range Read optimizations, but the basic concepts apply to control any of the optimizer_switch flags.

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. Show the current status of all optimizer_switch flags with the following command:

   SELECT @@optimizer_switch\G
   

3. The output of the previous command will be similar to the following screenshot. There may be some differences depending on our local server settings...