Why should you care?

Drop wrong index on orders — production slows overnight. Make invisible first, watch slow log, then drop.

See it live — copy this example

Run in MySQL Workbench or the mysql CLI.

ALTER TABLE orders ALTER INDEX idx_orders_customer INVISIBLE;
EXPLAIN SELECT * FROM orders WHERE customer_id = 5;
-- Plan may skip idx_orders_customer
ALTER TABLE orders ALTER INDEX idx_orders_customer VISIBLE;

What happened?

• INVISIBLE hides index from default plans.
• If queries slow, index was still needed — make VISIBLE again.
• Safer than DROP in prod experimentation.

Practice next

1. CREATE or pick existing secondary index.
2. ALTER INDEX INVISIBLE.
3. EXPLAIN key queries — note plan change.
4. SET SESSION use_invisible_indexes = ON to force use for experiments.
5. Query performance_schema for index usage stats.

Remember

Test index removal without DROP. Toggle VISIBLE/INVISIBLE quickly. Monitor slow log during invisible window.

DataFlow index retirement

DBA makes legacy idx invisible during sale week — no regressions → drop later.

Outcome: Write throughput gains without read surprise.