MySQL Optimization

This deck walks you through the fundamentals of MySQL performance tuning, starting with the mindset behind optimization and moving into practical tools like the EXPLAIN command. You'll explore how to read execution plans, understand join types, and interpret what MySQL is doing under the hood when it processes a query. The cards also cover indexes in depth, including how composite indexes work and the leftmost prefix rule that governs their effective use.

The material is well suited for backend developers, database administrators, students learning about relational databases, or anyone preparing for interviews that touch on SQL performance. If you've ever written queries that felt slow but weren't sure where to start looking, these flashcards will give you a structured way to think about diagnosing and fixing the problem.

Because the concepts build on each other, try working through the cards in order on your first pass, then use spaced repetition to revisit the trickier ideas like EXPLAIN output columns and index tradeoffs. A helpful habit while studying is to open a local MySQL instance and run EXPLAIN on your own queries as you encounter each card, turning the flashcards into a hands-on learning session rather than passive memorization.

The primary goal of MySQL optimization is to reduce query response times and resource consumption while improving the database's ability to scale with growing workload. Optimization is not a single activity but a practice that spans several interrelated areas: schema design, indexing strategy, query construction, server configuration, hardware provisioning, and ongoing monitoring. Because these areas interact, a change in one can affect the others—for example, adding an index speeds up reads but slows down writes—so optimization is best approached holistically rather than as a series of isolated fixes.

Before making any change, it is essential to measure current performance to establish a baseline. Without a baseline, you cannot tell whether an optimization actually helps, and you risk spending effort on the wrong parts of the system. A sound general workflow is to measure first, change one thing at a time, verify the impact with metrics, and iterate. This discipline matters even more in production, where regressions can hurt users immediately. Performance work should also be tied to business metrics such as user-facing latency and error rates, so that effort is focused where it delivers real value rather than where it simply improves abstract benchmarks.

A data-driven mindset should guide every optimization decision. Synthetic benchmarks can be misleading because they rarely match production workloads in data volume, distribution, or contention patterns, and optimizing only micro-benchmarks of single queries can mask bigger bottlenecks. Realistic test data, ideally drawn from production shapes, is necessary for trustworthy results. Equally important is the principle of simplicity: simple schemas and queries are easier to reason about, easier to roll back, and often faster in practice. Performance must also be evaluated under concurrency, not just single-user load, because contention and resource saturation often only appear when many users run queries simultaneously. Finally, every optimization should be deployed safely—tested in staging, rolled out gradually through feature flags, and monitored for both throughput and latency to ensure that improvements in one area do not silently degrade another.

The EXPLAIN command is the primary tool for inspecting how MySQL plans to execute a SELECT query. Its output reveals the table access order, the join type used for each table, which indexes (if any) the optimizer chose, and the estimated number of rows examined at each step. By comparing plans before and after a change, you can verify whether a new index is actually being used, whether a rewrite improved selectivity, or whether a configuration change altered join ordering. In MySQL 8, EXPLAIN ANALYZE goes further by actually executing the query and reporting real runtime statistics such as actual row counts and timing, which makes it far more useful than estimates alone for diagnosing real performance problems.

Several columns in EXPLAIN output deserve careful attention. The type column shows how MySQL accesses rows: values such as system, const, eq_ref, ref, range, index, and ALL indicate progressively less efficient access, with ALL representing a full table scan—the worst case for performance. The rows column reports the optimizer's estimate of how many rows must be examined at each step, so large numbers there often point to missing or poorly chosen indexes. The Extra column can also reveal inefficiencies: Using filesort indicates that MySQL cannot use an index for ordering, and Using temporary signals that an intermediate result was stored in a temporary table. GUI tools such as MySQL Workbench and EXPLAIN visualizers can render these plans more readably as join trees.

