MySQL Index Debugging Guide

Use evidence before adding another index. Last updated September 1, 2026.

Debug MySQL index usage with EXPLAIN, rows examined, composite index order and slow query evidence. This Formalint reference is written for working developers, DBAs and support engineers who need a repeatable debugging path instead of a one-line snippet with no context.

Use the notes below as a practical review order: understand the input, capture evidence, make one small change and verify the result before moving to the next assumption.

When to use this page

Use evidence before adding another index. It is most useful when a small validation or debugging mistake can create noisy tickets, misleading logs or hard-to-review production changes.

Practical workflow

Step	What to confirm
Read EXPLAIN	Check possible_keys, key, rows and filtered before changing schema.
Match index order	Composite indexes work best when their leftmost columns match common filters.
Measure write cost	Each new index can slow inserts and updates.

Index evidence commands

EXPLAIN SELECT *
FROM orders
WHERE customer_id = 42
  AND created_at >= '2026-09-01';

SHOW INDEX FROM orders;

Review checklist

Format the query before reading the plan.
Compare estimated rows with actual business expectations.
Check selectivity of leading index columns.
Avoid duplicate or nearly duplicate indexes.
Test on production-like data volume before celebrating.

Common mistake

Adding indexes without reading query shape can create maintenance cost without improving the slow endpoint.

Formalint is strongest when the page helps the developer decide what the tool cannot prove. Treat every formatter, regex and command as one layer of evidence, not the whole truth.

Frequently asked questions

Why is MySQL not using my index?

The optimizer may estimate that another path is cheaper, or the index order does not match the filter.

Should every foreign key have an index?

Often yes for joins and deletes, but verify workload and schema rules.

Related Formalint references

Continue with MySQL DBA Checklist, MySQL Slow Query Debugging, Sql Join Debugging Guide.