The Risk of the 'Drop Index' Command
In a production environment, dropping an index is often a high-stakes gamble. You might identify an index that appears unused according to your performance schema, but once you drop it, a critical report or a background cron job might suddenly grind the database to a halt. Rebuilding a large index on a table with millions of rows can take hours, during which your application performance suffers.
MySQL 8.0 introduced a powerful solution to this dilemma: Invisible Indexes. This feature allows you to hide an index from the MySQL Optimizer without actually removing the data structure from the disk. It is the ultimate safety net for database administrators and developers.
How Invisible Indexes Work
An invisible index is still maintained by the storage engine. When you insert, update, or delete rows, MySQL continues to update the index. However, the Optimizer—the part of MySQL that decides which index to use for a query—will ignore it. This allows you to simulate the index's absence to see if performance degrades before you commit to a permanent deletion.
Making an Index Invisible
You can toggle the visibility of an index using the ALTER TABLE command. If you suspect an index named idx_user_email is redundant, you can hide it with the following syntax:
ALTER TABLE users ALTER INDEX idx_user_email INVISIBLE;
If your application performance remains stable after this change, you can safely drop the index later. If queries suddenly slow down, you can make the index visible again instantly, without the overhead of rebuilding it:
ALTER TABLE users ALTER INDEX idx_user_email VISIBLE;
Testing New Indexes Without Impacting Users
Invisible indexes aren't just for deletion; they are also useful for testing new optimizations. You can create a new index as invisible to ensure it doesn't accidentally change the execution plan of existing queries until you are ready to test it specifically.
CREATE INDEX idx_created_at_status ON orders (created_at, status) INVISIBLE;
To test if this new index actually helps a specific query, you can force the optimizer to see invisible indexes at the session level. This is perfect for benchmarking in a staging environment or a controlled session:
SET SESSION optimizer_switch = 'use_invisible_indexes=on'; -- Now run your EXPLAIN plan to see if the index is picked up EXPLAIN SELECT * FROM orders WHERE created_at > '2023-01-01' AND status = 'shipped';
Key Considerations
While invisible indexes are highly effective, keep these rules in mind:
- Primary Keys: You cannot make a Primary Key invisible. MySQL requires the Primary Key to be visible for basic table operations.
- Maintenance Overhead: Because the index is still updated during write operations, an invisible index still incurs a performance cost on
INSERTandUPDATEstatements. Do not leave indexes in an invisible state indefinitely; eventually, either delete them or make them visible. - Unique Constraints: Unique indexes still enforce uniqueness even when invisible.
By incorporating invisible indexes into your workflow, you move away from guesswork and toward a data-driven approach to database optimization. It allows you to refactor your schema with the confidence that a single command can undo any unforeseen performance regressions.