Beyond EXPLAIN, MySQL provides diagnostic data that complements plan inspection. High values of Handler_read_rnd_next, visible via SHOW STATUS LIKE 'Handler_read%', suggest excessive full scans, while steady growth in Created_tmp_disk_tables indicates that queries are spilling intermediate results to disk. The optimizer trace feature produces a detailed JSON record of the decisions the optimizer considered, which can be invaluable when a plan looks wrong but the cause is not obvious; you enable it by setting optimizer_trace within a session, running the query, then reading information_schema.OPTIMIZER_TRACE. While the trace output can be large for complex queries, it shows exactly which indexes, join orders, and strategies the optimizer rejected. Tools like the sys schema offer curated views over performance_schema and information_schema, making it easier to spot hotspots, top queries, and inefficient access patterns without manually sifting through raw metrics.

An index is a data structure that lets MySQL locate rows quickly based on column values, accelerating lookups, range filters, joins, and sorted access. By narrowing the rows MySQL must examine, an index can transform a multi-second scan into a millisecond lookup. However, indexes are not free: every insert, update, and delete must maintain them, and each index consumes storage. As a result, an over-indexed table pays a recurring tax on writes, and a long-term indexing strategy must balance read speed against write cost and storage overhead. Small tables are often cheaper to scan than to maintain multiple indexes, so over-indexing very small tables adds write overhead without real read benefit.

Composite indexes—those spanning multiple columns—follow a leftmost prefix rule: MySQL can use an index on (a, b, c) to filter or sort on (a), (a, b), or (a, b, c) in that order, but it cannot skip columns in the middle. This makes the column order of a composite index critical, and queries that filter on later columns without the earlier ones will not benefit. A particularly powerful technique is the covering index, which includes every column the query needs; when this happens, MySQL can satisfy the query entirely from index pages without touching table rows, dramatically reducing I/O. For example, a query filtering by user_id and selecting user_id and created_at can be served from a single index on (user_id, created_at). Cardinality—the number of distinct values—also matters: highly selective indexes quickly narrow down to few rows, while low-selectivity indexes such as a boolean flag on its own may be ignored by the optimizer in favor of a table scan and only become useful when combined with more selective columns in a composite key. NULLable columns can reduce index selectivity and complicate cardinality estimates, so they should be avoided in composite indexes where possible.

Index hints such as USE INDEX or FORCE INDEX exist to steer the optimizer, and the optimizer_switch setting controls various optimizer behaviors, but hints should be used sparingly because they can lock in suboptimal plans if data distributions change. Invisible indexes offer a safer alternative: the index is still maintained but ignored by the optimizer unless explicitly referenced, allowing you to simulate dropping an index without losing it. Index management is an ongoing practice. Reactive, query-by-query index additions tend to accumulate redundant or overlapping indexes that hurt write performance without proportional read benefit. Periodic reviews using performance_schema and sys schema views can reveal indexes that are never used and can be safely dropped, and any new index should be evaluated alongside the WHERE, JOIN, ORDER BY, and GROUP BY clauses of real queries. Indexing foreign key columns is particularly important because unindexed foreign keys cause full scans on parent and child tables during referential checks. Over time, indexes also fragment due to page splits and deletions; operations like OPTIMIZE TABLE or ALTER TABLE ... ENGINE=InnoDB can rebuild them, though such operations should be scheduled carefully because they can lock large tables and require extra space.

Good schema design begins with choosing appropriate data types for each column. Smaller and simpler types consume less storage, fit more rows per page, improve cache hit rates, and compare more efficiently. For identifier columns, numeric types like INT and BIGINT are almost always preferable to VARCHAR because they are fixed-width, faster to compare, and produce smaller, denser indexes. Care should also be taken with NULLable columns in composite indexes, because NULL values can reduce selectivity and complicate cardinality estimates used by the optimizer. Choosing BIGINT for primary keys on busy tables provides a much larger ID space than INT, avoiding emergency migrations when smaller ranges are exhausted; small auto-increment types can run out of values, causing insert failures, while BIGINT gives headroom for very large tables. Auto-increment contention can also appear in high-write workloads when many concurrent inserts fight over the same hot page, and this can be alleviated by sharding or partitioning inserts across multiple tables.

