The Journey of a SQL Query Through a Database

Step-by-step walkthrough

Saurabh Dashora

Nov 19, 2024

What happens when you make an SQL query to the database?

Let’s say this is the query:

SELECT name, age FROM users WHERE city = 'New York';

Here’s a diagram that shows the process visually:

You can play around with the diagram on Eraser.io

Let’s go through each step in more detail:

STEP 1

When you hit run, the query string first reaches the Transport Subsystem of the database.

As the name suggests, this subsystem manages the connection with the client.

It also performs initial authentication and authorization checks, and if satisfied, lets the query go to the next step.

STEP 2

The query now reaches the Query Processor, which has two main parts:

Query Parser
Query Optimizer

The Query Parser breaks down the SQL query into its parts (SELECT, FROM, WHERE, etc). It checks for any syntax errors and creates a parse tree.

Then, the Query Optimizer goes through the parse tree, checks for semantic errors (e.g. - if the ‘users’ table exists or not), and determines the most efficient way to execute the query.

As output, the optimizer produces an execution plan.

STEP 3

Next, the execution plan makes its way to the Execution Engine.

The plan is made up of all the steps that are needed to execute the query.

The Execution Engine takes this plan and coordinates the execution of each step by calling the Storage Engine. It also collects the results from each step and returns a unified response to the upper layer.

STEP 4

The Execution Engine sends low-level read/write requests to the Storage Engine depending on the execution plan.

All of this is facilitated by the various employees of the Storage Engine.

For example,

The transaction manager ensures that the query runs within a transaction for consistency.
The lock manager acquires necessary locks on the “users” table to prevent conflicts.
The buffer manager checks if the required data pages are already in memory. If not, it requests the data to be loaded from the disk into memory.
The recovery manager logs the operations for rollback or recovery.

👉 So - what else would you add to this journey of an SQL query?