The anatomy of a query

Before any SQL exists there is a connection. A client reaches mysqld over a TCP socket (port 3306 by default) or, on the same host, a Unix domain socket. The Unix socket is meaningfully faster — no TCP stack, no loopback — which is why -h localhost and -h 127.0.0.1 behave differently in the MySQL client: the first uses the socket file, the second forces TCP.

What follows is not a simple password check. It is a negotiation, and the server speaks first.

The auth plugins, and why 8.4 broke things

The password is never sent. The server issues a random scramble; the client proves it knows the password by returning a hash computed over that scramble. Which hash depends on the plugin:

The caching_sha2_password design deserves a note, because it explains a confusing first-connection failure. On a cache miss the client must send the password in a form the server can verify, which requires either a TLS connection or an RSA public-key exchange. Over a plaintext connection with no RSA key available, the server refuses. The second connection for the same user succeeds, because the cache is now warm — which produces the classic "it fails once then works" report.

-- Which plugin is each account using?
SELECT user, host, plugin FROM mysql.user ORDER BY user;

-- What does this server accept? (8.4 replaced default_authentication_plugin)
SHOW VARIABLES LIKE 'authentication_policy';

-- Socket vs TCP — these are NOT the same connection.
SHOW VARIABLES LIKE 'socket';
--   mysql -h localhost   → unix socket
--   mysql -h 127.0.0.1   → TCP, even on the same box

-- Prove it: this column differs between the two.
SELECT connection_type, processlist_host
FROM performance_schema.threads
WHERE processlist_id = CONNECTION_ID();

Once authenticated, the connection is handed a thread. In the default model — thread_handling = one-thread-per-connection — every client connection owns an OS thread for its entire life, whether it is executing a query or sitting idle for an hour.

Threads are expensive to create, so mysqld keeps a cache of idle ones. thread_cache_size controls how many are retained for reuse; when a connection closes, its thread goes back to the pool instead of being destroyed. A high Threads_created counter relative to Connections means the cache is too small and you are paying thread creation cost on every connect — the signature of an application that opens a fresh connection per request instead of pooling.

Why this matters more than it looks

Each thread carries its own per-connection buffers — sort_buffer_size, join_buffer_size, read_buffer_size and others. These are allocated per connection, sometimes per join, not globally. This is the arithmetic that kills servers:

max_connections        = 1000
sort_buffer_size       = 4M
join_buffer_size       = 4M
read_rnd_buffer_size   = 2M

worst case ≈ 1000 × (4 + 4 + 2) MB = 10 GB  on top of the buffer pool

You will meet this properly in chapter 75. For now, the rule: raising a per-connection buffer multiplies by max_connections. Raising it "because the server has RAM" is how you get an OOM kill during a traffic spike.

