Optimization: MySQL index clean-up and tuning with the sys schema - unused, redundant and full-scan queries

#Unused indexes

SELECT * FROM sys.schema_unused_indexes
WHERE object_schema NOT IN ('mysql', 'sys', 'performance_schema');

Counters reset on restart. Make sure uptime covers a full business cycle:

SHOW GLOBAL STATUS LIKE 'Uptime';

On replicas, an index might be unused on the primary but used by reports on the replica. Check each server.

#Redundant indexes

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;

An index on (a) is redundant when (a, b) exists, unless it's unique or used for a specific constraint.

#Make an index invisible before dropping

ALTER TABLE orders ALTER INDEX ix_orders_status INVISIBLE;
-- watch for a business cycle; if nothing regresses:
ALTER TABLE orders DROP INDEX ix_orders_status;
-- if something regresses:
ALTER TABLE orders ALTER INDEX ix_orders_status VISIBLE;

#Statements doing full table scans

SELECT query, db, exec_count, no_index_used_count, rows_examined_avg
FROM sys.statements_with_full_table_scans
ORDER BY no_index_used_count DESC LIMIT 20;

-- Statements examining far more rows than they return
SELECT digest_text, count_star,
       sum_rows_examined / count_star AS rows_examined_avg,
       sum_rows_sent / count_star AS rows_sent_avg
FROM performance_schema.events_statements_summary_by_digest
WHERE count_star > 100
ORDER BY sum_rows_examined DESC LIMIT 20;

For each, run EXPLAIN ANALYZE (8.0.18+) and design an index that matches the WHERE equality columns first, then range/sort columns.

#Add indexes safely

• Online DDL: ALTER TABLE ... ADD INDEX ..., ALGORITHM=INPLACE, LOCK=NONE;
• For very large tables, consider tools like gh-ost or pt-online-schema-change, or test the replication lag impact of INPLACE.
• Watch replica lag during builds (see the replication lag post).

#Verify

Reset or snapshot digest stats, run for a period, compare latency and rows examined for the targeted statements.

Tip

Every index slows inserts and updates and uses buffer pool memory. Dropping five unused indexes can help a write-heavy table as much as adding the one missing index helps reads.

#Checklist

• Uptime covers a business cycle (on every server)
• Unused and redundant indexes listed
• Invisible before drop
• Full-scan statements analysed with EXPLAIN ANALYZE
• Indexes added online with lag watched
• Digest metrics compared before/after