Intermediate22 min read

Indexes & Optimization

Design indexes, analyze queries with EXPLAIN, and tune MySQL performance.

Indexing Strategies

B-tree indexes (default) accelerate equality and range lookups on indexed columns. Create indexes on foreign keys, columns in WHERE clauses, and columns used in JOIN ON conditions.

Compound indexes support left-prefix matching—index (a, b, c) helps queries filtering on a, or a+b, or a+b+c, but not b alone.

Primary key is a clustered index in InnoDB—choose stable, narrow keys
Too many indexes slow INSERT/UPDATE/DELETE operations
Covering indexes include all SELECT columns, avoiding table lookups

CREATE INDEX idx_orders_user_created ON orders (user_id, created_at DESC);
CREATE UNIQUE INDEX idx_users_email ON users (email);

Query Optimization

Identify slow queries via slow query log or Performance Schema. Look for full table scans, filesorts, and temporary tables in EXPLAIN output.

Rewrite OR conditions on different columns into UNION when each branch can use a separate index. Avoid functions on indexed columns in WHERE (e.g., YEAR(created_at)) that prevent index use.

EXPLAIN ANALYZE
SELECT * FROM orders WHERE user_id = 42 AND status = 'open';

Reading EXPLAIN

EXPLAIN shows access type (ALL = full scan, ref/range = index use), rows examined estimate, and Extra notes like Using index, Using filesort, Using temporary. EXPLAIN ANALYZE (MySQL 8.0.18+) executes the query and shows actual timings.

Compare rows examined to rows returned—large gaps indicate poor selectivity or missing indexes.

type: const or eq_ref is ideal for single-row lookups
Extra: Using index indicates covering index usage
Review Join buffer and hash join metrics in ANALYZE output

Performance Tuning

Size innodb_buffer_pool_size to hold your hot working set—typically 50–70% of RAM on dedicated database servers. Tune max_connections relative to application pool sizes across all app instances.

Use connection pooling in applications; each MySQL connection consumes memory. Read replicas offload read-heavy reporting queries with replication lag awareness.

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';

Maintenance

InnoDB automatically maintains indexes, but stale statistics cause bad plans—run ANALYZE TABLE after large bulk loads. Monitor fragmentation and table size growth over time.

Partition large historical tables by range (month/year) when queries consistently filter on time and archival is needed.