Index Advisor and Optimization

Index Advisor and Optimization

Indexes are the most impactful MySQL optimization lever — but too many indexes hurt write performance. This chapter covers scientific index design and management.

Selectivity

SELECT COUNT(DISTINCT col) / COUNT(*) AS selectivity FROM tbl;
-- > 0.1: worth indexing alone; < 0.1: combine with other columns

Composite Index Design

Leftmost prefix rule: put equality columns first, range columns last, ORDER BY columns at the end. High-cardinality columns should come before low-cardinality ones.

Covering Indexes

Include all SELECT columns in the index to avoid table lookups. Extra: Using index in EXPLAIN confirms a covering index is used.

Invisible Indexes (8.0+)

ALTER TABLE t ALTER INDEX idx INVISIBLE;
-- Verify no impact, then: DROP INDEX idx ON t;
-- Emergency rollback: ALTER TABLE t ALTER INDEX idx VISIBLE;

Unused Index Cleanup

-- Find never-accessed indexes
SELECT * FROM sys.schema_unused_indexes WHERE object_schema = 'mydb';
-- Find duplicate indexes
pt-duplicate-key-checker --database=mydb