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 key1 (d1)
) ENGINE = InnoDB;
--Insert values into table
INSERT INTO table1 VALUES
(1, 1, '1990-01-01'), (1, 2, &apos...