MySQL 8 Administrator???s Guide

Index extension

Index extension is the feature by which MySQL 8 extends a secondary index by appending the primary key. The InnoDB engine automatically extends a secondary index if it is required. To control the behavior of index extensions, MySQL 8 has defined a use_index_extensions flag in the optimizer_switch system variable. By default, this option is enabled, but the user is allowed to change it at runtime by using the following command:

SET optimizer_switch = 'use_index_extensions=off';

Let's look at one example to understand the index extension in detail. Let's create a table and insert the following values:

CREATE TABLE table1 ( c1 INT NOT NULL DEFAULT 0, c2 INT NOT NULL DEFAULT 0, d1 DATE DEFAULT NULL, PRIMARY KEY (c1, c2), INDEX ...