MySQL Invisible Index

In this tutorial, we will be learning about the invisible index in MySQL and some points related to it which will help you in the future. So, let’s get started!

Also read: Descending Index in MySQL to Speed Up Queries

Introduction to MySQL Invisible Index

The invisible indexes are very important for checking if the index that we have created is really needed or not.

Let us take a scenario here. Let’s suppose you have created an index on a table. The table consists of millions of records. You thought that the index you created is not helping the queries, so you drop it. Later you found that the index just you dropped was so essential and you have to recreate it.

Now the main problem occurs. The table already consists of millions of records and adding the new index can be expensive. If you try to create an index on the table, it might take days or weeks depending on the number of records.

To overcome this problem, the Invisible index comes into the picture.

You can test the effect of removing the index on query performance without making the change that will cost you time and performance. The invisible indexes are very helpful as making the index invisible and visible again is much faster than dropping and re-adding the index.

When you make an index invisible, the optimizer simply ignores it and will not be used by the query.

MySQL keeps these indexes up to date when the data in the columns associated with that indexes changes.

Syntax of Invisible Index

To create an invisible index, the following syntax is used.

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

Here, you create an index with the CREATE INDEX statement and use the keyword INVISIBLE to make the index invisible.

For example,

CREATE INDEX empName
ON employees(firstName,lastName) INVISIBLE;Code language: SQL (Structured Query Language) (sql)

You can make the index visible later by using the ALTER command and the VISIBLE keyword.

The syntax to alter the index visibility is as follows-

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

For example,

ALTER TABLE students
ALTER INDEX  stud INVISIBLE;Code language: SQL (Structured Query Language) (sql)

To check the visibility of any index, you can use the ‘statistics’ present in the ‘information_schema’ database.

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

In the above query, the ‘journaldev’ is the database name and the ‘students’ is a table.

As you can see, the index name ‘stud’ is set to invisible.

You can check the index names available on the table using the SHOW INDEX statement.

SHOW INDEX FROM students;Code language: SQL (Structured Query Language) (sql)

Here, the index ‘PRIMARY’ is automatically created by MySQL because we have set the constraint PRIMARY to the id column of the table.

Remember the PRIMARY index point because we will be learning the important relationship between the PRIMARY key and the invisible index.

Invisible Index and Primary Key

This is the most important point about the invisible index.

Note that, we can not make the PRIMARY index invisible. The PRIMARY is none other than the primary key on the table.

If you try to make the primary key column invisible, you will get an error.

Also, if you define a UNIQUE index on a NOT NULL column where the table doesn’t have a primary key, MySQL will automatically understand that it is a primary key and won’t allow you to make the index invisible.

Let’s try it out.

CREATE TABLE laptops(
name VARCHAR(50) NOT NULL,
company VARCHAR(50),
UNIQUE name_idx(name)
);Code language: SQL (Structured Query Language) (sql)

Here, we have created a table and the column ‘name’ is set as a UNIQUE and NOT NULL.

Let’s now try to make the index ‘nam_idx’ invisible.

ALTER TABLE laptops 
ALTER INDEX name_idx INVISIBLE;Code language: SQL (Structured Query Language) (sql)

As you can see, you get an error because the column is set to be a primary key on the table and the primary key index can not be invisible.

Summary

In this tutorial, we have learned about the invisible indexes in MySQL. We have also seen the reason why an invisible index exists in MySQL and what is its real-life use. I hope you have understood the MySQL invisible index. You can refer to the official documentation for more information.

References

Stackoverflow thread on visible index vs invisible index.

MySQL official documentation on invisible indexes.