Unveiling MySQL: Step‑by‑Step How a SQL Query Is Executed

1. Introduction

The author wanted to understand how a SQL statement is executed and in what order, so this article summarizes the MySQL execution process from architecture to clause‑by‑clause parsing.

2. MySQL Architecture Overview

The system consists of two layers: the upper "SQL Layer" (MySQLD) that handles client connections and query parsing, and the lower "Storage Engine Layer" that provides various storage engines. Each module’s name hints at its role.

3. Query Execution Process

The execution can be divided into three main stages: Connection, Processing, and Result.

1. Connection

1.1 Client sends a query request.

1.2 Request is forwarded to the connection/thread module.

1.3 User module performs authentication.

1.4 A free thread from the thread pool is assigned, or a new thread is created.

2. Processing

2.1 Check query cache; if a match and permissions are valid, return data directly.

2.2 If cache miss, the command parser performs lexical and syntactic analysis to build a parse tree.

2.3 Pre‑processing resolves semantics, checks permissions, and may produce a new parse tree.

2.4 The appropriate module handles the request.

2.5 For SELECT, the optimizer generates an execution plan.

2.6 Access control verifies that the user can access the target tables/columns.

2.7 Table manager checks the table cache; if missing, the table file is opened.

2.8 Table metadata determines the storage engine, which then processes the request.

2.9 Any data changes are logged to binary logs if logging is enabled.

3. Result

3.1 The query result is returned to the connection/thread module.

3.2 The client receives either data or a status indicator.

3.3 The thread cleans up and waits for the next request or disconnects.

4. SQL Parsing Order

The actual execution order of a SQL statement differs from its textual order. The following diagram shows the logical flow.

1. FROM

When multiple tables are involved, the left table’s output becomes the input for the right table, producing a virtual table VT1.

1.1 Cartesian product (CROSS JOIN) – generates VT1‑J1.

1.2 ON filter – filters VT1‑J1 according to ON predicates, producing VT1‑J2.

1.3 Add external columns – for outer joins, rows that do not satisfy ON are added as external rows, forming VT1‑J3.

2. WHERE

Filters the temporary table generated by FROM, inserting rows that satisfy the WHERE clause into VT2.

Note: GROUP BY prevents use of aggregate functions here, and column aliases from SELECT cannot be used.

3. GROUP BY

Groups rows of VT2 according to the GROUP BY columns, producing VT3. Columns not in GROUP BY must be aggregated.

4. HAVING

Filters groups in VT3, keeping only those that satisfy HAVING conditions, resulting in VT4.

5. SELECT

Processes the SELECT list, creating VT5. It includes expression evaluation (VT5‑J1) and optional DISTINCT processing (VT5‑J2), which may create an in‑memory temporary table with a unique index.

6. ORDER BY

Sorts the result of the previous step (VT5‑J2) according to ORDER BY clauses, producing VT6. This is the only place where SELECT aliases can be used.

7. LIMIT

Applies offset and row count to VT6, returning the final result set. Large offsets can be inefficient; using sub‑queries to fetch the maximum ID first or employing INNER JOIN with indexed ID lists can improve performance.

By following these steps, the complete journey of a SQL statement through MySQL—from client request to final result—becomes clear.