How Does MySQL Execute a Simple SELECT? Inside the Server Architecture

For example, a simple SQL statement: mysql> select * from T where ID=10; How does MySQL execute it internally? Let's walk through the process.

Connector

The client first establishes a connection to MySQL using a connector, which handles connection establishment, permission verification, and connection management.

Parser

After the client and server are connected, the SQL request is sent to the server. The connector passes the request to the parser, which performs lexical analysis (identifying keywords such as select , table names, column names) and then syntax analysis to verify MySQL syntax.

Optimizer

After parsing, MySQL knows what to do and relies on the optimizer to decide how to do it.

Example:

mysql> select * from t1 join t2 using(ID) where t1.c=10 and t2.d=20;

Plan 1: Retrieve IDs from table t1 where c=10, then join with t2 on ID, and finally check if t2.d=20.

Plan 2: Retrieve IDs from table t2 where d=20, then join with t1 on ID, and finally check if t1.c=10.

Both plans produce the same logical result, but their execution efficiency differs; the optimizer chooses the better one. It also decides which index to use when multiple indexes exist.

Executor

Knowing what to do and how to do it, the executor carries out the actual execution.

The executor's basic logic calls the storage engine to fetch the first row that satisfies the condition, then repeatedly fetches the next matching rows, assembling all rows into a result set returned to the client.

At this point, the statement execution is complete.

Overall MySQL Structure

MySQL consists of two layers: the server layer and the storage engine layer.

Server layer includes the connector, parser, optimizer, executor, core services, built‑in functions (date, time, math, encryption, etc.), and cross‑engine features such as stored procedures, triggers, and views.

The server layer also contains a query cache, which checks for cached results before parsing; however, its impact is minimal in real environments and it was removed in MySQL 8.

Storage engine layer handles data storage and retrieval and follows a plug‑in architecture.

Content adapted from the GeekTime column “MySQL in Practice 45 Lectures”.