MySQL Invisible Indexes: How to Test an Index Removal Before Deleting It

Database indexes help applications retrieve data efficiently, but every index also introduces maintenance overhead. When a row is inserted, updated, or deleted, MySQL may need to update multiple indexes. These additional operations consume storage and can increase write costs.

Over time, databases often accumulate indexes that are no longer useful. An index might have been created for an old query, replaced by a better composite index, or left behind after an application feature changed. Removing unnecessary indexes can reduce storage requirements and simplify database maintenance.

The challenge is determining whether an index is genuinely unnecessary. An index that appears unused during normal testing might support a reporting query, a monthly business process, or an infrequent administrative operation.

MySQL provides invisible indexes to help developers investigate this problem safely. An invisible index remains present in the database, but the optimizer generally ignores it when selecting an execution plan. This allows developers to observe query performance without permanently deleting the index.

In this article, you will learn how invisible indexes work, how to test an index removal, how to measure query performance, and when an invisible index is useful in production.

What Are Invisible Indexes in MySQL?

An invisible index is an index that exists in the database but is normally excluded from the optimizer's index-selection process.

Unlike a deleted index, an invisible index remains defined in the table. MySQL continues maintaining it when the underlying data changes.

For example, consider an Orders table with an index on CustomerId:

CREATE TABLE Orders
(
    Id BIGINT PRIMARY KEY AUTO_INCREMENT,
    CustomerId BIGINT NOT NULL,
    OrderDate DATETIME NOT NULL,
    Status VARCHAR(30) NOT NULL,
    TotalAmount DECIMAL(12, 2) NOT NULL,

    INDEX idx_orders_customer (CustomerId)
);

The index idx_orders_customer can help MySQL locate orders belonging to a specific customer.

Suppose the development team suspects that the index is unnecessary because another index or access pattern might already serve the relevant queries.

Instead of dropping the index immediately, the team can make it invisible:

ALTER TABLE Orders
ALTER INDEX idx_orders_customer INVISIBLE;

After this change, the optimizer normally stops considering idx_orders_customer when building execution plans.

The index itself is not deleted. This distinction allows the team to evaluate the effect of removing the index before making a permanent change.

Invisible Indexes Versus Dropped Indexes

The difference between these operations is important.

Behavior	Visible index	Invisible index	Dropped index
Index definition exists	Yes	Yes	No
Optimizer normally considers it	Yes	No	No
Index maintained during writes	Yes	Yes	No
Index storage retained	Yes	Yes	No
Can be restored without rebuilding the index	Not applicable	Yes	No
Useful for testing index removal	Yes, as a baseline	Yes	Only after deletion, with a rebuild needed to restore it

An invisible index is therefore useful when the main question is whether the optimizer can operate effectively without a particular index.

However, invisibility does not provide the same storage or write-performance benefits as dropping the index. Since MySQL still maintains the invisible index, the underlying write and storage costs remain.

Why Should You Test Index Removal Before Dropping It?

Indexes are often created to solve a specific performance problem. Later, application behavior changes, but the original index remains.

For example, a table might contain these indexes:

CREATE INDEX idx_orders_customer
ON Orders (CustomerId);

CREATE INDEX idx_orders_customer_date
ON Orders (CustomerId, OrderDate);

CREATE INDEX idx_orders_status
ON Orders (Status);

The composite index on (CustomerId, OrderDate) may support queries that filter by customer and order date.

Depending on the queries and their predicates, it may also support searches that filter only by CustomerId, because that column is the leftmost column in the composite index.

This could make the single-column index on CustomerId redundant for some workloads.

However, redundancy cannot be determined from column names alone. The single-column index might have different ordering, size, selectivity, or other characteristics that make it useful for particular queries.

Making the index invisible provides a controlled way to investigate the impact.

The main benefits include:

Testing whether important queries still receive efficient execution plans.
Identifying queries that depend on the index.
Reducing the risk of an unexpected performance regression after deployment.
Restoring the index's visibility without rebuilding it.
Gathering evidence before permanently removing an index.

The technique is especially useful for large tables, production databases, and applications with workloads that are difficult to reproduce completely in a test environment.

Checking Existing Indexes Before Making Changes

Before changing an index, inspect the indexes defined on the table.

You can use:

SHOW INDEX FROM Orders;

This command displays information about the table's indexes, including index names, indexed columns, column order, uniqueness, and visibility.

You can also query information_schema.statistics:

SELECT
    TABLE_NAME,
    INDEX_NAME,
    COLUMN_NAME,
    SEQ_IN_INDEX,
    NON_UNIQUE,
    IS_VISIBLE
FROM information_schema.statistics
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME = 'Orders'
ORDER BY INDEX_NAME, SEQ_IN_INDEX;

The IS_VISIBLE column indicates whether the index is visible to the optimizer.

This query is useful when reviewing several indexes or building an index inventory for database maintenance.

