How MySQL Executes a Simple SQL Query: Step‑by‑Step Deep Dive

1. Simple SQL Execution Process in MySQL

A simple diagram shows MySQL’s architecture and component relationships before analyzing a sample SQL statement.

SELECT department_id FROM employee WHERE name = 'Lucy' AND age > 18</code>
<code>GROUP BY department_id

The client (e.g., MySQL CLI, Navicat, DBeaver) sends the query to the MySQL server.

Connector : establishes and manages the client connection, authenticates credentials, and allocates a thread.

Query Cache : checks whether an identical query result is already cached; if so, returns it directly.

Parser : validates syntax, table and column names, and builds a query tree.

Optimizer : evaluates possible execution plans, estimates costs, and selects the best plan—often using the index on name in this example.

Executor : follows the optimizer’s plan, requesting rows from the storage engine.

Storage Engine (InnoDB) : performs the actual index scan, checks the buffer pool, loads required pages from disk if not cached, and returns the rows.

During execution, the executor re‑checks index conditions (e.g., name and age > 18) and then groups results by department_id before sending them back to the client.

2. Execution Order of SQL Keywords

The logical processing order differs from the written order:

FROM – retrieve data from tables.

ON – apply join conditions.

JOIN – combine rows from multiple tables.

WHERE – filter rows using indexed conditions when possible.

GROUP BY – group filtered rows.

HAVING – filter groups.

SELECT – project the final columns.

DISTINCT – remove duplicate rows.

ORDER BY – sort the result set.

LIMIT – truncate the result set.

3. Join Query Execution Process

SELECT s.id, s.name, s.age, es.subject, es.score</code>
<code>FROM employee s JOIN employee_score es ON s.id = es.employee_id</code>
<code>WHERE s.age > 18 AND es.subject_id = 3 AND es.score > 80;

The query uses a composite index on subject_id and score, and an index on age. The execution follows the same component flow:

Connector authenticates and creates the session.

Query Cache is consulted first.

Parser parses tables employee and employee_score and identifies columns.

Optimizer chooses a plan that scans the composite index, decides the driver table (often the one with fewer qualifying rows), and selects a join strategy (e.g., Nested‑Loop or Hash Join).

Executor requests rows from the storage engine based on the plan.

Storage Engine uses the indexes to locate matching rows, checks the InnoDB buffer pool, loads pages from disk if needed, and returns the data.

The executor then performs in‑memory join, applies remaining filters ( age > 18), and finally returns the result set.

Even after the storage engine loads pages using index conditions, the executor must re‑evaluate those conditions to ensure only qualifying rows are returned.

4. Summary

The article illustrated MySQL’s internal components and their interactions using a simple diagram, then traced a single‑table and a join query from client request to result delivery, highlighting the logical execution order of SQL clauses and the role of indexes in optimizing performance.