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

An invisible index in MySQL is an index that exists in the database but is ignored by the query optimizer during query execution. It allows you to keep the index for testing or future use without affecting current query performance.

How It Works

Think of an invisible index like a bookmark in a book that you keep but don't use while reading. The index is still there, maintained by MySQL, but the database engine does not consider it when deciding how to run queries. This means the index does not speed up or slow down queries, but it still exists for reference or testing.

This feature helps when you want to test if an index is useful without dropping it permanently. You can make the index invisible, run your queries, and see if performance changes. If you want, you can make it visible again later without rebuilding it.

Example

This example shows how to create an 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 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 removing an index without actually deleting it. For example, if you suspect an index is not helping performance or is slowing down writes, you can make it invisible and monitor query speed.

They are also helpful during database maintenance or upgrades, allowing DBAs to prepare indexes for future use or phase them out gradually.

Key Points

• Invisible indexes exist but are ignored by the query optimizer.
• They allow safe testing of index usefulness without dropping.
• Making an index invisible does not remove it or free storage.
• Indexes can be toggled between visible and invisible anytime.

Key Takeaways

Invisible indexes let you keep indexes without affecting query plans.

Use invisible indexes to test index impact safely before dropping.

You can toggle index visibility without rebuilding the index.

Invisible indexes do not improve or degrade query performance.

They help in database maintenance and performance tuning.