Understanding MySQL Execution Process and SQL Execution Order

MySQL is encountered daily in development as the primary data source, handling storage and read/write operations; understanding its request handling and SQL execution is essential.

MySQL Execution Process

The overall execution flow is illustrated in the diagram below.

Connector

The connector handles half‑duplex client communication, validates user credentials, and checks permissions using four privilege tables: user , db , tables_priv , and columns_priv . If authentication fails, an "Access denied" error is returned.

Cache

MySQL’s query cache (removed after version 8.0) stored SQL statements as keys and result sets as values to speed up reads; in newer versions the cache is disabled by default, and external caching is recommended.

Parser

The parser analyzes the incoming SQL, builds a parse tree, extracts keywords (SELECT, UPDATE, WHERE, etc.), and validates syntax, reporting errors such as "ERROR: You have an error in your SQL syntax."

Optimizer

If the statement passes semantic checks, the optimizer selects the best execution plan, chooses 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 ) to improve performance.

Executor

The executor invokes the storage engine API (commonly InnoDB or MyISAM) to perform the actual data operations; only data‑modifying statements are written to the binlog, while SELECTs are not.

Execution State

Running SHOW FULL PROCESSLIST reveals server states such as locking, sorting, temporary table creation, and data transmission, as shown in the accompanying diagram.

SQL Execution Order

SQL clauses are processed in a fixed logical order, not the textual order: FROM → JOIN/ON → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT. Each step creates intermediate temporary tables (Temp1, Temp2, …) that feed the next stage, with examples provided for each phase.

Summary

The article summarizes MySQL’s execution pipeline and SQL execution order, helping readers understand query processing, optimize statements, and deepen their database knowledge.