MySQL Execution Process Overview

1. MySQL Execution Process

The overall execution flow of MySQL is illustrated in the diagram below.

1.1 Connector

1.1.1 Responsibilities of the Connector

1) Handles communication with the client in a half‑duplex mode, meaning at any moment only the client can send a request or the server can send data, but not both simultaneously; MySQL uses TCP/IP for this.

2) Verifies the supplied username and password; on failure it returns Access denied for user 'root'@'localhost' (using password: YES) .

3) After successful authentication, it checks the user's privileges in MySQL's internal privilege tables.

MySQL maintains four privilege tables: user , db , tables_priv , and columns_priv :

user : stores global (instance‑wide) privileges and account information.

db : stores database‑level privileges.

tables_priv : stores table‑level privileges.

columns_priv : stores column‑level privileges.

1.1.2 MySQL Privilege Table Verification Process

1) The server first checks the Host , User , and Password fields in the user table to confirm the client’s IP, username, and password.

2) After authentication, privileges are evaluated in the order user → db → tables_priv → columns_priv . If a global privilege is set to Y , the user is granted that privilege for all databases and the remaining tables are not checked; otherwise the check proceeds to the next table.

3) If any step fails, an error is returned.

1.2 Cache

MySQL’s query cache (removed in 8.0) stored the result set of a SQL statement as a key‑value pair to speed up repeated queries. Because the cache invalidated frequently in write‑heavy workloads, it was disabled by default in 5.6 and fully removed later; external caching on the client side is recommended for better performance.

1.3 Parser

The parser analyses the incoming SQL, performing lexical analysis and building a parse tree. It extracts keywords such as select , update , delete , where , group by , etc. Syntax errors produce messages like ERROR: You have an error in your SQL syntax. .

Example:

select * from user where userId =1234;

The parser then separates keywords from non‑keywords, validates object existence (e.g., table or column), and reports errors such as unknown column in field list. .

1.4 Optimizer

If the statement reaches the optimizer, it means the SQL conforms to MySQL’s semantic rules. The optimizer chooses the best execution plan, selects appropriate indexes, and may rewrite queries. Example: a query with a composite index on columns A, B, C can be reordered by the optimizer to match the index’s leftmost prefix.

1.5 Executor

The executor invokes the storage engine API (e.g., InnoDB or MyISAM) to perform the actual data operations. Only statements that modify data ( update , delete , insert ) are written to the binary log; select statements are not.

2. Execution States

Running show full processlist displays all server threads and their current states, covering everything from client request receipt to lock acquisition, sorting, temporary table creation, and data transmission.

3. SQL Execution Order

SQL statements are not executed in the textual order they are written; MySQL follows a fixed logical order, producing intermediate temporary tables as needed.

Example query:

select distinct s.id from T t join S s on t.id=s.id where t.name="Yrion" group by t.mobile having count(*)>2 order by s.create_time limit 5;

Key points:

SQL execution starts with FROM , not SELECT .

SELECT is evaluated after FROM and GROUP BY , so aliases cannot be used in WHERE .

UNION is processed before ORDER BY .

The optimizer may choose a plan that differs from the logical order.

When indexes exist, the optimizer prefers the most efficient index.

4. Order of Conditions After WHERE

4.1 Conclusion

In MySQL, condition evaluation proceeds left‑to‑right, top‑to‑bottom.

In Oracle, it proceeds right‑to‑left, bottom‑to‑top.

4.2 MySQL

For small data sets the order of WHERE conditions matters little, but for large tables the most selective condition should be placed first to filter out rows early.

Example of poor performance:

select … where p.languages_id=1 and t.type=1 and p.products_id in(472,474)

Changing the order to place the most selective predicate first dramatically reduces execution time:

where p.products_id in(472,474) and p.languages_id=1 and t.type=1

Although MySQL often optimizes the order automatically, manual tuning is sometimes required for optimal performance.

-- End of article --