What Is a Invisible Indexes in MySQL?
An invisible index is maintained but ignored by the optimizer by default, enabling reversible tests before index removal.
How the Invisible Indexes Algorithm Works
An invisible index is maintained but ignored by the optimizer by default, enabling reversible tests before index removal.
When to Use Invisible Indexes
Testing if a workload depends on an index without immediately dropping it.
How to Create a MySQL Invisible Indexes
Review data quality, existing structures, feature support, build time, and deployment requirements before creating an index on a production table.
ALTER TABLE orders ALTER INDEX orders_legacy_idx INVISIBLE;
ALTER TABLE orders ALTER INDEX orders_legacy_idx VISIBLE;Invisible Indexes Query Example
This example demonstrates the matching query shape. Check the execution plan to confirm that the engine can use the index.
SELECT customer_id FROM orders WHERE customer_id = 42;Queries Supported by Invisible Indexes
Testing if a workload depends on an index without immediately dropping it. A similar-looking predicate may not use the same access path; operators, expressions, key order, and data type all matter.
Read Performance, Storage, and Write Costs
Still consumes space and adds write maintenance; invisibility does not reclaim resources. Indexes reduce some search work but require storage and add maintenance to writes.
Invisible Indexes Limitations and Common Mistakes
Syntax and optimizer controls vary by release; invisible indexes still incur DML cost.
Do not assume that an index being present means the optimizer chose it or that the query became selective.
How to Measure and Tune Invisible Indexes
Compare plans and workload latency visible versus invisible; drop only after evidence.
Compare representative plans and timings before and after, including concurrent writes and realistic data volumes.
Invisible Indexes Questions
Will the database always use this index?
No. The optimizer weighs selectivity, estimated costs, statistics, cache state, and the amount of data returned.
What should I verify before production?
Confirm version support, query compatibility, write overhead, build/rollback procedures, and index behavior during backup and recovery.
Official MySQL Index Documentation
Check MySQL 8.4 Reference Manual: Optimization and Indexes for release-specific requirements and supported operators.