MySQL Invisible Index

Summary: in this tutorial, you will learn about MySQL invisible index and the statements to manage the index visibility.

Introduction to MySQL invisible index

The invisible indexes allow you to mark indexes as unavailable for the query optimizer. MySQL maintains the invisible indexes and keeps them up to date when the data in the columns associated with the indexes changes.

By default, indexes are visible. To make them invisible, you have to explicitly declare its visibility at the time of creation, or by using the ALTER TABLE command. MySQL provides us with the VISIBLE and INVISIBLE keywords to maintain index visibility.

To create an invisible index, you the following statement:

1 2	CREATE INDEX index_name ON table_name( c1, c2, ...) INVISIBLE;

In this syntax:

1. First, you specify the name of the index after the CREATE INDEX clause.
2. Second, you list the table name and the column list which you want to add to the index. The INVISIBLE keyword indicates that the index that you are creating is invisible.

For example, the following statement creates an index on the extension column of the employees table in the sample database and marks it as an invisible index:

1 2	CREATE INDEX extension ON employees(extension) INVISIBLE;

To change the visibility of existing indexes, you use the following statement:

1 2	ALTER TABLE table_name ALTER INDEX index_name [VISIBLE | INVISIBLE];

For example, to make the extension index visible, you use the following statement:

1 2	ALTER TABLE employees ALTER INDEX extension VISIBLE;

You can find the indexes and their visibility by querying the statistics table in the information_schema database:

1 2 3 4 5 6 7 8

SELECT     index_name,     is_visible FROM     information_schema.statistics WHERE     table_schema = 'classicmodels'         AND table_name = 'employees';

Here is the output:

In addition, you can use the SHOW INDEXES command to display all indexes of a table:

1	SHOW INDEXES FROM employees;

As mentioned earlier, the query optimizer does not use invisible index so why do you use the invisible index in the first place? Practically speaking, invisible indexes have a number of applications. For example, you can make an index invisible to see if it has an impact to the performance and mark the index visible again if it does.

MySQL invisible index and primary key

The index on the primary key column cannot be invisible. If you try to do so, MySQL will issue an error.

In addition, an implicit primary key index also cannot be invisible. When you defines a UNIQUE index on a NOT NULL column of a table that does not have a primary key, MySQL implicitly understands that this column is the primary key column and does not allow you to make the index invisible.

Consider the following example.

First, create a new table with a UNIQUE index on a NOT NULL column:

1 2 3 4 5 6 7 8

CREATE TABLE discounts (     discount_id INT NOT NULL,     name VARCHAR(50) NOT NULL,     valid_from DATE NOT NULL,     valid_to DATE NOT NULL,     amount DEC(5 , 2 ) NOT NULL DEFAULT 0,     UNIQUE discount_id(discount_id) );

Second, try to make the discount_id index invisible:

1 2	ALTER TABLE discounts ALTER INDEX discount_id INVISIBLE;

MySQL issued the following error message:

1	Error Code: 3522. A primary key index cannot be invisible

MySQL invisible index system variables

To control visible indexes used by the query optimizer, MySQL uses the use_invisible_indexes flag of the optimizer_switch system variable. By default, the use_invisible_indexes is off:

1	SELECT @@optimizer_switch;

In this tutorial, you have learned about the MySQL invisible index, how to create an invisible index, and how to change the visibility of an existing index.