MySQL Enterprise (and MariaDB's community build, and Percona) offer a thread pool plugin that decouples connections from threads, serving thousands of connections with a small worker pool. In community MySQL you achieve the same by putting ProxySQL in front.

-- Is the thread cache doing its job?
-- Threads_created should be tiny next to Connections.
SHOW GLOBAL STATUS WHERE Variable_name IN
  ('Threads_created', 'Threads_cached', 'Threads_connected',
   'Threads_running', 'Connections');

-- Threads_running is the number actually doing work right now.
-- Connected 500 / running 4 is healthy. Running 200 means queueing.

SHOW VARIABLES LIKE 'thread_cache_size';
SHOW VARIABLES LIKE 'thread_handling';

The thread reads a packet whose first byte is a command code — 0x03 is COM_QUERY, "here is a SQL string" — and the rest is your statement. Two stages follow.

Lexical analysis turns the character stream into tokens: keywords, identifiers, literals, operators. Parsing then matches those tokens against the grammar (a Bison grammar, in sql_yacc.yy) and produces a parse tree of C++ objects.

Syntax errors come from this stage, which is why their messages are so characteristically unhelpful — the parser knows only that the token sequence did not match a production. The "check the manual that corresponds to your MySQL server version for the right syntax to use near…" message is the parser telling you where it gave up, and the token it names is frequently the one after the actual mistake.

The query cache is gone, and that is good

Before 8.0, a stage sat right here: the query cache. It hashed the incoming SQL text and, on an exact byte-for-byte match, returned a previously computed result set without parsing or executing anything.

It was removed in 8.0, and you should understand why, because it is a good lesson in concurrency design:

• It was protected by a single global mutex. Every query, cached or not, contended on it. On multi-core hardware this became the bottleneck.
• Invalidation was table-granular. One write to a table evicted every cached result mentioning that table, so any write-active table made the cache useless.
• Matching was on exact text. An extra space, a different comment, a differing case — all misses.

The net effect on a busy server was negative: it slowed things down while appearing to help. The modern answer is caching in the application or in a proxy like ProxySQL, where invalidation can be reasoned about properly.

A parse tree is syntactically valid but semantically meaningless. The resolution phase gives it meaning:

• Name resolution. Every table name is looked up in the data dictionary; every column name is bound to a specific table. This is where "Unknown column 'x' in 'field list'" and "Table doesn't exist" originate — note that these are different errors from syntax errors, raised at a later stage.
• Ambiguity checks. A bare id in a join of two tables that both have id produces "Column 'id' in field list is ambiguous".
• Privilege checks. Does this account have SELECT on these columns of these tables? Checked here, before any execution.
• View and CTE expansion. Views are merged into the query or materialized; CTEs are registered.
• Type derivation. Each expression gets a result type, which determines implicit conversions — the source of the sargability disasters in chapter 74.

-- Watch the stages separate. Each of these fails at a DIFFERENT phase.

SELECT FROM t;              -- parser: syntax error near 'FROM'
SELECT * FROM no_such_table;  -- resolver: table doesn't exist
SELECT nope FROM mysql.user;  -- resolver: unknown column

-- Server-side prepare: parse+resolve once, execute many.
PREPARE st FROM 'SELECT ? + ?';
SET @a = 3, @b = 4;
EXECUTE st USING @a, @b;
DEALLOCATE PREPARE st;

-- Count how many prepares vs executes this server has done.
SHOW GLOBAL STATUS LIKE 'Com_stmt_%';

Part 8 is twelve chapters on the optimizer. Here we only establish where it sits and what it is deciding, so the rest of the pipeline makes sense.

The optimizer takes the resolved tree and produces an execution plan. It works in two modes at once:

• Rule-based rewrites that are always wins: constant folding (WHERE 1=1 AND x=5 loses the tautology), removing impossible conditions (WHERE 1=0 short-circuits to "Impossible WHERE"), flattening some subqueries into joins, eliminating unused tables in a join.
• Cost-based choices where there are several valid plans and it has to guess which is cheapest: which index to use, in what order to join the tables, whether to sort or use an index's ordering, whether to materialize a derived table.

The cost is a unitless number derived from estimated page reads and row comparisons, weighted by constants in mysql.server_cost and mysql.engine_cost. Crucially, the row counts feeding that estimate come from the storage engine through the handler API and are approximations — InnoDB samples a handful of index pages rather than counting. This is why EXPLAIN's rows column is so often wrong, and why the optimizer sometimes picks a plan that is obviously bad to you and defensible to it.

The plan is a tree of iterators. Execution is a pull: the top of the tree asks for a row, which asks its child for a row, down to a leaf that asks the storage engine. This is the classic Volcano model, and MySQL 8.0 refactored its executor to implement it explicitly — which is what made EXPLAIN ANALYZE and its per-iterator timings possible.

Two properties of this model explain a lot of observed behavior:

• It streams. Rows flow to the client as they are produced, so a query with LIMIT 10 over a huge table can stop early — the top iterator simply stops pulling. This is why adding LIMIT to an indexed, ordered query is nearly free, while adding it to a query requiring a full sort is not.
• Blocking operators break the streaming. A sort cannot emit its first row until it has seen its last input row. Neither can a hash join build, or a materialized derived table. In EXPLAIN ANALYZE these show up as a large gap between "actual time to first row" and the child's total time — the clearest signal in the whole output.

-- EXPLAIN ANALYZE prints the iterator tree with real timings.
-- Read it inside-out: the most indented node runs first.
-- "actual time=A..B" → A = time to FIRST row, B = time to LAST row.

EXPLAIN ANALYZE
SELECT u.id, u.email
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE u.created_at > '2026-01-01'
ORDER BY u.id
LIMIT 10\G

-- Look for a Sort node where A is close to B: that is the blocking
-- operator. Everything below it had to finish before anything emerged.

At the leaves of the iterator tree, the executor stops being able to do anything itself. It must ask the storage engine. That request goes through the handler class — the seam introduced in chapter 6.

The vocabulary is small. A full scan is rnd_init() then rnd_next() repeatedly until end-of-file. An index range scan is index_init(), index_read() to position at the start key, then index_next() to walk forward. Writes are write_row(), update_row(old, new), delete_row().

The cost of the seam

Each row crossing this boundary costs a virtual function call and a copy into the server's row buffer. For an index range scan returning a million rows to be filtered down to ten by the server, that is a million crossings, and it is the reason two optimizations exist specifically to avoid them:

• Index Condition Pushdown hands part of the WHERE clause down to the engine so it can reject rows before crossing the boundary. Using index condition in EXPLAIN.
• Covering indexes let the engine answer entirely from the index without fetching the row at all. Using index.

Both are chapter 35. The point here is that they exist because of this architectural seam — they are not general database optimizations, they are MySQL's answer to its own layering.

Rows return to the client as a sequence of packets. Every MySQL packet carries a 4-byte header: 3 bytes of payload length, 1 byte of sequence number. The 3-byte length caps a single packet at 16 MB − 1; anything larger is split across packets and reassembled, which is what max_allowed_packet governs.

A result set is a defined sequence of these:

-- text protocol result set
1.  column_count           -- how many columns follow
2.  column_definition × N  -- name, table, type, flags, charset
3.  rows                   -- each value length-encoded, NULL = 0xFB
4.  OK packet              -- terminator, carries affected_rows,
-- last_insert_id, warnings, status flags

Text protocol vs binary protocol

A plain COM_QUERY returns values as strings. The integer 42 arrives as the two characters 4 and 2; a DATETIME arrives as '2026-09-21 14:03:00'. Your driver parses them back into native types.

A server-side prepared statement uses the binary protocol: integers come back as fixed-width little-endian values, dates as packed structures. Less bandwidth, no string parsing, and no ambiguity about types. This is a second, less-discussed reason to prepare statements.

-- Packet size ceiling for one payload.
SHOW VARIABLES LIKE 'max_allowed_packet';

-- Bytes actually sent and received by this server.
SHOW GLOBAL STATUS LIKE 'Bytes_%';

-- See the real protocol. Run this in a shell, not in SQL:
--   mysql --protocol=TCP -e "SELECT 1" --debug-info
-- or watch the wire directly:
--   sudo tcpdump -i lo0 -X -s0 'port 3306'

-- Compare payload size: text vs binary protocol for the same row.
SELECT 42, NOW(), 'hello';

That is the full path. Part 2 goes down one level: the files, tablespaces and 16KB pages that the storage engine was reading from at the bottom of it.