Query Optimization Techniques for MySQL

Using EXPLAIN

EXPLAIN SELECT * FROM orders WHERE customer_id = 42 AND status = 'active';

Key columns to check:

• type — aim for "ref" or "range", avoid "ALL" (full table scan)
• rows — estimated rows examined (lower is better)
• Extra — "Using index" is good, "Using filesort" or "Using temporary" are warnings

Index Strategies

# Single column index
CREATE INDEX idx_customer ON orders(customer_id);

# Composite index (order matters!)
CREATE INDEX idx_customer_status ON orders(customer_id, status);

# Covering index (includes all queried columns)
CREATE INDEX idx_covering ON orders(customer_id, status, total, created_at);

Common Anti-Patterns

# BAD: Function on indexed column prevents index usage
SELECT * FROM users WHERE YEAR(created_at) = 2026;

# GOOD: Use range instead
SELECT * FROM users WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01';

# BAD: Leading wildcard
SELECT * FROM products WHERE name LIKE '%widget%';

# GOOD: Trailing wildcard uses index
SELECT * FROM products WHERE name LIKE 'widget%';

Query Profiling

# Enable profiling
SET profiling = 1;

# Run your query
SELECT ...;

# View profile
SHOW PROFILES;
SHOW PROFILE FOR QUERY 1;