MENU
Query Optimization and Performance Tuning
Query optimization and performance tuning is the practice of making MySQL answer the same question with less work: fewer rows examined, less disk I/O, less memory churn, and less time spent waiting on locks. It spans reading execution plans, designing indexes that the optimizer actually chooses, rewriting queries into forms the optimizer can reason about, tuning join strategy, sizing InnoDB's caches, and configuring server variables to match the workload. This chapter is new reference material (not part of the original book) written for MySQL 8.4 LTS and MySQL 9.x.This chapter is organized into the following topics:
- EXPLAIN and Execution Plans – EXPLAIN, EXPLAIN ANALYZE, and EXPLAIN FORMAT=JSON; reading access type, key, rows, and the Extra column.
- Index Strategy – composite index column order, covering indexes, cardinality and selectivity, why the optimizer skips an index, functional and generated-column indexes.
- Query Rewriting – avoiding SELECT *, writing sargable predicates, EXISTS vs IN vs JOIN, and LIMIT/OFFSET pitfalls fixed with keyset pagination.
- JOIN Optimization – join order, nested-loop vs hash join, driving/driven tables, join_buffer_size, and STRAIGHT_JOIN.
- InnoDB Buffer Pool and Caching – sizing innodb_buffer_pool_size, buffer pool instances, the removed query cache, and warm-up strategies.
- Performance Schema and Sys Schema – finding slow and expensive queries with performance_schema, the sys schema, and the slow query log.
- Common Anti-Patterns – N+1 queries, unindexed foreign keys, implicit type conversion, over-indexing, and huge IN() lists.
- Server Variable Tuning – log and flush settings, max_connections, temporary table sizing, sort buffers, and the thread cache.
Read this chapter after Query Optimizer and Indexes in earlier chapters – those cover what the optimizer is and how indexes are declared; this chapter covers how to diagnose and fix a slow query in a running system.