Before proceeding, confirm the following:

The index is not required to enforce a primary key or unique constraint.
No critical query depends on the index for acceptable performance.
The index is not required by an operational process that is missing from ordinary test workloads.
The table has a reliable backup and an appropriate recovery plan.
You understand the table's write volume and index-maintenance requirements.

Invisible indexes cannot be used to bypass the enforcement of primary key or unique constraints. MySQL does not permit primary key indexes to be made invisible, and unique indexes have additional restrictions.

Making an Index Invisible

Once you identify a candidate index, make it invisible using ALTER TABLE.

For example:

ALTER TABLE Orders
ALTER INDEX idx_orders_customer INVISIBLE;

MySQL changes the index's visibility while retaining the index definition and its stored data.

After the operation, inspect the index:

SHOW INDEX FROM Orders;

The index should now report IS_VISIBLE as NO in the output.

You can verify the same information through information_schema:

SELECT
    INDEX_NAME,
    IS_VISIBLE
FROM information_schema.statistics
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME = 'Orders'
  AND INDEX_NAME = 'idx_orders_customer';

This confirms the index's current visibility state.

What Happens to Queries After the Change?

Consider this query:

SELECT
    Id,
    CustomerId,
    OrderDate,
    TotalAmount
FROM Orders
WHERE CustomerId = 125;

Before making the index invisible, MySQL might choose idx_orders_customer to locate matching rows.

After the index becomes invisible, the optimizer must choose from the indexes it is permitted to consider, or use another available access method.

The query can still return correct results. Index invisibility changes optimizer choices, not the logical meaning of the SQL statement.

However, the execution plan may become less efficient if the optimizer cannot find an equally suitable alternative.

Comparing Execution Plans With EXPLAIN

The first step in evaluating an invisible index is comparing query execution plans.

MySQL provides EXPLAIN to show how the optimizer intends to execute a query.

For example:

EXPLAIN
SELECT
    Id,
    CustomerId,
    OrderDate,
    TotalAmount
FROM Orders
WHERE CustomerId = 125;

Inspect the following fields in the output:

Field	What it tells you
`type`	The access method selected by the optimizer.
`possible_keys`	Indexes that may be suitable for the query.
`key`	The index selected by the optimizer, if any.
`rows`	The estimated number of rows MySQL expects to examine.
`filtered`	The estimated percentage of examined rows that satisfy the relevant condition.
`Extra`	Additional execution details, such as filtering or temporary operations.

The exact interpretation depends on the query and the execution plan.

For example, if key previously showed idx_orders_customer and now shows another index, the optimizer has changed its access strategy.

If key becomes NULL, MySQL is not using an index for that table access. That does not automatically prove the query is slow, but it deserves investigation when the table is large.

Use EXPLAIN ANALYZE for Actual Execution Information

On supported MySQL versions, EXPLAIN ANALYZE executes the query and reports actual execution information alongside the plan.

EXPLAIN ANALYZE
SELECT
    Id,
    CustomerId,
    OrderDate,
    TotalAmount
FROM Orders
WHERE CustomerId = 125;

Unlike ordinary EXPLAIN, this operation runs the query. Use caution with expensive queries and production workloads.

Compare actual execution time, row counts, and the amount of work performed. Estimated row counts alone are not sufficient to determine whether removing an index is safe.

A query may have a similar-looking plan but perform more work because it examines many more rows. Conversely, an alternate plan may be equally efficient for the application's actual data distribution.

For a meaningful comparison, test representative parameter values. A customer with a few orders and a customer with hundreds of thousands of orders can produce very different performance characteristics.

Testing an Invisible Index With Optimizer Hints

MySQL also provides a session-level setting that allows invisible indexes to be considered by the optimizer.

For example:

SET SESSION optimizer_switch = 'use_invisible_indexes=on';

When this setting is enabled, invisible indexes can participate in optimizer decisions for the current session.

This is useful when you want to compare execution plans before and after the visibility change without making the index visible to every connection.

For example:

SET SESSION optimizer_switch = 'use_invisible_indexes=on';

EXPLAIN
SELECT
    Id,
    CustomerId,
    OrderDate,
    TotalAmount
FROM Orders
WHERE CustomerId = 125;

This allows the optimizer to consider invisible indexes again.

To return to the normal behavior for that session:

SET SESSION optimizer_switch = 'use_invisible_indexes=off';

Be aware that this setting affects the optimizer's treatment of invisible indexes in the session. It is not a command to restore the index's global visibility.

For repeatable testing, use separate sessions or explicitly record the session settings used for each experiment.

A Practical Workflow for Testing Index Removal

A controlled testing process is more reliable than changing an index and waiting for users to report performance problems.

Step 1: Establish a Baseline

Identify the queries that may use the index.

Capture their execution plans, execution times, row counts, and relevant workload metrics while the index is visible.

