MySQL Invisible Indexes: Drop an Index Safely

Your orders table on MySQL 8.4 carries eleven secondary indexes, inserts are getting slower, and sys.schema_unused_indexes lists four of them as unused. Dropping them looks like a free win until the monthly finance report, which runs on a replica the view never saw, falls back to a full table scan, and rebuilding the index can take hours. MySQL has a safer step. ALTER TABLE ... ALTER INDEX ... INVISIBLE hides an index from the optimizer while InnoDB keeps it up to date, so you can watch what breaks and undo it with one statement. This guide covers what invisibility changes, how to find real candidates, the replica traps, and a workflow from identify to drop.

Scope and review date: November 2026. Covers MySQL 8.4 LTS, with the same behavior in 9.7 LTS, whose invisible index chapter matches 8.4's text. The feature exists since MySQL 8.0. Statements, defaults, and error numbers were checked against the MySQL 8.4 reference manual, the MySQL 8.4 error reference, and the sys schema source. They were not run in a lab, so rehearse them on staging.

What is an invisible index in MySQL?

An invisible index is one the optimizer ignores when it builds plans. The MySQL 8.4 invisible indexes chapter says the feature "applies to indexes other than primary keys (either explicit or implicit)". Indexes are visible by default. You can create one invisible, or flip an existing one either way.

-- Hide an existing index from the optimizer
ALTER TABLE shop.orders ALTER INDEX idx_orders_status INVISIBLE;

-- Make it usable again
ALTER TABLE shop.orders ALTER INDEX idx_orders_status VISIBLE;

-- Create a new index that starts invisible
CREATE INDEX idx_orders_region ON shop.orders (region) INVISIBLE;

Interpretation: the first two statements are the whole mechanism. Creating an index invisible works the other way round: build it, test it from one session, then make it visible.

Read visibility from the IS_VISIBLE column of INFORMATION_SCHEMA.STATISTICS or the Visible column of SHOW INDEX.

SELECT INDEX_NAME, COLUMN_NAME, NON_UNIQUE, IS_VISIBLE
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'shop' AND TABLE_NAME = 'orders'
ORDER BY INDEX_NAME, SEQ_IN_INDEX;

Interpretation: IS_VISIBLE = NO marks a hidden index. Visibility replicates like other DDL, so a mismatch between servers means someone changed one by hand.

Which indexes cannot be made invisible?

An explicit primary key cannot be invisible. Neither can an implicit one. If a table has no explicit primary key but has a UNIQUE index on NOT NULL columns, the first such index acts as the primary key, and the manual shows the error you get:

mysql> ALTER TABLE t2 ALTER INDEX j_idx INVISIBLE;
ERROR 3522 (HY000): A primary key index cannot be invisible.

Interpretation: error 3522 (ER_PK_INDEX_CANT_BE_INVISIBLE) means the index is doing a primary key's job. Once you add an explicit primary key, that unique index stops acting as one and can be hidden.

Is ALTER INDEX INVISIBLE instant, and does it lock the table?

It is fast, but it is not an INSTANT operation. The manual calls making an index invisible or visible a "fast, in-place" operation, while dropping and re-adding an index "can be expensive for a large table". The 8.4 ALTER TABLE reference lists index visibility under the INPLACE algorithm and says it "cannot be performed using ALGORITHM=INSTANT".

-- Rejected: visibility changes do not support INSTANT
ALTER TABLE shop.orders ALTER INDEX idx_orders_status INVISIBLE, ALGORITHM=INSTANT;
-- ERROR 1845 (0A000): ALGORITHM=INSTANT is not supported for this operation. Try ...

-- Accepted: leave the algorithm out, or name INPLACE
ALTER TABLE shop.orders ALTER INDEX idx_orders_status INVISIBLE, ALGORITHM=INPLACE;

Interpretation: error 1845 is ER_ALTER_OPERATION_NOT_SUPPORTED, with the template "%s is not supported for this operation. Try %s." Do not copy an ALGORITHM=INSTANT clause from a column-change script.

Fast does not mean lock-free. Per Online DDL performance and concurrency, the metadata lock is upgraded to exclusive at commit. An open transaction that has touched the table makes the ALTER wait, and "a pending exclusive metadata lock requested by an online DDL operation blocks subsequent transactions on the table". A one-second change can stall every query on orders.

-- 1. Look for old transactions first
SELECT trx_mysql_thread_id, trx_started,
       TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS age_seconds
FROM information_schema.innodb_trx
ORDER BY trx_started
LIMIT 5;

-- 2. Give up quickly instead of queueing the table
SET SESSION lock_wait_timeout = 5;
ALTER TABLE shop.orders ALTER INDEX idx_orders_status INVISIBLE;

