- 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
- Learning Python Web Penetration Testing
- The Supervised Learning Workshop
- 案例式C語言程序設計
- Mastering Spring MVC 4
- Java Web開發技術教程
- Rust Essentials（Second Edition）
- Mastering ROS for Robotics Programming
- 軟件測試教程
- R用戶Python學習指南：數據科學方法
- 案例式C語言程序設計實驗指導
- 運維前線：一線運維專家的運維方法、技巧與實踐
- Learning JavaScript Data Structures and Algorithms（Second Edition）
- 并行編程方法與優化實踐
- Practical Microservices
- Java設計模式深入研究