Use representative data and query parameters. Include frequently executed queries and infrequent operations that may be important to the business.

Step 2: Make the Index Invisible

Apply the visibility change:

ALTER TABLE Orders
ALTER INDEX idx_orders_customer INVISIBLE;

Record the time of the change and the index name so that any performance changes can be correlated with the experiment.

Step 3: Test Critical Queries

Repeat the baseline queries and inspect their execution plans.

Look for full table scans, higher row estimates, increased actual execution time, and alternative indexes that may be unsuitable.

Also test reporting jobs, scheduled tasks, and administrative queries that may not run during normal application traffic.

Step 4: Observe Representative Workloads

Run the application through realistic read and write scenarios.

Monitor query latency, database CPU usage, disk activity, and application response times. If possible, use query-level performance monitoring to identify regressions.

Remember that the invisible index is still maintained. The experiment primarily tests the effect of removing the index from optimizer consideration, not the performance improvement that dropping it might provide.

Step 5: Restore or Drop the Index

If the results show a significant regression, restore visibility:

ALTER TABLE Orders
ALTER INDEX idx_orders_customer VISIBLE;

If the index is unnecessary and the testing results support removal, drop it during an appropriate maintenance window:

ALTER TABLE Orders
DROP INDEX idx_orders_customer;

Dropping the index is a permanent schema change. Restoring it later requires recreating the index, which may take time and consume substantial database resources on a large table.

Common Mistakes When Working With Invisible Indexes

Assuming an Invisible Index Stops Consuming Storage

An invisible index remains physically present. MySQL continues maintaining it as rows change.

If the objective is to reclaim disk space or reduce index-maintenance overhead, invisibility is a testing step, not the final optimization.

Testing Only One Query

An index can support many different queries. Testing a single query may miss an important workload that depends on the index.

Review query logs, application code, performance monitoring data, and scheduled jobs before deciding that an index is unnecessary.

Ignoring Unique Constraints

Unique indexes enforce data-integrity rules. Making a unique index invisible does not remove the uniqueness requirement, and MySQL imposes restrictions on invisible unique indexes.

Before changing visibility, verify whether the index supports a constraint and confirm that the intended operation is supported by your MySQL version.

Treating EXPLAIN Estimates as Actual Performance

EXPLAIN provides estimates of how MySQL expects to execute a query. Those estimates do not guarantee actual performance.

Use EXPLAIN ANALYZE when appropriate, along with representative workload measurements. Avoid relying on a single estimated row count to justify index removal.

Forgetting to Check Session Settings

A session with use_invisible_indexes=on can consider invisible indexes, while sessions using the default behavior normally cannot.

If testing sessions use different settings, their execution plans may differ. Record the settings so that the comparison remains valid.

Best Practices for Production Databases

Follow these practices when evaluating index removal:

Review index definitions and constraints before making changes.
Use invisible indexes as a reversible testing mechanism.
Capture baseline execution plans and query performance metrics.
Test queries with representative data distributions and parameter values.
Monitor application latency and database resource usage during the experiment.
Include scheduled jobs and infrequent reporting queries in the evaluation.
Keep the experiment long enough to cover meaningful workload variations.
Restore visibility promptly if the change causes unacceptable performance degradation.
Drop the index only after collecting sufficient evidence that it is unnecessary.
Test schema changes in a staging environment before applying them to critical production systems.

Invisible indexes are particularly useful for investigating suspected redundant indexes, but they do not replace query analysis or workload monitoring.

Advantages and Disadvantages

Advantages

Allow developers to test the effect of excluding an index from normal optimizer decisions.
Preserve the index so its visibility can be restored without rebuilding it.
Reduce the risk of permanently removing an index that supports an important query.
Help identify alternative execution plans and potential performance regressions.
Support a more evidence-based database optimization process.

Disadvantages

Do not immediately reclaim index storage.
Do not eliminate the cost of maintaining the index during inserts, updates, and deletes.
Require representative workload testing to produce reliable conclusions.
Can create misleading test results if session-level optimizer settings differ.
Do not guarantee that dropping the index will improve overall performance by a predictable amount.

Conclusion

MySQL invisible indexes provide a practical way to evaluate whether an index is still necessary before deleting it permanently. By making an index invisible, developers can observe how the optimizer behaves without that index while preserving the ability to restore its visibility quickly.

The process should include reviewing existing indexes, capturing baseline execution plans, testing critical queries, and monitoring representative workloads. EXPLAIN and EXPLAIN ANALYZE help reveal changes in query execution, while session-level optimizer settings support controlled comparisons.

The most important limitation is that an invisible index still consumes storage and requires maintenance during data modifications. Its primary purpose is to test the consequences of removing an index from optimizer consideration.

When used as part of a structured database optimization process, invisible indexes help teams make safer schema changes and reduce the risk of unexpected query performance problems.