Understanding MySQL Execution Process: Connectors, Permissions, Cache, Parser, Optimizer, and Executor

1. MySQL Execution Process

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

1.1 Connector

1.1.1 Main 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 client connections.

2. Verifies the supplied username and password; if they are incorrect, MySQL returns an error such as Access denied for user 'root'@'localhost' (using password: YES) .

3. After successful authentication, MySQL checks the user’s privileges in its internal privilege tables.

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

user : stores global (all‑databases) privileges for each user/host.

db : stores database‑level privileges.

tables_priv : stores table‑level privileges.

columns_priv : stores column‑level privileges.

1.1.2 MySQL privilege verification process

1. MySQL first checks the Host , User , and Password fields in the user table to determine whether the connection IP, username, and password exist.

2. After authentication, MySQL validates privileges in the order user → db → tables_priv → columns_priv . If a global privilege in user is set to Y , the user is granted that privilege on all databases and the remaining tables are not checked. If it is N , MySQL proceeds to the db table, then tables_priv , and finally columns_priv .

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

1.2 Cache

The cache improves query efficiency by storing SQL statement → result set pairs in a hash table. In MySQL 8.0 the query cache was removed because it invalidated frequently in write‑heavy workloads. In earlier versions (e.g., 5.6) the cache is disabled by default; a common practice is to move caching to the client side, which can yield a 5× performance boost.

1.3 Parser

The parser analyses the incoming SQL, performing preprocessing and syntactic parsing. It extracts keywords such as select , update , delete , or , in , where , group by , having , count , limit . If a syntax error is detected, MySQL returns an error like ERROR: You have an error in your SQL syntax. .

Example of a parsed statement:

select * from user where userId =1234;

The parser also validates the existence of referenced tables and columns, reporting errors such as unknown column in field list.

1.4 Optimizer

If the SQL passes parsing, it reaches the optimizer stage, where MySQL chooses the most efficient execution plan, selects appropriate indexes, and may rewrite the query. For example, a query with a composite index on columns A, B, C written as select xx where B=x and A=x and C=x will be internally reordered to where A=x and B=x and C=x to satisfy the left‑most index rule.

1.5 Executor

The executor invokes the storage engine API (e.g., InnoDB or MyISAM) to perform the actual data operations. The executor records changes to the binary log only for update , delete , and insert statements; select statements are not logged. Updates are written using a two‑phase commit and stored in the redo log.

2. Execution States

You can view all current threads with SHOW FULL PROCESSLIST . The output includes states such as "Sending data", "Sorting result", "Waiting for lock", etc., which together describe the full lifecycle of a client request.

3. SQL Execution Order

SQL statements are not executed in the textual order they appear. MySQL follows a fixed logical order: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT. Intermediate temporary tables may be created during this process.

Example:

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 the FROM clause, not SELECT.

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

UNION is processed before ORDER BY.

The optimizer may rewrite the plan; however, it does not always produce the optimal plan, so manual tuning may be required.

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

4. Order of Conditions in a WHERE Clause

4.1 Conclusion

For MySQL, conditions are evaluated from left to right, top to bottom.

For Oracle, evaluation proceeds from right to left, bottom to top.

4.2 MySQL

MySQL evaluates WHERE conditions left‑to‑right. With small data sets the order matters little, but with large tables it is important to place the most selective condition first.

Example of a slow query:

select … where p.languages_id=1 and t.type=1 and p.products_id in(472,474) takes over 20 seconds despite indexes, because MySQL first filters on p.languages_id and returns tens of thousands of rows.

Reordering the conditions:

select … where p.products_id in(472,474) and p.languages_id=1 and t.type=1 makes the first filter return fewer than ten rows, dramatically improving performance.

Therefore, place the condition that eliminates the most rows at the beginning of the WHERE clause.

Most of the time MySQL will automatically choose a good plan, but when it does not, manual ordering of conditions can help.

Feel free to discuss, ask questions, or contact the author for further clarification.

Additional resources and community links are provided at the end of the original article.