Unlocking MySQL 8.0 Invisible Indexes for Safer Query Optimization

Introduction

MySQL 8.0 introduces invisible indexes (INVISIBLE), a metadata‑only attribute that hides an index from the optimizer while keeping it available for later activation, similar to Oracle's invisible indexes.

When to Use Invisible Indexes

When an existing index is rarely used and you want to test its removal without risking a sudden workload that depends on it.

When a query that runs only once a month would benefit from an index that you do not want to keep visible all the time.

When you need to evaluate the impact of a new index on the whole workload without affecting current queries.

Example Setup

First create a simple table and two visible indexes:

CREATE TABLE f1 (
  id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  f1 INT,
  f2 INT,
  PRIMARY KEY (id),
  KEY idx_f1 (f1),
  KEY idx_f2 (f2)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

ALTER TABLE f1 ADD KEY idx_f1 (f1), ADD KEY idx_f2 (f2);

Generate test data (10,000 rows) with a stored procedure (omitted for brevity). After the data is loaded, make the second index invisible:

ALTER TABLE f1 ALTER INDEX idx_f2 INVISIBLE;

Effect on the Optimizer

Running an EXPLAIN on a query that filters on f2 before the index is invisible shows that the optimizer does not consider the index:

EXPLAIN SELECT COUNT(*) FROM f1 WHERE f2 = 52\G

The output contains possible_keys: NULL and key: NULL, meaning the invisible index is ignored.

If you try to force the invisible index, MySQL returns an error because the index is not visible:

ERROR 1176 (42000): Key 'idx_f2' doesn't exist in table 'f1'

Enabling Use of Invisible Indexes

MySQL provides an optimizer switch to allow the optimizer to consider invisible indexes when it deems them beneficial:

SET @optimizer_switch='use_invisible_indexes=on';

After setting the switch, the same EXPLAIN now shows the invisible index as a possible and chosen key:

EXPLAIN SELECT COUNT(*) FROM f1 WHERE f2 = 52\G

The output now contains possible_keys: idx_f2 and key: idx_f2, confirming that the optimizer can use the invisible index when the switch is enabled.

Conclusion

Invisible indexes give DBAs a low‑cost way to hide indexes from the optimizer, making it easier to test index removal, stage temporary indexes, or evaluate performance impact without dropping the index. By toggling the use_invisible_indexes optimizer switch, you can let MySQL decide when an invisible index should be used, providing a flexible safety net for schema changes.