Invisible IndexesΒΆ

Invisible indexes are a new feature in MySQL 8.0 that provide the ability to mark an index as unavailable for use by the Optimizer. This means that the index will still be maintained and kept up-to-date as data is modified, but no queries will be permitted to make use of the index (even if the query uses FORCE INDEX index_name).

Invisible indexes should not to be confused with disabled indexes, which the MyISAM storage engine implements (disabled indexes halt maintenance of an index). There are two notable use cases for invisible indexes:

  1. Soft delete. Whenever performing a destructive operation in production, it is desirable to be able to observe before making the change permanent. Think of this as like an index “Recycle bin.” In the event that you were mistaken and the index was being used, it is only a metadata change to make it visible again - much faster than recreating or restoring from backup. For example:

    ALTER TABLE Country ALTER INDEX c INVISIBLE;

  2. Staged rollout. Whenever adding indexes, it is important to consider that they may change your existing query plans - sometimes in undesirable ways. Invisible indexes present an opportunity to stage the rollout of an index to a desirable time, potentially away from peak load and when you are actively in a position to observe the system. For example:

    ALTER TABLE Country DROP INDEX c;
ALTER TABLE Country ADD INDEX c (Continent) INVISIBLE;
# after some time
ALTER TABLE Country ALTER INDEX c VISIBLE;

All indexes default to being visible unless specified otherwise. You can search for invisible indexes across all schemas with:

SELECT * FROM information_schema.statistics WHERE is_visible='NO';
*************************** 1. row ***************************
TABLE_CATALOG: def
 TABLE_SCHEMA: world
   TABLE_NAME: Country
   NON_UNIQUE: 1
 INDEX_SCHEMA: world
   INDEX_NAME: c
 SEQ_IN_INDEX: 1
  COLUMN_NAME: Continent
    COLLATION: A
  CARDINALITY: 7
     SUB_PART: NULL
       PACKED: NULL
     NULLABLE:
   INDEX_TYPE: BTREE
      COMMENT: disabled
INDEX_COMMENT:
   IS_VISIBLE: NO

Tip

It’s a good idea to remove un-needed indexes. While most practitioners are aware that indexes hurt the performance of modifications (insert, update) this itself is a simplification. They can also hurt the performance of reads as the optimizer needs to evaluate them for plan selection.