Primary key design has far-reaching consequences. In InnoDB, secondary indexes implicitly include the primary key, so a wide composite primary key inflates every secondary index on the table. A common pattern is to use a narrow surrogate key (such as a BIGINT auto-increment) as the primary key and keep large natural keys as secondary indexed columns. Random keys such as UUIDv4 cause inserts to land at random positions in the B-tree, leading to page splits, fragmentation, and poor cache locality. When UUID-like identifiers are required, ordered schemes such as UUIDv7 or mapping to a sequential integer reduce this hot-spotting. Very large BLOB or TEXT columns similarly bloat rows and indexes; storing only references in main tables and moving the large values to separate tables or external storage keeps hot paths compact and efficient. Timezone handling deserves thought as well: storing timestamps in UTC and converting at the application layer, or via indexed generated columns for common local time views, avoids expensive CONVERT_TZ calls that block index usage on datetime columns.

Schema design also involves tradeoffs between normalization and denormalization. Proper normalization reduces duplication, ensures consistency, and keeps indexes small, which generally helps performance. Denormalization, by contrast, can dramatically speed up read-heavy workloads that would otherwise require expensive joins, at the cost of extra storage and more complex writes that must keep the duplicates in sync. Generated columns—either virtual or stored—offer a middle ground by precomputing expensive expressions or JSON extractions; when stored and indexed, they let queries access derived values as if they were ordinary columns, often without the function-call overhead that would otherwise prevent index use, which is particularly helpful for JSON columns where nested paths are otherwise hard to index. Histograms, created with ANALYZE TABLE ... UPDATE HISTOGRAM ON column, give the optimizer more accurate selectivity estimates for skewed distributions where simple cardinality statistics are misleading. Finally, enabling innodb_file_per_table stores each table in its own tablespace, which avoids shared tablespace bloat and allows per-table operations like shrinking or moving to faster storage, while modern row formats such as DYNAMIC or COMPRESSED keep large off-page values efficient.

The way a query is written often matters more than the configuration surrounding it. SELECT * is a common performance anti-pattern because it reads every column, preventing index-only scans and transferring unnecessary data across the network. Selecting only the columns actually needed allows the query to use a covering index and reduces both I/O and serialization cost. Similarly, applying functions to indexed columns—such as WHERE DATE(created_at) = '...'—prevents MySQL from using a normal index range, because the function must be evaluated against every row; rewriting the predicate as a range like WHERE created_at >= '...' AND created_at < '...' restores index usage. The same logic applies to implicit type conversions: when MySQL converts indexed values to match a parameter, the index becomes unusable. Keeping parameter types aligned with column types—and casting in the application rather than in SQL—avoids this trap. Heavy string manipulation in SQL is generally less efficient than doing it in application code, where libraries are usually more capable.

JOINs and subqueries deserve careful construction. INNER JOIN is usually cheaper than LEFT JOIN because it does not retain unmatched rows, so an unnecessary LEFT JOIN can force MySQL to process more data than the application actually needs. The cost of any join can be reduced by indexing join columns, filtering as early as possible with selective WHERE conditions, and minimizing the number of tables involved. Subqueries also need attention: a correlated subquery runs once per outer row, often causing serious slowdown, while a non-correlated subquery runs only once; rewriting a correlated subquery as a JOIN or a derived table is frequently much faster. A classic related problem is the N+1 query pattern, where application code issues many small queries in a loop instead of one set-based query, multiplying round-trip overhead; the fix is to use joins or IN lists that fetch all needed data in fewer, larger queries. SELECT ... FOR UPDATE is similarly expensive in hot code paths because it acquires row locks even for reads, increasing contention; when possible, optimistic concurrency with version columns and conditional UPDATE or INSERT provides a cheaper alternative.

