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:

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.

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.