MySQL Invisible Indexes

MySQL 8 introduces invisible indexes. Invisible indexes are indexes that actually exist, but are not visible to the MySQL query optimizer. Even if used by FORCE INDEX, the optimizer will not use invisible indexes.

Before dropping an index, you can hide the index first. If this doesn’t affect performance, then you can actually drop the index.

A invisible indexe is invisible to the MySQL query optimizer, but it exists and is kept up to date for write operations.

MySQL Invisible Index Usage

MySQL allows you to use VISIBLE and INVISIBLE identify whether an index is visible.

Create invisible indexe

To create a invisible indexe, use the CREATE INDEX statement:

CREATE INDEX index_name
ON table_name(c1, c2, ...) INVISIBLE;

Modify the visibility of the index

To change the visibility of an existing index, use the ALTER TABLE statement:

ALTER TABLE table_name
ALTER INDEX index_name [VISIBLE | INVISIBLE];

ALTER TABLE You can easily toggle the visibility of existing indexes with the statement.

Note that you cannot set an index on a primary key column as a invisible indexe, or MySQL will give an error.

Use Invisible Indexes

The MySQL query optimizer does not use invisible indexes by default, but you can modify this behavior through the use_invisible_indexes property in the system variable optimizer_switch.

To view the current settings, use the following statement:

SELECT @@optimizer_switch;

index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on,condition_pushdown_from_having=on,not_null_range_scan=off

To modify the default behavior for the current session, use the following statement:

SET SESSION optimizer_switch="use_invisible_indexes=on";

Conclusion

A MySQL invisible indexe is a real index that is invisible to the MySQL query optimizer.