ORDER BY, LIMIT, and pagination introduce their own considerations. To avoid filesort, the columns in ORDER BY should match an index in the same order and direction, and the query should not mix incompatible expressions. Large OFFSET values are surprisingly expensive because MySQL must still scan and discard all preceding rows; keyset pagination, which uses an indexed condition like id > ? based on the last seen value, scales much better for deep paging. LIMIT combined with selective WHERE clauses reduces rows scanned, processed, and sent to the client. Wildcards at the start of LIKE patterns (LIKE '%abc') prevent normal prefix index usage, while full-text search needs dedicated FULLTEXT indexes or external search engines. Window functions require additional sorting or buffering per partition, so limiting partition and ORDER BY scope and supporting them with indexes helps. DISTINCT is often misused to mask duplicate-creating joins; fixing the join logic is preferable to relying on DISTINCT for deduplication. CTEs (WITH clauses) make complex queries clearer but can hurt performance when misused—non-materialized or recursive CTEs may be re-evaluated repeatedly, and large materialized CTEs can create heavy temp tables—so comparing EXPLAIN plans with equivalent derived tables is a good verification step. Prepared statements and parameterized queries amortize parsing and planning costs across many executions and improve plan cache efficiency. Finally, monitoring rows_examined versus rows_sent helps catch queries that scan far more rows than they return, a strong signal of poor selectivity or missing indexes; ORM-generated SQL often produces such patterns, so profiling real queries and rewriting critical paths yields large gains.

Because InnoDB is MySQL's default storage engine, its internals strongly shape overall performance. The InnoDB buffer pool caches data and index pages in memory, so innodb_buffer_pool_size is one of the most important configuration variables: a larger buffer pool allows more of the working set to be served from RAM, drastically reducing disk I/O. On a dedicated database server, allocating roughly 60% to 75% of system RAM to the buffer pool is a common starting point, with the exact value depending on workload and other memory consumers. Splitting the buffer pool into multiple instances via innodb_buffer_pool_instances reduces contention on internal latches for highly concurrent workloads, and enabling innodb_buffer_pool_dump_at_shutdown with innodb_buffer_pool_load_at_startup lets a server warm its cache quickly after restart.

