Understanding MySQL Query Execution Process and Optimization

To achieve efficient MySQL query execution, it is essential to understand each stage of the SQL processing pipeline, from the client request to the final result returned to the client.

The execution steps are: (1) the client sends the query to the server; (2) the server checks the query cache and returns cached results if available; (3) the server parses, validates, and preprocesses the SQL, then the optimizer generates an execution plan; (4) the executor follows the plan and calls the storage engine to retrieve data; (5) the server sends the result back to the client.

A sample User table is created and populated:

DROP TABLE IF EXISTS User;
CREATE TABLE `User` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  `age` int DEFAULT 0,
  `address` varchar(255) DEFAULT NULL,
  `phone` varchar(255) DEFAULT NULL,
  `dept` int,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=40 DEFAULT CHARSET=utf8;

INSERT INTO User(name,age,address,phone,dept) VALUES
('张三',24,'北京','13265543552',2),
('张三三',20,'北京','13265543557',2),
('李四',23,'上海','13265543553',2),
('李四四',21,'上海','13265543556',2),
('王五',27,'广州','13265543558',3),
('王五五',26,'广州','13265543559',3),
('赵六',25,'深圳','13265543550',3),
('赵六六',28,'广州','13265543561',3),
('七七',29,'广州','13265543562',4),
('八八',23,'广州','13265543563',4),
('九九',24,'广州','13265543564',4);

The following query is used to find the departments where the number of employees younger than 25 exceeds three, ordered by employee count descending and department number ascending, returning the top two departments:

SELECT dept, COUNT(phone) AS num
FROM User
WHERE age < 25
GROUP BY dept
HAVING num >= 3
ORDER BY num DESC, dept ASC
LIMIT 0, 2;

When the query is executed, MySQL first checks permissions, then looks for a cached execution plan. If not found, it performs a memory lookup using a key‑value cache (query text as key, result set as value). If the cache misses, the parser runs lexical analysis (tokenizing keywords, tables, columns, conditions) and syntax analysis (validating MySQL grammar).

After parsing, the FROM clause loads the table data into memory, the WHERE clause filters rows (operating on raw table columns), and GROUP BY creates temporary groups. The HAVING clause then filters groups using the aggregated alias num . Finally, ORDER BY sorts the result and LIMIT selects the first two rows.

The article also clarifies the difference between WHERE and HAVING: WHERE filters rows before aggregation and cannot use column aliases, while HAVING filters after aggregation and can reference aliases.

The optimizer converts the parse tree into one or more execution plans, estimates their costs, and chooses the cheapest plan. Frequently executed or similar statements can reuse cached plans, improving performance.

The executor carries out the chosen plan, invoking storage‑engine interfaces to fetch data. For data‑modifying statements (UPDATE, DELETE), MySQL would also involve transaction logs such as redo log and binlog, though this article focuses on SELECT.