In MySQL 8, invisible indexes are a feature that allows you to create an index without it being used by the optimizer. This can be useful in situations where you want to test the impact of an index on query performance, or when you want to temporarily remove an index for maintenance purposes without actually dropping it.
When you create an index as invisible, it is not used by the optimizer during query execution. This means that queries that would normally use the index will not use it, and may therefore have a different execution plan and performance characteristics. However, the index is still maintained by the database, and can be used for queries that explicitly reference it.
To create an invisible index in MySQL 8, you can use the INVISIBLE keyword in the CREATE INDEX statement. For example:
CREATE INDEX idx_name ON table_name (column1, column2) INVISIBLE;
To make the index visible again, you can use the ALTER TABLE statement with the VISIBLE keyword. For example:
ALTER TABLE table_name ALTER INDEX idx_name VISIBLE;
You can also use the SHOW INDEX statement to display a list of all indexes on a table, including whether they are visible or invisible. For example:
SHOW INDEX FROM table_name;
Invisible indexes in MySQL 8 can be useful for testing and maintenance purposes, but they should be used with caution. It’s important to remember that queries that rely on an index that has been made invisible will not use the index, and may therefore have suboptimal performance. Additionally, because the index is still maintained by the database, it may still consume disk space and memory resources. Therefore, it’s recommended to use invisible indexes only for short periods of time, and to carefully monitor query performance and system resources when using them.
