How to Configure and Use MySQL Invisible Indexes

Modern MySQL versions (8.0 and later) introduce invisible indexes, a powerful feature that allows database administrators to hide indexes from the query optimizer without actually dropping them. This article provides a practical guide on how to configure invisible indexes, demonstrates their syntax, and highlights how they benefit database performance tuning, safe schema migrations, and risk mitigation.

What is an Invisible Index?

An invisible index is a standard index that is maintained normally by MySQL during data modification operations (such as INSERT, UPDATE, and DELETE), but is ignored by the query optimizer when executing SELECT queries.

Unlike disabled indexes in other database engines, invisible indexes stay fully updated in the background. This ensures that if you decide to make the index visible again, it is immediately available for use without needing a costly rebuild.

How to Configure Invisible Indexes

You can create new invisible indexes, alter existing ones, and toggle their visibility using standard DDL (Data Definition Language) statements.

1. Creating a New Invisible Index

To create an invisible index during table creation or on an existing table, append the INVISIBLE keyword to the index definition:

-- Creating a table with an invisible index
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) NOT NULL,
    status VARCHAR(50),
    INDEX idx_status (status) INVISIBLE
);

-- Adding an invisible index to an existing table
CREATE INDEX idx_email ON users (email) INVISIBLE;

2. Toggling Index Visibility

You can change the visibility of an existing index at any time using the ALTER TABLE statement. This change occurs instantly metadata-wise, requiring no data copying or rebuilding.

-- Make an existing index invisible
ALTER TABLE users ALTER INDEX idx_email INVISIBLE;

-- Make an invisible index visible again
ALTER TABLE users ALTER INDEX idx_email VISIBLE;

3. Testing Invisible Indexes

To test how query execution plans behave with invisible indexes before making them visible to the entire application, you can force the optimizer to consider them for your current session:

-- Enable invisible indexes for the current session
SET SESSION optimizer_switch = 'use_invisible_indexes=on';

-- Run EXPLAIN to verify if the query uses the invisible index
EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';

-- Disable it again when finished testing
SET SESSION optimizer_switch = 'use_invisible_indexes=off';

Benefits of Using Invisible Indexes

1. Risk-Free Index Dropping (Soft Delete)

Dropping a large index on a production database is risky. If an active query relies on that index, dropping it can cause immediate performance degradation. Rebuilding a dropped index on a multi-million row table can take hours and lock resources.

By making the index INVISIBLE first, you can safely test the impact of its removal. If query performance degrades, you can instantly revert the change by making the index VISIBLE again. If performance remains stable over a designated monitoring period, you can safely drop the index.

2. Graceful Rollbacks

Traditional index optimization involves a cycle of dropping and recreating indexes. If a dropped index is found to be necessary, recreating it consumes CPU, I/O, and time. Invisible indexes eliminate this overhead, allowing you to “rollback” an index deletion in milliseconds.

3. Staged Index Deployments

When deploying new indexes to production, you might want to measure their impact on write performance (INSERT/UPDATE operations) before allowing the optimizer to use them for read queries. Creating the index as INVISIBLE allows you to monitor the overhead of index maintenance without affecting query execution plans.