MySQL Indexing Best Practices: From Beginner to Expert

The Index Myth

Everyone knows indexes speed up queries. Far fewer understand why, which leads to indexes that don't help and sometimes actively hurt performance. Let's fix that.

How MySQL B-Tree Indexes Actually Work

InnoDB (the default MySQL engine) stores data in a clustered index—the primary key IS the data. Every row in every secondary index stores the primary key value to look up the actual row. This means two things:

• Choose short, stable primary keys (INT, not VARCHAR(255))
• Secondary index lookups on large tables always cost a PK lookup too—unless it's a covering index

The Left-Prefix Rule Is Everything

Composite index on (last_name, first_name, email):

• ✅ WHERE last_name = 'Smith' — uses index
• ✅ WHERE last_name = 'Smith' AND first_name = 'John' — uses index
• ❌ WHERE first_name = 'John' — does NOT use index
• ❌ WHERE email = 'john@example.com' — does NOT use index

The optimizer can use a prefix of your composite index, never a suffix. Design indexes around your most common query patterns.

Covering Indexes: The Performance Multiplier

A covering index contains every column your query needs—it never touches the actual table rows. This is the single biggest performance win available in MySQL:

-- Query:
SELECT order_id, total_amount FROM orders
WHERE customer_id = 123 AND status = 'pending';

-- Covering index:
CREATE INDEX idx_orders_covering
  ON orders (customer_id, status, order_id, total_amount);

Check for "Using index" in EXPLAIN output—that means the index is covering.

EXPLAIN Is Your Best Friend

Always run EXPLAIN before and after index changes:

EXPLAIN SELECT * FROM orders WHERE customer_id = 123;
EXPLAIN FORMAT=JSON SELECT * FROM orders WHERE customer_id = 123;

Key columns to watch: type (want: ref, range, index, const — avoid: ALL), rows (lower is better), Extra (watch for "Using filesort" and "Using temporary").

When NOT to Add Indexes

Indexes cost write performance. Every INSERT, UPDATE, and DELETE must update all affected indexes. Rules:

• Columns with low cardinality (boolean, status with 3 values) rarely benefit from standalone indexes
• Tables under 1,000 rows almost never need more than a PK
• Write-heavy tables: keep indexes minimal, batch reads instead

Index Maintenance in MySQL

Unlike SQL Server, InnoDB uses online DDL for most index operations since MySQL 5.6+. Still, large tables benefit from pt-online-schema-change or gh-ost to avoid locking:

-- Check fragmentation:
SELECT table_name, data_free/1024/1024 AS free_mb
FROM information_schema.tables
WHERE table_schema = 'your_db' AND data_free > 104857600;

-- Optimize (be careful on large tables—this rewrites the table):
OPTIMIZE TABLE orders;

The Index Audit Checklist

Run this quarterly:

1. Check sys.schema_unused_indexes for indexes nobody's using
2. Check sys.schema_redundant_indexes for duplicates
3. Review slow query log for full table scans
4. Verify your top 10 slowest queries have appropriate indexes