How MySQL Executes a SELECT Query: From Client to Storage Engine

SQL Execution Flow Overview

MySQL processes a query through three logical layers—client, server, and storage engine—while omitting the low‑level system file layer for clarity.

Client

The client (GUI, command line, or library) sends a SQL statement such as: SELECT * FROM users WHERE id = 1; It first establishes a connection to the MySQL server, providing authentication credentials. Long‑lived connections retain the permissions that were in effect when the connection was created.

Connector

The connector validates the client’s identity and permissions, manages the session, and forwards the SQL request to the server layer. It also handles error messages like “Access denied for user” and disconnects idle connections after a default timeout (e.g., 8 hours). In MySQL 5.5 a thread pool was introduced to reuse threads for many connections.

Query Cache (pre‑MySQL 8.0)

For older versions, the server checks a query cache that stores complete result sets for identical statements. If a cache hit occurs, the result is returned immediately; otherwise execution continues. The cache is invalidated on any data‑modifying operation (INSERT, UPDATE, DELETE), so it is generally discouraged and disabled by default in MySQL 5.6 and removed in MySQL 8.0.

Parser

The parser converts the SQL text into a sequence of tokens (lexical analysis) and then builds a parse tree (syntax analysis). For the example statement the tokens are: SELECT — query keyword * — wildcard FROM — source clause users — table name WHERE — filter clause id — column name = — operator 1 — literal value ; — statement terminator

It then checks that the referenced table and columns exist and that the syntax is correct, producing an abstract syntax tree that is passed to the optimizer.

Optimizer

The optimizer evaluates multiple execution strategies using table metadata (indexes, statistics) and chooses the lowest‑cost plan. Possible strategies include a full table scan or an index lookup on id. For joins, it may select nested‑loop, index‑nested‑loop, or hash joins, and decide whether to use covering indexes, sort avoidance, or temporary tables. Cost factors consider I/O, row counts, and CPU usage.

The resulting execution plan can be inspected with the EXPLAIN command:

EXPLAIN SELECT * FROM users WHERE id = 1;

Executor

The executor follows the plan, invoking the appropriate storage‑engine API (e.g., InnoDB). It first checks the client’s SELECT privilege on the target table. Then it retrieves rows either via index lookups or full scans, filters them according to the WHERE clause, and streams matching rows back to the client, respecting any LIMIT clause.

Requests matching records from the storage engine.

Streams each retrieved row to the client immediately.

Stops when all rows are processed or the LIMIT is reached.

Summary

Client : Sends the SQL text to the server.

Connector : Authenticates, manages the session, and forwards the request.

Query Cache : (If enabled) Returns cached results or proceeds.

Parser : Tokenizes and parses the statement into a syntax tree.

Optimizer : Generates the most efficient execution plan.

Executor : Executes the plan by interacting with the storage engine and returns results.