What Really Happens Inside MySQL When You Run a SELECT Query?

When learning SQL, the first statement you encounter is usually a

SELECT

query. This article explores what actually happens inside MySQL when such a query is executed, revealing the internal architecture and the role of each component.

How does MySQL execute a query?

The following diagram (shown below) illustrates the overall flow of a SQL statement through MySQL’s internal modules.

MySQL’s architecture consists of two main layers:

Server layer – establishes connections, parses and executes SQL, provides query cache, optimizer, executor, built‑in functions, stored procedures, triggers, views, etc.

Storage engine layer – stores and retrieves data. It supports multiple engines such as InnoDB, MyISAM, Memory. InnoDB is the default engine since MySQL 5.5 and implements B+‑tree indexes for primary and secondary keys.

Step 1: Connector

To use MySQL on Linux you first connect to the server, typically with:

<code># -h specify MySQL server IP (omit for localhost)
# -u specify username (root for admin)
# -p prompt for password (do not write password on command line)
mysql -h$ip -u$user -p
</code>

The connector performs a TCP three‑way handshake, then validates the username and password. If authentication fails, MySQL returns an “Access denied for user” error.

How can you see how many clients are connected to MySQL?

Run the command:

show processlist

The output shows each connection’s state; a

Sleep

state indicates an idle connection. MySQL’s

wait_timeout

variable (default 8 hours) controls how long an idle connection is kept before being closed.

<code>mysql> show variables like 'wait_timeout';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout | 28800 |
+---------------+-------+
1 row in set (0.00 sec)
</code>

You can manually terminate a connection with

kill connection +id

:

<code>mysql> kill connection +6;
Query OK, 0 rows affected (0.00 sec)
</code>

MySQL also limits the total number of connections via the

max_connections

variable (default 151). Exceeding this limit results in a “Too many connections” error.

<code>mysql> show variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   |
+-----------------+-------+
1 row in set (0.00 sec)
</code>

Connections can be short‑lived (connect, query, disconnect) or long‑lived (multiple queries per connection). Long connections reduce handshake overhead but may increase memory usage; idle connections are closed after

wait_timeout

or can be reset with

mysql_reset_connection()

in MySQL 5.7+.

Step 2: Query Cache

After the connector, MySQL checks whether the statement is a

SELECT

. If so, it looks in the query cache (key = SQL text, value = result). A cache hit returns the result immediately; a miss proceeds to execution and stores the result for future use.

Because any table modification invalidates related cache entries, the cache is ineffective for frequently updated tables and was removed entirely in MySQL 8.0. In earlier versions it can be disabled by setting

query_cache_type=DEMAND

.

Step 3: Parser

The parser performs lexical analysis (tokenizing the SQL) and syntactic analysis (building a parse tree). Errors such as misspelled keywords or non‑existent tables are reported at this stage.

<code>mysql> select * from form;
ERROR 1064 (42000): You have an error in your SQL syntax ...
</code>

Step 4: Optimizer

After parsing, the optimizer creates an execution plan, choosing the most efficient indexes based on cost estimation. For the example

select * from product where id = 1

, the optimizer selects the primary key index.

You can view the chosen plan with

EXPLAIN

:

<code>EXPLAIN SELECT * FROM product WHERE id = 1;
+----+-------------+---------+------+---+---------+-------+------+-------------+-------+
| id | select_type | table   | type | ...
+----+-------------+---------+------+---+---------+-------+------+-------------+-------+
|  1 | SIMPLE      | product | const| ...
+----+-------------+---------+------+---+---------+-------+------+-------------+-------+
</code>

If the optimizer cannot use an index, a full table scan (type = ALL) occurs.

Step 5: Executor

The executor follows the plan and interacts with the storage engine to fetch rows. Three typical execution paths are demonstrated:

Primary‑key index lookup

Full table scan

Index‑condition pushdown (index‑down‑push)

Primary‑Key Index Lookup

For

SELECT * FROM product WHERE id = 1

, the optimizer chooses a

const

access type. The executor calls the storage engine’s

read_first_record

function, which uses the InnoDB B+‑tree to locate the row, returns it, and then terminates the loop.

Full Table Scan

For a query without usable indexes, e.g.

SELECT * FROM product WHERE name = 'iphone'

, the optimizer selects

ALL

. The executor repeatedly calls the storage engine’s full‑scan interface, filtering rows on the server side until the table is exhausted.

Index‑Condition Pushdown

With a composite index (age, reward) and a query

SELECT * FROM t_user WHERE age > 20 AND reward = 100000

, MySQL 5.7+ can push the

reward = 100000

condition down to the storage engine. The engine first filters rows using the index, avoiding costly “row‑lookup” (back‑table) operations for rows that do not satisfy the condition.

When the execution plan shows

Using index condition

, index‑condition pushdown is active.

Summary

Connector – establishes TCP connection, authenticates, loads permissions.

Query cache – returns cached results if available (removed in MySQL 8.0).

Parser – lexical and syntactic analysis, builds parse tree.

Optimizer – selects the lowest‑cost execution plan, chooses indexes.

Executor – follows the plan, reads rows from the storage engine, returns results to the client.

That’s the complete journey of a SELECT statement inside MySQL.