Master MySQL Query Optimization: From Architecture to High‑Performance Indexing

MySQL Logical Architecture

If you can picture how MySQL's components cooperate, you gain a deeper understanding of the server. The diagram below shows the three‑layer logical architecture: the client layer (handling connections, authentication, security), the service layer (query parsing, analysis, optimization, caching, built‑in functions, stored procedures, triggers, views), and the storage engine layer (actual data storage and retrieval).

MySQL Query Process

Understanding how MySQL processes a query reveals why many optimization tips work. The process consists of six main steps:

Client/Server Communication Protocol – MySQL uses a half‑duplex protocol; a client sends a single packet containing the query, and the server replies with one or more packets. Large queries require increasing max_allowed_packet. Keeping queries simple and limiting result size reduces packet traffic.

Query Cache – If the cache is enabled, MySQL checks whether the exact query (including database, protocol version, etc.) hits the cache. Cached results bypass parsing and execution. Cache invalidates when any referenced table changes; each write operation flushes related cache entries, which can be costly.

Syntax Parsing and Pre‑processing – The SQL text is tokenised and turned into a parse tree, which is validated against syntax rules and checked for existence of tables/columns.

Query Optimization – The optimizer transforms the parse tree into an execution plan, using a cost‑based model. You can view the estimated cost via SELECT @@last_query_cost after SHOW STATUS LIKE 'last_query_cost'.

Query Execution Engine – The plan is executed by calling the storage‑engine handler API. Each table is represented by a handler instance that provides column, index, and statistics information.

Result Return to Client – Rows are streamed back to the client packet by packet. If the query is cacheable, the result is also stored in the cache.

Performance Optimization Recommendations

1. Schema Design & Data‑Type Choices

Avoid unnecessary NULL columns; make them NOT NULL when you plan to index.

Integer width (e.g., INT(11)) does not affect storage; all INT values occupy 4 bytes.

Use UNSIGNED for non‑negative numbers to double the positive range.

Prefer BIGINT over DECIMAL for high‑precision numeric data when possible. TIMESTAMP uses 4 bytes (1970‑2038) while DATETIME uses 8 bytes; choose based on range and timezone needs.

Limit the number of columns; each extra column adds CPU overhead during row buffering.

Large ALTER TABLE operations rebuild the table; consider pt‑online‑schema‑change or similar tools.

2. High‑Performance Index Creation

Indexes speed up lookups but consume disk and memory; avoid adding them without profiling.

Use prefix indexes for long string columns to save space.

Multi‑column indexes should follow the “most selective first” rule.

MySQL cannot use two range conditions simultaneously; choose the column with higher selectivity for the index.

Covering indexes (index contains all columns needed by the query) eliminate the need for a table lookup.

When the index order matches the ORDER BY clause, MySQL can produce sorted results via an index scan (type = index in EXPLAIN).

Remove redundant or duplicate indexes; keep only the most useful one.

Periodically drop indexes that have not been used for a long time.

3. Index Data Structure & B+Tree Mechanics

MySQL primarily uses B+Tree indexes. All keys are stored in leaf pages; internal pages store only pointers. Leaf pages are linked, enabling efficient range scans. Node size equals a disk page (typically 4 KB), allowing a single I/O to load a node. The tree height is usually ≤ 3 for millions of rows, giving O(log_M N) lookup cost.

When a leaf page fills, MySQL splits it; if a sibling leaf has free space, a rotation (left/right) moves entries instead of splitting, reducing I/O.

4. Avoid Multiple Range Conditions

MySQL can use an index on only one range column per query. If you need both login_time and age ranges, consider a composite index that matches the query pattern or rewrite the query.

5. Specific Query Optimizations

COUNT() – Use COUNT(*) for row counts; it is faster and clearer than counting a non‑NULL column.

JOINs – Index the columns on the second table of the join order. Ensure ON / USING columns are indexed and that GROUP BY / ORDER BY involve only one table's columns.

LIMIT Pagination – Large offsets cause MySQL to read and discard many rows. Use “keyset pagination” (e.g., WHERE id > last_id ORDER BY id LIMIT 20) or a covering index to fetch only needed rows.

UNION – Prefer UNION ALL unless you need duplicate elimination. Push WHERE, LIMIT, and ORDER BY into each sub‑query to let the optimizer use indexes.

Conclusion

Understanding the internal stages of MySQL query execution, the cost model, and the underlying B+Tree index structure enables you to evaluate whether a suggested optimization truly helps in your workload. Apply the principles above, test with EXPLAIN and real‑world benchmarks, and remember that for very small tables a full scan may be faster, while for massive tables you might need partitioning or external summarisation.

mysql> SELECT * FROM t_message LIMIT 10;

mysql> SHOW STATUS LIKE 'last_query_cost';
+-----------------+-------------+
| Variable_name   | Value       |
+-----------------+-------------+
| Last_query_cost | 6391.799000 |
+-----------------+-------------+