Zero-Risk MySQL Index Cleanup (Invisible Index & EXPLAIN ANALYZE)
-- Zero-Risk MySQL 8.4 / 9.x Unused Index Cleanup Workflow
-- Avoids multi-hour index rebuilds if a seemingly unused index is needed by a monthly job
-- Step 1: List Unused & Redundant Indexes (Ensure server uptime >= 7 days)
SELECT
object_schema AS db_name,
object_name AS table_name,
index_name,
format('ALTER TABLE %I.%I ALTER INDEX %I INVISIBLE;', object_schema, object_name, index_name) AS step1_hide_sql
FROM sys.schema_unused_indexes
WHERE object_schema NOT IN ('mysql', 'sys', 'performance_schema');
SELECT
table_schema,
table_name,
redundant_index_name,
redundant_index_columns,
dominant_index_name,
dominant_index_columns,
sql_drop_index
FROM sys.schema_redundant_indexes;
-- Step 2: Hide Candidate Index Instantly (Metadata-Only Operation, Zero Table Lock)
ALTER TABLE orders ALTER INDEX idx_orders_legacy_status INVISIBLE;
-- Step 3: Compare Execution Plans With vs Without the Invisible Index in Current Session
EXPLAIN ANALYZE
SELECT id, customer_id, total_cents
FROM orders
WHERE status = 'processing' AND created_at >= NOW() - INTERVAL 7 DAY
ORDER BY created_at DESC LIMIT 100;
-- Toggle visibility ON solely for this session to verify whether latency changes:
SET SESSION optimizer_switch = 'use_invisible_indexes=on';
EXPLAIN ANALYZE
SELECT id, customer_id, total_cents
FROM orders
WHERE status = 'processing' AND created_at >= NOW() - INTERVAL 7 DAY
ORDER BY created_at DESC LIMIT 100;
SET SESSION optimizer_switch = 'use_invisible_indexes=off';
-- Step 4A: Emergency Instant Rollback (If p99 latency regressed during soak window)
-- ALTER TABLE orders ALTER INDEX idx_orders_legacy_status VISIBLE;
-- Step 4B: Permanent Lock-Free Removal After Safe Soak Window
ALTER TABLE orders DROP INDEX idx_orders_legacy_status, ALGORITHM=INPLACE, LOCK=NONE;