MySQL 8 Administrator???s Guide

Invisible index

As mention previously, an invisible index is not used by the optimizer. Then what is the use of this index? This question comes into our mind, right? We will explain to you some of the use cases for invisible indexes:

• When many indexes are defined, but you are not sure which index is not in use. In this case, you can make an index invisible and check the performance impact. If it has an impact, then you can make that index visible on an immediate basis.
• Special cases where only one query is using an index. In this case, an invisible index is a good solution.

In the following example, we will create an invisible index using CREATE TABLE, CREATE INDEX, or ALTER TABLE commands:

CREATE TABLE `employee` ( `id` int(11) NOT NULL ...