Understanding MySQL Query Execution and Optimization Techniques

MySQL’s logical architecture consists of three layers: the client layer (handling connections, authentication, and security), the service layer (parsing, analysis, optimization, and built‑in functions), and the storage engine layer (actual data storage and retrieval). Understanding how these layers interact is essential for effective query optimization.

The query process begins when a client sends a request; MySQL first checks the query cache, then parses and preprocesses the SQL, generates an execution plan via the cost‑based optimizer, and finally executes the plan using the storage engine’s handler API. Results are streamed back to the client, optionally cached for future reuse.

Client/Server communication is half‑duplex: only one side can send data at a time, requiring the full packet to be received before the other side can respond. Large queries may need max_allowed_packet adjustments, and servers reject overly large packets.

When the query cache is enabled, MySQL looks for a cached result before parsing. Cached results bypass parsing and execution, but cache hits depend on exact query text, user, and database; many statements (e.g., those using functions, temporary tables, or user variables) are never cached.

Parsing transforms SQL into a syntax tree, which the pre‑processor validates. The optimizer then converts the tree into an execution plan, choosing the lowest‑cost plan based on statistics such as index cardinality and row distribution.

Indexes in MySQL are primarily B+Tree structures. Each node fits a disk page, reducing I/O to O(log_M N). Leaf pages store the actual row pointers, while internal pages store key ranges. B+Tree’s multi‑way branching and linked leaf pages enable efficient range scans and ordered retrieval.

Key index design guidelines include:

Prefer small, simple data types (e.g., INT over VARCHAR for IP addresses).

Use NOT NULL for indexed columns when possible.

Avoid unnecessary DECIMAL; BIGINT can often replace it.

Choose column order based on selectivity (most selective first).

Use prefix indexes for long string columns.

Combine related columns into a single multi‑column index rather than many single‑column indexes.

Beware of redundant or duplicate indexes.

Remove long‑unused indexes regularly.

Covering indexes (indexes that contain all columns needed by a query) eliminate the need for table lookups, dramatically improving performance. When possible, let the index provide the required ordering to avoid extra sorting steps.

For pagination, avoid large OFFSET values; instead use a “bookmark” technique such as SELECT id FROM t WHERE id > last_id LIMIT 10 or a delayed join:

SELECT f.film_id, f.description FROM film INNER JOIN (SELECT film_id FROM film ORDER BY title LIMIT 50,5) AS tmp USING (film_id);

UNION queries should use UNION ALL when duplicate elimination is not needed, and push down WHERE/LIMIT/ORDER BY clauses into each sub‑query to enable index usage.

Finally, the article emphasizes that understanding the execution flow, cost model, and index mechanics allows developers to test hypotheses with EXPLAIN, choose appropriate indexes, and avoid blind reliance on “absolute truths” about optimization.