- MariaDB Cookbook
- Daniel Bartholomew
- 300字
- 2021-07-16 12:19:27
Using extended keys with InnoDB and XtraDB
When creating an execution plan for a query, the MariaDB optimizer makes a cost-based decision about whether or not to use one or more indexes instead of reading through a table row-by-row. Indexes are often, but not always the faster choice. The extended key's optimization improves the index lookups for InnoDB and XtraDB tables.
How to do it...
- Launch the
mysqlcommand-line client application and connect it to our MariaDB server as the root user or as a user with theSUPERprivilege. - Enable the extended keys optimization with the following command:
SET GLOBAL optimizer_switch='extended_keys=on'; - Add the following code to our
my.cnformy.inifile (or to an existing[mysqld]section):[mysqld]
optimizer_switch = 'extended_keys=on'verifies thatextended_keysis set toonwith the following command:SHOW VARIABLES LIKE 'optimizer_switch'\G
How it works...
In this recipe, we turn on the extended_keys optimization globally for the running server and we then make the change permanent by adding it to our my.cnf config file. In this way, we turn the feature on, and then ensure that it stays on, without having to restart MariaDB.
There's more...
Many InnoDB or XtraDB tables have more than one key, for example, a primary key on the id column and a secondary key on the username column. Using the extended_keys optimization, MariaDB is able to create execution plans for some queries, which only touch the indexes of those keys. It does this by looking at the keys, and if all of the information that we are looking for is present, MariaDB uses that information instead of looking through tables row by row.
See also
- The full documentation of the extended keys optimization is available at https://mariadb.com/kb/en/extended-keys/
- A blog post about the development of this feature is available at http://igors-notes.blogspot.com/2011/12/3-way-join-that-touches-only-indexes.html