Zero-Risk MySQL Index Cleanup (Invisible Index & EXPLAIN ANALYZE)

Supanote Core Team @supanote · 2 min read

plain (sql) 1 day ago · 47 lines · 2 views

Artifact

Verified Blueprint MySQL 8.4 LTS

Drop unused or redundant MySQL indexes safely without downtime using sys schema audits, ALTER INDEX INVISIBLE, and EXPLAIN ANALYZE.

✓ Detects redundant prefix indexes and zero-read write-amplifying indexes via MySQL sys views
✓ Hides candidate indexes metadata-instantly (ALTER INDEX INVISIBLE) so rollback takes 1 millisecond
✓ Validates real iterator execution times and row estimates with EXPLAIN ANALYZE before online DROP

Quick Fetch

-- 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;

1-- Zero-Risk MySQL 8.4 / 9.x Unused Index Cleanup Workflow

2-- Avoids multi-hour index rebuilds if a seemingly unused index is needed by a monthly job

4-- Step 1: List Unused & Redundant Indexes (Ensure server uptime >= 7 days)

5SELECT

6 object_schema AS db_name,

7 object_name AS table_name,

8 index_name,

9 format('ALTER TABLE %I.%I ALTER INDEX %I INVISIBLE;', object_schema, object_name, index_name) AS step1_hide_sql

10FROM sys.schema_unused_indexes

11WHERE object_schema NOT IN ('mysql', 'sys', 'performance_schema');

13SELECT

14 table_schema,

15 table_name,

16 redundant_index_name,

17 redundant_index_columns,

18 dominant_index_name,

19 dominant_index_columns,

20 sql_drop_index

21FROM sys.schema_redundant_indexes;

23-- Step 2: Hide Candidate Index Instantly (Metadata-Only Operation, Zero Table Lock)

24ALTER TABLE orders ALTER INDEX idx_orders_legacy_status INVISIBLE;

26-- Step 3: Compare Execution Plans With vs Without the Invisible Index in Current Session

27EXPLAIN ANALYZE

28SELECT id, customer_id, total_cents

29FROM orders

30WHERE status = 'processing' AND created_at >= NOW() - INTERVAL 7 DAY

31ORDER BY created_at DESC LIMIT 100;

33-- Toggle visibility ON solely for this session to verify whether latency changes:

34SET SESSION optimizer_switch = 'use_invisible_indexes=on';

35EXPLAIN ANALYZE

36SELECT id, customer_id, total_cents

37FROM orders

38WHERE status = 'processing' AND created_at >= NOW() - INTERVAL 7 DAY

39ORDER BY created_at DESC LIMIT 100;

40SET SESSION optimizer_switch = 'use_invisible_indexes=off';

42-- Step 4A: Emergency Instant Rollback (If p99 latency regressed during soak window)

43-- ALTER TABLE orders ALTER INDEX idx_orders_legacy_status VISIBLE;

45-- Step 4B: Permanent Lock-Free Removal After Safe Soak Window

46ALTER TABLE orders DROP INDEX idx_orders_legacy_status, ALGORITHM=INPLACE, LOCK=NONE;

Replies 0

Reply with note

No replies yet

Every reply is a note. Start a discussion, ask a question, or attach a code snippet.

Reply with a note

Related MySQL 8.4 LTS & Database, Cache & SQL Blueprints

Production-ready configurations and diagnostics from the verified Supanote library.

All Database, Cache & SQL →

CONF MySQL

MySQL 8.0 InnoDB Production Performance Tuning Configuration

Hardened my.cnf parameters for InnoDB buffer pool size, redo log capacity, thread cache, and connection limits.

INI MySQL 8.4 LTS

MySQL 8.4 LTS Production my.cnf (Redo Capacity & NVMe)

Modern MySQL 8.4 LTS and 9.x production my.cnf using dynamic innodb_redo_log_capacity, NVMe IOPS tuning, and parallel replica workers.

SQL MariaDB 11.8 LTS

MariaDB 11.8 LTS Native VECTOR INDEX (HNSW + SIMD) Blueprint

Build plugin-free HNSW semantic vector search in MariaDB 11.8 LTS using native VECTOR(N) columns, SIMD cosine distance, and hybrid SQL.

SQL SQLite 3.50+

SQLite 3.50+ Binary JSONB, STRICT Table & Generated Indexes

Cut SQLite JSON CPU overhead by 50%+ using binary JSONB BLOB storage, STRICT tables, VIRTUAL generated columns, and jsonb_set updates.

Interactive Developer Workbench: .env, Log & API Key Secret Scanner

Detect and redact leaked AWS keys, JWTs, database URIs, private keys, and Bearer tokens before sharing.

Open Secret Scanner →