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

1. MySQL Execution Process

MySQL's overall execution flow includes a connector, cache, parser, optimizer, and executor as shown in the diagram.

1.1 Connector

1.1.1 Responsibilities of the connector

1. Handles client communication in half‑duplex mode (TCP/IP). 2. Authenticates user credentials; on failure returns "Access denied for user …". 3. After successful authentication, retrieves the user's privileges from MySQL's privilege tables.

MySQL stores privileges in four tables:

user : global user accounts and privileges.

db : database‑level privileges.

tables_priv : table‑level privileges.

columns_priv : column‑level privileges.

1.1.2 Privilege verification process

First checks Host , User , and Password in the user table. If valid, it proceeds to verify privileges in order: user → db → tables_priv → columns_priv . Any failure raises an error.

1.2 Cache

The cache stores query results as key‑value pairs to speed up repeated reads. From MySQL 8.0 the query cache was removed because of frequent invalidations; in earlier versions it is disabled by default and is often moved to the client side.

1.3 Parser

The parser analyses the incoming SQL, performs lexical analysis, builds a parse tree and validates syntax. Example keywords include select , update , delete , where , group by , having , limit . Syntax errors raise ERROR: You have an error in your SQL syntax.

1.4 Optimizer

After parsing, the optimizer chooses the best execution plan, selects appropriate indexes and may rewrite queries (e.g., reordering conditions to satisfy the leftmost‑most index rule).

1.5 Executor

The executor calls the storage‑engine API (e.g., InnoDB, MyISAM). Only DML statements (INSERT/UPDATE/DELETE) are written to the binlog; SELECT statements are not.

2. Execution States

Running SHOW FULL PROCESSLIST displays each thread’s state, such as acquiring locks, sorting, sending data, etc.

3. Logical SQL Execution Order

SQL is processed in a fixed logical order, not the textual order. Typical order: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT, with temporary tables created along the way.

4. Order of Conditions in WHERE Clause

4.1 Conclusion

MySQL evaluates WHERE conditions from left to right, top to bottom.

Oracle evaluates them from right to left, bottom to top.

4.2 MySQL

Place the most selective condition first to reduce the row set early. An example shows that reordering conditions dramatically improves query time, especially when indexes exist on the early‑evaluated columns.

Feel free to discuss, ask questions, or share your own experiences with MySQL execution internals.