Mastering the Art of Invisible Indexes in MySQL

Welcome to a technical dive into one of MySQL's nifty features—Invisible Indexes. Released with MySQL 8.0, this feature adds a layer of flexibility that every database administrator dreams of: the ability to toggle an index's visibility on the fly without dropping it! Let's delve into how this feature works and why it's a game-changer.

What are Invisible Indexes?

Invisible Indexes are a gem from the MySQL 8.0 suite that allows you to control the visibility of indexes to the optimizer. Essentially, you can decide whether or not an index should be considered during query optimization, all without the need to permanently add or remove the index.

Here’s how you can make an index invisible or visible:

-- To make an index invisible
ALTER TABLE <table_name> ALTER INDEX <index_name> INVISIBLE;

-- To make an index visible
ALTER TABLE <table_name> ALTER INDEX <index_name> VISIBLE;

Why use Invisible Indexes?

Imagine you're tuning the performance of a database or testing how queries perform without certain indexes. Traditionally, you might drop an index to test and later re-add it—a process that's not only time-consuming but also a bit risky in live environments. Invisible Indexes solve this by allowing the index to remain but simply telling the optimizer to ignore it.

This feature is not just about convenience; it's about performance. Making an index invisible is almost instantaneous, much faster than dropping and re-adding indexes.

Real-World Verification: A Case Study

To understand the impact of this feature, I used a Gitpod Enterprise workspace, leveraging the powerful & an ephemeral environment to avoid the hassle of setups and installations. Here’s a glance at the setup:

• Machine: Gitpod Enterprise XXLarge workspace
• OS: Ubuntu 22.04.4 LTS (Jammy Jellyfish)
• Containerization: Docker version 26.0.1
• MySQL Version: Official MySQL 8.0 Docker image

Setting the stage

I started by creating a books table and populating it with a substantial dataset:

CREATE TABLE books (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL
);

-- Adding 100 million records
SET SESSION cte_max_recursion_depth = 100000000;
INSERT INTO books(name)
WITH RECURSIVE cte (n) AS (
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM cte WHERE n < 100000000
)
SELECT CONCAT('book_', n) FROM cte;

Output: Query OK, 100000000 rows affected (7 min 40.58 sec)
Records: 100000000 Duplicates: 0 Warnings: 0

Experimenting with Indexes

Next, I added an index to the name column and performed operations to toggle its visibility:

-- Adding an index
ALTER TABLE books ADD INDEX idx_name(name);

Output: Query OK, 0 rows affected (3 min 54.75 sec)
Records: 0 Duplicates: 0 Warnings: 0

-- Making the index invisible
ALTER TABLE books ALTER INDEX idx_name INVISIBLE;

Output: Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

-- Making the index visible again
ALTER TABLE books ALTER INDEX idx_name VISIBLE;

Output: Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0

-- Dropping the index
DROP INDEX idx_name ON books;

Output: Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0

Observations and Results

The results were enlightening. Making the index invisible and visible again was nearly instantaneous, contrasting sharply with the time required to add or drop the index. This emphasizes the efficiency of using Invisible Indexes for performance tuning.

Conclusion

Invisible Indexes stand as a crucial feature within MySQL's toolkit, offering flexibility and performance benefits. They are particularly useful in scenarios where indexes may interfere with performance, or when testing the impact of indexes on query execution without the overhead of dropping and re-adding them.

For detailed MySQL operations, the MySQL official documentation on Online DDL Operations offers comprehensive guidance, including scenarios like adding full-text or spatial indexes where concurrent DML operations are not permitted.

Invisible Indexes aren't just a feature; they're a strategic advantage for database optimization—definitely a tool worth mastering for anyone involved in database administration or optimization!