Interpretation: lock_wait_timeout applies to metadata locks, and its default is 31536000 seconds (one year), per the system variable reference. With 5 seconds, a blocked change fails with a lock wait timeout and you retry later, instead of holding up the table behind it.

How do I find unused indexes in MySQL?

Start with sys.schema_unused_indexes. The view's manual page says it lists "indexes for which there are no events", and adds that it is "most useful when the server has been up and processing long enough that its workload is representative". Check uptime in the same session.

The view is a thin filter over a Performance Schema table. The view definition in the MySQL source selects rows from performance_schema.table_io_waits_summary_by_index_usage where count_star = 0, skips the mysql schema and PRIMARY, and joins INFORMATION_SCHEMA.STATISTICS with NON_UNIQUE = 1. Unique indexes never appear in it. Query the base table when you want the numbers, not just the zeros:

SELECT object_name, index_name, count_read, count_fetch, count_write
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE object_schema = 'shop'
  AND index_name IS NOT NULL
  AND index_name <> 'PRIMARY'
ORDER BY count_read, object_name;

Interpretation: per the table I/O summary reference, INDEX_NAME = NULL means table I/O that used no index, and inserts are counted against NULL. An index with a small but nonzero count_read is used rarely, not never, and that rare use may be the one report that needs it.

Duplicates are easier candidates than unused indexes. sys.schema_redundant_indexes lists each index that another index makes redundant, the dominant index, and a ready sql_drop_index statement.

SELECT table_name, redundant_index_name, redundant_index_columns,
       dominant_index_name, dominant_index_columns
FROM sys.schema_redundant_indexes
WHERE table_schema = 'shop';

Interpretation: an index on (customer_id) beside one on (customer_id, created_at) is a typical row. Still take it through the invisible step.

Can you trust sys.schema_unused_indexes?

Only for the server you ran it on, for the time since the counters last started. Four things reset or hide usage:

• Restarts. The Performance Schema overview says its tables are "in-memory tables that use no persistent on-disk storage", discarded at shutdown. A patch restart or instance resize starts the count again.
• Truncation and DDL. TRUNCATE TABLE on the summary table, or on table_io_waits_summary_by_table, zeroes the counters. The manual also says "a DDL operation that changes the index structure of a table may cause the per-index statistics to be reset".
• Instrumentation. If the wait/io/table/sql/handler instrument is off, or setup_objects excludes the schema, nothing is counted. The default setup_objects rows instrument every table except those in mysql, performance_schema, and INFORMATION_SCHEMA.
• Calendar. Month-end and quarterly jobs show up only after they have run.

SELECT @@performance_schema;

SELECT NAME, ENABLED, TIMED
FROM performance_schema.setup_instruments
WHERE NAME = 'wait/io/table/sql/handler';

SELECT OBJECT_SCHEMA, OBJECT_NAME, ENABLED
FROM performance_schema.setup_objects
WHERE OBJECT_TYPE = 'TABLE';

Interpretation: you want 1, ENABLED = YES, and no row that disables your schema. Otherwise a zero means "not measured", not "not used".

Because counters die with the process, copy them out on a schedule, reading each server and writing elsewhere.

#!/usr/bin/env bash
# Daily snapshot of index read counts from every server
for h in db-primary db-replica-1 db-replica-2; do
  mysql -h "$h" -N -B -e "
    SELECT @@hostname, NOW(), object_schema, object_name,
           index_name, count_read
    FROM performance_schema.table_io_waits_summary_by_index_usage
    WHERE object_schema = 'shop' AND index_name IS NOT NULL" \
  >> "index_usage_$(date +%F).tsv"
done

Interpretation: a falling count_read between files means a restart or reset. An index is a candidate only if its count stayed flat on every host for a full business cycle.

Why does index usage differ between the primary and replicas?

Each server counts only the queries it executed. With ProxySQL, MySQL Router, or read/write splitting in the application, reports and exports often run on replicas. The primary's sys.schema_unused_indexes may list exactly the index a replica's dashboard depends on.

The visibility change itself replicates. The binary log format reference says DDL such as ALTER TABLE is logged in statement format even under row-based logging, so ALTER INDEX ... INVISIBLE on the primary hides the index on every replica, and the test covers the replica workload too.

Before the observation window starts, run the IS_VISIBLE query from the first section on each replica. Every server should report NO; if one does not, check replication lag first.

How do I check query plans before and after hiding an index?

Capture plans before the change, so you have something to compare against. You can preview the plan without the index on the live server with an IGNORE INDEX hint, which affects only that statement.

-- Current plan
EXPLAIN FORMAT=TREE
SELECT id, total FROM shop.orders
WHERE status = 'pending' ORDER BY created_at LIMIT 50;

