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 key1 (d1)
) ENGINE = InnoDB;

--Insert values into table
INSERT INTO table1 VALUES
(1, 1, '1990-01-01'), (1, 2, '1991-01-01'),
(1, 3, '1992-01-01'), (1, 4, '1993-01-01'),
(1, 5, '1994-01-01'), (2, 1, '1990-01-01'),
(2, 2, '1991-01-01'), (2, 3, '1992-01...