Understanding MySQL Query Optimization and Performance Tuning

MySQL Logical Architecture

MySQL consists of three layers: the client layer handling connections and authentication, the server layer that parses, optimizes and caches queries, and the storage‑engine layer responsible for actual data storage and retrieval.

MySQL Query Process

When a query is received, MySQL first checks the query cache, then parses and preprocesses the SQL, generates an execution plan using a cost‑based optimizer, executes the plan via the handler API of the storage engine, and finally returns the result to the client, optionally storing it in the cache.

Client/Server Communication Protocol

The protocol is half‑duplex; the client sends a single packet containing the query, and the server may need to increase max_allowed_packet for large statements. Responses are sent in one or more packets and should be kept small, avoiding SELECT * and unnecessary LIMIT.

Query Cache

If enabled, MySQL looks for a cached result before parsing. Cache entries are invalidated when any referenced table changes, and cache maintenance incurs overhead on both reads and writes.

Parsing and Preprocessing

The parser builds a syntax tree and validates the statement; preprocessing checks table and column existence.

Query Optimization

The optimizer creates a cost‑based execution plan, using statistics such as row counts and index cardinality. It may reorder joins, push down limits, choose index scans, or apply transformations like MIN/MAX optimization.

Execution Engine

The plan is executed by calling the storage engine’s handler API; each table is represented by a handler instance that provides row access.

Result Return

Rows are streamed to the client in packets; if the query is cacheable, the result is also stored.

Performance Optimization Recommendations

Schema and Data‑Type Design

Prefer small, simple types; use NOT NULL for indexed columns, avoid unnecessary INT(11) width specifications, and choose appropriate types such as BIGINT over DECIMAL when possible.

High‑Performance Indexes

Use B‑Tree (or B+Tree) indexes, avoid redundant or overly many indexes, create covering indexes, respect the left‑most prefix rule, and order index columns by selectivity.

Specific Query Optimizations

COUNT(): use COUNT(*) for row counts; consider approximations or summary tables for large data.

JOINs: MySQL uses nested‑loop joins; index the second table’s join column.

LIMIT pagination: avoid large offsets; use covering indexes or “bookmark” queries (e.g., WHERE id > last_id LIMIT n).

UNION: prefer UNION ALL when duplicate removal is unnecessary.

Conclusion

Understanding the internal execution flow and the cost of each step helps developers apply the right optimization techniques and avoid blind reliance on “rules of thumb”.