Inside MySQL: How Queries Are Processed from Client to Execution

Introduction

MySQL is a daily workhorse for developers, serving as the primary source for data storage and retrieval. Understanding what happens after a client sends an SQL statement is essential. This article explores the entire MySQL execution process, from the initial connection to the final result.

MySQL Execution Process

Connector

The connector handles half‑duplex communication with the client, validates the username and password, and checks the user’s privileges using MySQL’s internal privilege tables.

MySQL maintains four privilege tables:

user – stores global user accounts and privileges.

db – stores database‑level privileges.

tables_priv – stores table‑level privileges.

columns_priv – stores column‑level privileges.

Privilege verification proceeds as follows:

Check Host, User, and Password fields in the user table; if they match, authentication succeeds.

After authentication, MySQL checks privileges in the order user → db → tables_priv → columns_priv. If a global privilege is granted (Y), lower‑level checks are skipped; otherwise, the process continues down the hierarchy.

If any check fails, an error is returned.

Cache

MySQL’s query cache (removed in 8.0) stored SQL statements as keys and result sets as values to speed up repeated queries. Because the cache invalidated frequently in write‑heavy workloads, it was disabled by default in 5.6 and removed later. Modern practice recommends client‑side caching for better performance.

Parser

The parser analyzes the incoming SQL, performing lexical analysis and building a parse tree. It extracts keywords (SELECT, UPDATE, DELETE, FROM, WHERE, GROUP BY, HAVING, LIMIT, etc.) and validates syntax. Errors such as “ERROR: You have an error in your SQL syntax” are raised at this stage.

Optimizer

If the statement passes parsing, the optimizer generates an execution plan, chooses the best indexes, and may reorder predicates. For example, a query with conditions on columns B, A, and C can be reordered to match the leftmost index rule, improving index usage.

Executor

The executor invokes the storage engine API (e.g., InnoDB or MyISAM) to read or modify data. It records changes in the binary log (only for UPDATE/DELETE/INSERT) and may use a two‑phase commit for durability.

Execution State

Running SHOW FULL PROCESSLIST displays all server threads and their current states, such as “Sending data”, “Sorting result”, or “Waiting for lock”, providing insight into what the server is doing for each client request.

SQL Execution Order

SQL statements are not executed in the order they are written. MySQL follows a fixed logical order, creating temporary intermediate tables at each step:

FROM – Identify source tables.

JOIN ON – Perform joins and produce Cartesian products, then filter with ON conditions.

WHERE – Filter rows based on predicates (no aggregates allowed).

GROUP BY – Group rows, creating a temporary table.

HAVING – Apply aggregate filters to groups.

SELECT – Choose columns (or *), producing another temporary table.

DISTINCT – Remove duplicate rows.

ORDER BY – Sort the result set, which can be resource‑intensive.

LIMIT – Paginate the final result.

Each stage may generate intermediate tables (Temp1, Temp2, …) that feed into the next stage. MySQL may also apply internal optimizations that alter this flow for efficiency.

Conclusion

Understanding the MySQL execution pipeline and the logical order of SQL processing helps developers diagnose performance issues, write more efficient queries, and gain deeper insight into how their statements travel from the client to the storage engine.