Using extended keys with InnoDB and XtraDB_MariaDB Cookbook-QQ阅读女频现言网

上QQ阅读APP看书，第一时间看更新

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 mysql command-line client application and connect it to our MariaDB server as the root user or as a user with the SUPER privilege.
Enable the extended keys optimization with the following command:
```
SET GLOBAL optimizer_switch='extended_keys=on';
```
Add the following code to our my.cnf or my.ini file (or to an existing [mysqld] section):
```
[mysqld]
```
optimizer_switch = 'extended_keys=on' verifies that extended_keys is set to on with 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.