-- Preview: the same query as if idx_orders_status were gone
EXPLAIN FORMAT=TREE
SELECT id, total FROM shop.orders IGNORE INDEX (idx_orders_status)
WHERE status = 'pending' ORDER BY created_at LIMIT 50;

Interpretation: if the second plan turns an index lookup into a Table scan with a sort, this query depends on the index. Repeat for the top queries on the table.

Which queries? Take them from the statement digest summary, which keeps per-query-shape totals since the counters started (see statement summary tables).

SELECT LEFT(DIGEST_TEXT, 90) AS query_shape,
       COUNT_STAR, SUM_NO_INDEX_USED,
       ROUND(SUM_TIMER_WAIT / 1e12, 1) AS total_seconds
FROM performance_schema.events_statements_summary_by_digest
WHERE SCHEMA_NAME = 'shop' AND DIGEST_TEXT LIKE '%`orders`%'
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 15;

Interpretation: timers are in picoseconds, hence the division. Save this before the change. Afterwards, a query shape whose SUM_NO_INDEX_USED or total time climbs is the one that needed the index.

Once the index is invisible, you can still ask what the plan would be with it, for one statement only, with a SET_VAR optimizer hint. This is the manual's own technique, from the optimizer_switch reference, where use_invisible_indexes defaults to off.

-- With the invisible index taken into account (this statement only)
EXPLAIN SELECT /*+ SET_VAR(optimizer_switch = 'use_invisible_indexes=on') */
       id, total FROM shop.orders
WHERE status = 'pending' ORDER BY created_at LIMIT 50;

-- What production now gets
EXPLAIN SELECT id, total FROM shop.orders
WHERE status = 'pending' ORDER BY created_at LIMIT 50;

Interpretation: compare the key, type, and rows columns. In the manual's example, the first form shows type: range on the hidden index, and the second falls back to type: ALL, a full scan.

The same switch works for a whole session with SET SESSION optimizer_switch = 'use_invisible_indexes=on'. That can carry one regressed batch job through the window, but a job that needs the index means the index is not a drop candidate.

The manual lists the other signals that a hidden index was needed: errors for queries whose index hints name it, more workload in Performance Schema data, changed EXPLAIN plans, and new slow query log entries. Search the code for hints before you start:

grep -rniE "(use|force|ignore) (index|key) *\(" ./src | grep -i "idx_orders_status"

Interpretation: any hit is a query that will fail, not just slow down, once the index is invisible.

What is a safe workflow to drop a MySQL index?

Every step before the last can be reversed with one statement.

A full business cycle means every scheduled job that touches the table has run at least once: one month-end at minimum, longer if quarterly reports query it. Waiting costs only the write overhead you already pay.

How do I drop the index once it is proven unused?

Per the index operations table in Online DDL operations, dropping an index runs in place, permits concurrent DML, and only modifies metadata. Naming the algorithm and lock level makes MySQL refuse rather than fall back to something heavier.

-- Save the definition first; it is your only rollback after the drop
SHOW CREATE TABLE shop.orders\G

SET SESSION lock_wait_timeout = 5;
ALTER TABLE shop.orders
  DROP INDEX idx_orders_status,
  ALGORITHM=INPLACE, LOCK=NONE;

Interpretation: the same metadata lock rules apply, on the primary and again on each replica as the statement replicates.

If a foreign key needs the index, the drop fails. The MySQL 8.4 error reference lists error 1553, ER_DROP_INDEX_FK, "Cannot drop index '%s': needed in a foreign key constraint", raised when you drop the last index that can enforce a constraint. The invisible test does not reveal this, because it only shows what the optimizer does. Check before you schedule the drop:

SELECT CONSTRAINT_NAME, TABLE_NAME, COLUMN_NAME,
       REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = 'shop'
  AND (TABLE_NAME = 'orders' OR REFERENCED_TABLE_NAME = 'orders')
  AND REFERENCED_TABLE_NAME IS NOT NULL;

Interpretation: if a constraint's columns lead the candidate index and no other index starts with them, the foreign key needs that index. Keep it.

Unique indexes need the same care. Dropping one removes a data rule the invisible test never exercised, so treat it as a schema change the application owner signs off, not as cleanup.

Frequently asked questions

Key takeaways

Clean up MySQL indexes with JusDB

JusDB's MySQL performance tuning team can review index usage across your primary and replicas, separate unused indexes from rarely used ones, and test plans on a staging replica before any index is hidden or dropped.

Bring your schema, read routing setup, and scheduled jobs. Your team approves every production change.

Related reading

• MySQL performance tuning guide
• MySQL Performance Schema: queries, locks, and index usage
• How to read MySQL EXPLAIN plans
• MySQL 8.4 Reference Manual: Invisible Indexes