Skip to content
Join the waitlist for our new database scaling course
2.14
Invisible indexes
2.16

Making indexes invisible in MySQL

Sometimes you might need to drop an index in MySQL, but you're not entirely sure of the ramifications. You might feel nervous and want to make sure you've looked at every query that uses this index. This is where making an index invisible in MySQL comes in handy. In this video, we'll look at how to make indexes invisible and why you might want to do so.

Why make an index invisible?

There are times when you need to drop an index due to its inefficiency or because it's no longer used by your queries. However, before you do so, you get nervous and start thinking, "What if I've missed something?" Making an index invisible allows you to monitor how your queries perform without the index without having to rebuild it. If everything goes well, you can drop the index. But if something goes wrong, you can quickly turn the index back on without any hassle. Thus, making an index invisible reduces the risks and potential complications of dropping an index.

How to make an index invisible

Making an index invisible is a straightforward process in MySQL:

SQL
ALTER TABLE people ALTER INDEX email_idx INVISIBLE;

This command makes the email_idx index invisible so that it is no longer used in any queries.

To verify that the index is invisible, you can use the show indexes from [table] command again. This time, you'll notice that the email_idx index still exists, but its "Visible" column is set to "NO."

| Table | Non_unique | Key_name | Column_name | Collation | Cardinality | Index_type | Visible |
|--------|------------|-----------|-------------|-----------|-------------|------------|---------|
| people | 1 | email_idx | email | A | 468192 | BTREE | NO |

If you want to use the index again, you can revert it back to its visible state using the "alter index [index_name] visible" command.

SQL
ALTER TABLE people ALTER INDEX email_idx VISIBLE;

This command makes the index visible again so that MySQL uses it in queries.

Benefits of making an index invisible

Making an index invisible enables you to test your queries without risking data loss or any adverse impact on system performance. Once you make an index invisible, MySQL will stop using it in any queries, but it will still maintain this index's integrity. If something goes wrong during testing, it's easy to revert back to the original index state.

You can monitor your system's performance with minimal to no effect on your workflow. Once you are confident of your system's stability with the invisible index, you can fully drop the index.

About this lesson

Dropping an index can be a scary prospect. Before you commit to dropping it, you can make it invisible to test the ramifications on your application.

03:02
Closed captioned

Meet your instructor, Aaron Francis

Developer Education

I’m a dad to one-year-old twins and a software developer with a keen interest in processes and automation.

Feedback or questions? Reach out to our team at education@planetscale.com.

By submitting your email, you agree to the processing of your personal information by PlanetScale as described in the Privacy Policy.