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

1. MySQL Execution Process

1.1 Connector

The connector handles client communication in a half‑duplex mode, validates user credentials, and checks the four privilege tables (user, db, tables_priv, columns_priv) to determine access rights.

1.2 Cache

MySQL cache stores SQL‑to‑result mappings as a hash table to speed up queries; it was removed in MySQL 8.0 because frequent invalidations made it inefficient, and the recommendation is to move caching to the client side.

1.3 Parser

The parser analyses incoming SQL, extracts keywords such as select , update , delete , where , group by , etc., builds a parse tree, and reports syntax errors (e.g., ERROR: You have an error in your SQL syntax. ).

1.4 Optimizer

After successful parsing, the optimizer chooses the best execution plan, selects appropriate indexes, and may reorder conditions (e.g., turning select ... where B=x and A=x and C=x into where A=x and B=x and C=x ) to match index order.

1.5 Executor

The executor calls the storage‑engine API (commonly InnoDB or MyISAM) to perform the actual data operations; only update/delete/insert statements are written to the binlog, while select is not.

2. Execution States

Running SHOW FULL PROCESSLIST displays each thread’s current state, covering lock acquisition, sorting, temporary table usage, data sending, etc.

3. SQL Execution Order

SQL is executed in a fixed logical order, not the textual order written by the user. The typical order is: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT. (Diagram omitted.)

4. Order of Conditions in WHERE Clause

4.1 Conclusion

MySQL evaluates conditions from left to right (top to bottom).

Oracle evaluates from right to left (bottom to top).

4.2 MySQL Practical Tip

Place the most selective condition first to reduce the row set early. For example, changing the order from where p.languages_id=1 and t.type=1 and p.products_id in(472,474) to where p.products_id in(472,474) and p.languages_id=1 and t.type=1 reduced execution time dramatically because the first filter returned fewer than ten rows.

Overall, while MySQL often chooses an optimal plan automatically, understanding and influencing the execution order can yield significant performance gains.