Master MySQL Query Performance: A Step‑by‑Step SQL Optimization Guide

SQL Optimization Overview

MySQL processes a query through lexical, syntactic, semantic, logical, and physical stages before execution. DBAs can influence the logical stage (rewriting queries to match optimizer rules) and, to a lesser extent, the physical stage (by providing appropriate indexes and statistics). The goal is to reduce the number of rows and columns scanned, thereby improving response time.

1. Examine Table Structures

Confirm that every table uses the InnoDB storage engine. Since MySQL 5.5, most storage‑level optimizations target InnoDB.

If a table is a temporary table, verify that the temporary‑table parameters (e.g., tmp_table_size, max_heap_table_size) are reasonable, or replace it with a disk‑based table when appropriate.

Review column definitions for inappropriate data types (e.g., using VARCHAR(255) for a column that only stores a few distinct values).

Check row counts; very large tables may benefit from partitioning, sharding, or logical splitting.

Make sure table statistics are up‑to‑date. Run ANALYZE TABLE <tbl>; after major data changes.

Analyze index design. Typical situations are:

No indexes at all, not even a primary key.

Primary or unique index exists, but no secondary indexes.

Primary/unique index plus well‑chosen secondary indexes.

Primary/unique index plus secondary indexes with poor selectivity (e.g., low cardinality columns).

2. Views

Identify the view algorithm: TEMPTABLE (materialized as a temporary table) or MERGE (merged into the outer query). Test both to see which yields a better execution plan.

Simplify complex view logic or move heavy calculations to the application layer to avoid unnecessary processing inside the database.

If a view is not essential, replace it with direct queries on the base tables so the optimizer has full freedom to reorder joins.

3. Join Keys

When join keys are primary‑key/foreign‑key pairs, add additional filter predicates to limit the rows scanned on the parent side.

If join keys are non‑unique, redesign the schema to use unique keys or add surrogate keys that guarantee uniqueness.

Ensure that the data types, collations, and character sets of the join columns match; otherwise, implicit conversions can prevent index usage.

4. Analyze the Query Itself

If the query is already simple (e.g., a straight join of many tables), performance problems often stem from the sheer number of joins. Consider breaking the workload into smaller, staged queries or using temporary tables.

If the query is complex, decompose it into smaller parts: replace sub‑queries with joins, push predicates down, eliminate unnecessary aggregations, and avoid SELECT *.

5. Evaluate the Execution Plan

Run EXPLAIN [FORMAT=JSON] <query>; and verify that the optimizer chooses the most selective index for each table.

If the plan does not use an appropriate index, create one (e.g., CREATE INDEX idx_name ON tbl(col1, col2);) and re‑run the query to confirm improvement.

If the plan uses a suitable index but performance remains poor, consider the following causes:

Index selectivity varies with filter values (e.g., a date filter that returns 100 rows vs. 10,000 rows). In such cases, statistics may need to be refreshed or a composite index added.

The underlying table is extremely large, causing even indexed scans to process many rows. Apply partitioning, sharding, or business‑level data limits to reduce the scanned data volume.

Following these systematic steps—checking storage engine, statistics, indexes, view algorithms, join key design, query structure, and execution plan—provides a solid foundation for optimizing MySQL statements. Subsequent articles can dive deeper into each technique with concrete examples and performance benchmarks.