Write performance is governed by several related mechanisms. The redo log records changes to data pages for crash recovery, and innodb_log_file_size affects how often checkpoints occur: larger log files reduce write stalls but lengthen recovery time. The setting innodb_flush_log_at_trx_commit trades durability for performance—value 1 flushes the log to disk on every commit for full ACID safety, while value 2 flushes to the operating system cache at commit and to disk less often, reducing fsync overhead at the cost of potentially losing a second of transactions on a crash. sync_binlog similarly controls how often the binary log is fsynced. The doublewrite buffer adds another layer of safety by writing pages to a special area before their final location, protecting against torn writes. Background IO behavior is tunable through innodb_io_capacity (an honest estimate of the storage's IOPS), innodb_read_io_threads, innodb_write_io_threads, and innodb_read_ahead_threshold; setting innodb_io_capacity too low causes dirty page buildup and stalls, while setting it too high can saturate the storage and create latency spikes. The adaptive hash index, controlled by innodb_adaptive_hash_index, can speed up point lookups on hot B-tree pages but may cause latch contention on some workloads, so disabling it is sometimes beneficial.

Hardware choices and server-level parameters also matter a great deal. Insufficient CPU, RAM, disk IOPS, or network bandwidth can bottleneck even perfectly written queries. Fast SSDs or NVMe devices dramatically reduce fsync latency, which dominates write-bound workloads, and battery-backed write caches can help similar workloads on traditional disks. NUMA architectures can cause latency when threads frequently access remote memory nodes, which can be mitigated by CPU and memory pinning or interleaved allocation policies. Swapping, aggressive memory overcommit, and tight container limits are all dangerous because they introduce unpredictable latency or outright crashes, and a typical sign of IO-bound pressure is high disk utilization and increasing read or write latencies while CPU sits underutilized. Server-side caches such as table_open_cache, table_definition_cache, tmp_table_size, max_heap_table_size, and join_buffer_size improve performance when sized appropriately, but each carries a multiplication factor under high concurrency—many connections each allocating large buffers can quickly exhaust memory, so it is usually better to fix query patterns than to grow global buffers endlessly. Settings like max_connections, wait_timeout, and skip-name-resolve further shape connection behavior, and the long-removed query cache is no longer relied upon: modern systems depend instead on application-level caches and external stores like Redis. Compression via ROW_FORMAT=COMPRESSED or InnoDB page compression can reduce IO and storage but adds CPU cost for compression and decompression, so it tends to help read-heavy, IO-bound workloads where CPU is not the bottleneck. Resource groups in MySQL 8 can assign threads to groups with CPU limits and priorities, helping noisy-neighbor scenarios in multi-tenant or mixed workloads. Finally, keeping MySQL up to date brings optimizer improvements, better defaults, and new features, but major version upgrades should always be tested for plan regressions on production-like data.

InnoDB uses Multi-Version Concurrency Control (MVCC) to allow readers to see a consistent snapshot without blocking writers, which dramatically improves concurrency compared to simple table-level locking. To support this, InnoDB retains old row versions in the undo log until no transaction needs them anymore. Long-running transactions are therefore dangerous: they keep old versions alive, grow the undo log, and prevent purge from reclaiming space. The same transactions also hold locks longer, increasing contention. Keeping autocommit enabled for OLTP workloads keeps individual statements short, reduces lock hold time, and minimizes undo and redo pressure; disabling autocommit and forgetting to commit is one of the most common causes of mysterious performance and stability problems.

Isolation levels control how transactions interact. MySQL's InnoDB default, REPEATABLE READ, prevents dirty reads and non-repeatable reads within a transaction but uses gap locks to prevent phantom reads, which can reduce concurrency by blocking inserts into ranges. Switching to READ COMMITTED reduces gap locking and often improves throughput, at the cost of weaker consistency guarantees. READ UNCOMMITTED allows dirty reads and is rarely appropriate, while SERIALIZABLE is the strictest and the most contention-prone. Higher isolation levels generally increase lock hold times and reduce parallelism, so the choice of level should reflect the application's actual consistency needs rather than defaulting to the strictest option. Read-only transactions declared with START TRANSACTION READ ONLY can sometimes get optimized execution paths and avoid accidental writes.

Pessimistic locking also deserves attention. SELECT ... FOR UPDATE acquires row locks even for reads and can quickly become a hotspot in high-traffic code paths; it is appropriate only when strong consistency is essential for a critical invariant, in which case optimistic concurrency with version columns and conditional UPDATE or INSERT can avoid the lock overhead entirely. Deadlocks occur when transactions hold locks the others need in a cycle, and InnoDB detects and rolls back one of them; their likelihood can be reduced by accessing rows in a consistent order, keeping transactions short, and avoiding unnecessary locking. Metadata locks, which protect table definitions, can also stall DDL when long-running queries hold them; online DDL operations governed by ALGORITHM and LOCK clauses in ALTER TABLE minimize this impact but still require testing on large tables. Frequent or long lock waits indicate contention that can throttle throughput and increase latency, and they can be observed through performance_schema tables such as data_locks and data_lock_waits, along with related sys schema views. Transactions themselves should be kept small and short-lived, because overly large transactions hold locks longer, increase contention, grow the undo and redo logs, and make rollbacks more expensive. Finally, MAX_EXECUTION_TIME provides a safety net by aborting runaway SELECT statements, though it should be tuned carefully to avoid killing legitimate long jobs.

Effective optimization depends on good observability. The slow query log, enabled via slow_query_log and thresholded by long_query_time, captures queries that exceed runtime budgets; log_queries_not_using_indexes can additionally flag queries that scan without index use, and log_slow_admin_statements extends coverage to slow administrative commands, though these options must be used carefully on busy systems to avoid log floods. Analyzing the slow query log regularly identifies the most expensive queries so that effort can be prioritized where it has the biggest impact. performance_schema provides fine-grained instrumentation on waits, locks, IO, and statement statistics, and the sys schema offers curated views on top of it—making it easy to find top queries by digest, hot tables, or IO hotspots. Pairing slow logs with performance_schema data gives both pattern-level and resource-level insight. External slow-log analyzers such as Percona Toolkit's pt-query-digest aggregate and rank queries by time and frequency. Beyond averages, monitoring query latency percentiles such as p95 and p99 reveals tail latencies that averages hide, which is critical because the worst user experiences often dominate perceived performance. Digest-based aggregation (for example, events_statements_summary_by_digest) groups similar queries regardless of literal values, surfacing patterns rather than individual calls, while SHOW PROCESSLIST or events_statements_current help observe current activity.

When a single MySQL instance reaches its limits, scaling strategies come into play. Replication copies changes from a primary to one or more replicas, allowing reads to be offloaded; row-based replication is generally more reliable and predictable than statement-based, and GTID-based replication simplifies failover and topology changes by uniquely identifying transactions across servers. Semisynchronous replication waits for at least one replica to acknowledge a transaction before commit, trading some latency for reduced data loss risk, and parallel replication lets a replica apply independent transactions concurrently, benefiting workloads with transactions spread across different databases or partitions. Group Replication provides a fault-tolerant multi-primary or single-primary cluster with automatic failover, though consensus and conflict checks add coordination overhead that can raise write latency. Read-only replicas are a particularly effective way to isolate heavy analytics from OLTP, but replication lag means that reads on replicas can be stale, and many replicas multiply replication traffic on the primary. Connection pooling, often paired with smart proxies such as ProxySQL or MaxScale, reuses connections and can do read/write splitting, query caching, and failover routing without changes to application code, which is preferable to raising max_connections arbitrarily high. When write capacity is the bottleneck, sharding distributes data across multiple databases or servers, though at the cost of application complexity and the difficulty of cross-shard queries; per-user or per-host resource limits can also protect stability by preventing one tenant from consuming all connections.

Operational practices tie all of this together. Backups—logical via mysqldump or physical via hot backup tools—affect performance during the backup window, so they should be scheduled off-peak, run from replicas, or use incremental methods. Partitioning a large table, especially by RANGE on dates, enables partition pruning so that queries scan only relevant data, while dropping old partitions is much cheaper than deleting rows; the partition key should be included in indexes so pruning can work alongside index access, and over-partitioning must be avoided because too many partitions increase overhead and slow metadata operations. Sliding-window partitioning strategies that expire data by dropping whole partitions are an effective way to manage time-series or log data, and data archiving in general keeps hot tables lean by moving cold rows into archive tables or external storage. Online DDL allows schema changes with minimal locking, but each operation should still be tested, because some algorithms still need long metadata locks or large temporary space. Heavy analytical queries should be isolated from OLTP, either by running them on dedicated replicas or by pre-aggregating results in summary or rollup tables. Triggers add hidden work on every row modification and can become performance hotspots on heavy-write tables, so their logic is sometimes better placed in application or batch code. Heavy batch work should be broken into smaller chunks, use multi-row INSERTs or LOAD DATA INFILE for bulk loads with precautions around secondary indexes and foreign keys, and avoid running during peak traffic. Feature flags, canaries, and gradual rollout limit blast radius and let you monitor impact before full deployment, and circuit breakers and exponential backoff prevent connection storms and cascading failures from overwhelming MySQL. Index reasoning should be documented, expensive queries should be tagged with feature names so they can be correlated with metrics, and quarterly performance reviews help catch drift from new features or data growth. Finally, every change should be tested in a staging environment with before-and-after metrics and a rollback plan, with version upgrades staged carefully and, where possible, new indexes first tried out on replicas.