Understanding MySQL Execution Process and SQL Execution Order

MySQL is a core component in daily development, responsible for storing and retrieving data; this article explains the complete server-side processing of a client SQL request.

Connector handles half‑duplex communication with the client, validates user credentials, and checks permissions using the four MySQL privilege tables (user, db, tables_priv, columns_priv).

Permission tables : user – global user accounts and privileges db – database‑level privileges tables_priv – table‑level privileges columns_priv – column‑level privileges If any verification step fails, MySQL returns an error such as "Access denied for user …".

Cache (query cache) stores SQL statements as keys and result sets as values to speed up repeated queries; it was removed in MySQL 8.0 due to low hit rates in write‑heavy workloads, and is disabled by default in 5.6, with client‑side caching often recommended.

Parser analyses the incoming SQL, builds a parse tree, extracts keywords (SELECT, UPDATE, DELETE, FROM, WHERE, etc.), and validates syntax, reporting errors like "ERROR: You have an error in your SQL syntax".

Optimizer receives a syntactically correct query, chooses the best execution plan, selects appropriate indexes, and may reorder conditions (e.g., rewriting "WHERE B=x AND A=x AND C=x" to "WHERE A=x AND B=x AND C=x") for optimal performance.

Executor invokes the storage engine API (commonly InnoDB or MyISAM) to perform the actual data operations; only data‑modifying statements (INSERT, UPDATE, DELETE) are written to the binary log, while SELECT statements are not.

Execution states can be inspected with SHOW FULL PROCESSLIST , revealing stages such as locking, sorting, sending data, etc. (see image below).

SQL execution order (the logical processing sequence, not the textual order) includes:

FROM – determine source tables.

JOIN ON – combine tables and apply join conditions.

WHERE – filter rows.

GROUP BY – group rows.

HAVING – filter groups.

SELECT – choose output columns.

DISTINCT – remove duplicate rows.

ORDER BY – sort the result set.

LIMIT – paginate the final rows.

Each step creates intermediate temporary tables (Temp1, Temp2, …) as illustrated in the accompanying diagrams.

Conclusion – Understanding MySQL's execution pipeline and the logical SQL execution order enables developers to write more efficient queries, diagnose performance issues, and deepen their overall database knowledge.