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:

CREATE INDEX index_name
ON table_name( c1, c2, ...) INVISIBLE;Code language: SQL (Structured Query Language) (sql)

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:

CREATE INDEX extension 
ON employees(extension) INVISIBLE;
Code language: SQL (Structured Query Language) (sql)

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

ALTER TABLE table_name
ALTER INDEX index_name [VISIBLE | INVISIBLE];Code language: SQL (Structured Query Language) (sql)

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

ALTER TABLE employees
ALTER INDEX extension VISIBLE; Code language: SQL (Structured Query Language) (sql)

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

SELECT 
    index_name, 
    is_visible
FROM
    information_schema.statistics
WHERE
    table_schema = 'classicmodels'
        AND table_name = 'employees';Code language: SQL (Structured Query Language) (sql)

Here is the output:

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

SHOW INDEXES FROM employees;
Code language: SQL (Structured Query Language) (sql)

As mentioned earlier, the query optimizer does not use an 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 on 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 define 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:

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)
);Code language: SQL (Structured Query Language) (sql)

Second, try to make the discount_id index invisible:

ALTER TABLE discounts
ALTER INDEX discount_id INVISIBLE;Code language: SQL (Structured Query Language) (sql)

MySQL issued the following error message:

Error Code: 3522. A primary key index cannot be invisible   
Code language: SQL (Structured Query Language) (sql)

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:

SELECT @@optimizer_switch;
Code language: SQL (Structured Query Language) (sql)

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.