- 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
- Java高并發核心編程（卷2）：多線程、鎖、JMM、JUC、高并發設計模式
- Manga Studio Ex 5 Cookbook
- Python自動化運維快速入門（第2版）
- OpenCV for Secret Agents
- Java程序設計與實踐教程（第2版）
- 軟件項目管理實用教程
- Linux操作系統基礎案例教程
- Responsive Web Design by Example
- PHP+Ajax+jQuery網站開發項目式教程
- jQuery for Designers Beginner's Guide Second Edition
- Java 從入門到項目實踐（超值版）
- JavaScript前端開發基礎教程
- Android應用開發攻略
- C語言從入門到精通（視頻實戰版）
- PHP典型模塊與項目實戰大全