Invisible Index in MySQL 8: What It Is and How It Works

An invisible index in MySQL 8 is an index that exists in the database but is ignored by the query optimizer during query execution. It allows you to test or disable an index without dropping it, making it easy to compare performance or troubleshoot without losing the index definition.

How It Works

Think of an invisible index like a book's index that is still printed but hidden from readers. The index is there, but the database does not use it when searching for data. This means the index is maintained during data changes but ignored during queries.

This feature helps you test if an index is really needed. You can make an index invisible to see if queries run faster or slower without it, without deleting the index and losing its structure. If needed, you can quickly make it visible again.

Example

This example shows how to create a visible index, make it invisible, and then make it visible again.

sql

CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  department VARCHAR(50)
);

-- Create a visible index on department
CREATE INDEX idx_department ON employees(department);

-- Make the index invisible
ALTER TABLE employees ALTER INDEX idx_department INVISIBLE;

-- Make the index visible again
ALTER TABLE employees ALTER INDEX idx_department VISIBLE;

Output

Query OK, 0 rows affected (for each statement)

When to Use

Invisible indexes are useful when you want to test the impact of an index on query performance without dropping it. For example, if you suspect an index is slowing down writes or not helping queries, you can make it invisible and compare results.

They are also helpful during database tuning or upgrades, allowing DBAs to safely disable indexes temporarily without losing them. This reduces risk and downtime.

Key Points

• Invisible indexes exist but are ignored by the optimizer.
• They are maintained during data changes, so no rebuild is needed when toggling visibility.
• Useful for testing and performance tuning without dropping indexes.
• Introduced in MySQL 8.0.

Key Takeaways

Invisible indexes let you disable an index without dropping it, helping test query performance safely.

They are maintained during data changes but ignored by the query optimizer.

Use invisible indexes to troubleshoot or tune your database with minimal risk.

This feature is available starting from MySQL 8.0.