4.1 Architecture Overview (Query Flow)

Level: 4 | Version: v1.1 | Author: Meptrasoft

Overview

When you send a SQL query, the database does not immediately return the answer.

The query passes through several internal components that parse, optimize, execute, retrieve, and return the result.

A simplified query flow is:

SQL QUERY
   ↓
PARSER
   ↓
OPTIMIZER
   ↓
EXECUTOR
   ↓
STORAGE / CACHE
   ↓
RESULT

Understanding this flow helps explain why database systems can execute the same query in different ways and why some queries perform better than others.

A SQL query is processed by multiple database components before the final result reaches the client.

How a SQL Query Flows Through a Database: Client, Network, Parser, Optimizer, Executor, Cache/Storage, Result — Figure 1: A SQL query passes through communication, parsing, optimization, execution, and data-access components before the result is returned.

Step 1: Client Sends the Query

The process starts when a client application sends a SQL statement to the database.

The client could be:

SQL command-line tool
Database management application
Backend application
Web application

For example:

SELECT Name, Salary
FROM Employees
WHERE Department = 'IT';

The database receives this request through its communication interface.

Step 2: Query Parser

The query parser checks whether the SQL statement follows the database's syntax and can be interpreted.

For example:

SELECT Name
FROM Employees;

is valid SQL syntax.

But:

SELEC Name
FROM Employees;

contains a syntax error.

The parser helps identify such problems before execution continues.

The parser may also create an internal representation of the query that later stages can work with.

Parser = Understand and validate the SQL statement.

Step 3: Query Optimizer

After parsing, the database optimizer determines an efficient way to execute the query.

Suppose the query is:

SELECT *
FROM Employees
WHERE Department = 'IT';

There may be several possible ways to find the required rows.

The optimizer considers factors such as:

Available indexes
Table statistics
Join methods
Estimated number of rows
Available access paths

It then produces an execution plan.

Optimizer = Decide how the query should be executed efficiently.

Step 4: Query Executor

The query executor follows the selected execution plan.

It performs operations such as:

Reading rows
Filtering rows
Joining tables
Sorting results
Calculating values

For example:

EXECUTION PLAN
      ↓
READ DATA
      ↓
FILTER
      ↓
RETURN MATCHING ROWS

The executor works with the storage and memory components required to obtain the data.

Executor = Run the selected execution plan.

Step 5: Storage Engine and Data Access

The executor needs access to the actual data.

The database's storage components handle how data is read from and written to persistent storage.

Depending on the database system, this can involve:

Data pages
Indexes
Buffer/cache memory
Database files

For example:

QUERY EXECUTOR
      ↓
STORAGE ENGINE
      ↓
INDEX / DATA PAGES
      ↓
DATABASE STORAGE

The exact architecture varies between database products, so the names and responsibilities of internal components are not identical in every RDBMS.

Step 6: Caching

Reading from disk or other persistent storage is generally slower than reading data already available in memory.

Database systems therefore use memory caches, such as buffer pools, to keep frequently needed database pages available in memory.

A simplified idea is:

QUERY
  ↓
CHECK MEMORY CACHE
  ↓
DATA FOUND?
 ↙       ↘
YES       NO
 ↓         ↓
READ      READ FROM STORAGE
CACHE         ↓
          LOAD INTO CACHE

Caching can reduce repeated physical I/O and improve query performance.

Step 7: Transaction and Write Management

For queries that modify data, the database must also manage transactional and recovery-related work.

For example:

UPDATE Accounts
SET Balance = Balance - 1000
WHERE Account_ID = 1;

The database may need to coordinate:

Transaction state
Locks or other concurrency mechanisms
Changes to data pages
Transaction logging
Recovery information

This helps maintain reliable database operations.

Step 8: Result Returns to the Client

After the query has been executed, the result is sent back to the client.

The overall simplified flow becomes:

CLIENT
  ↓
NETWORK / DATABASE SERVICE
  ↓
PARSER
  ↓
OPTIMIZER
  ↓
EXECUTOR
  ↓
CACHE / STORAGE
  ↓
RESULT
  ↓
CLIENT

For example:

SELECT Name, Salary
FROM Employees
WHERE Department = 'IT';

may return:

Name	Salary
Arun	65000
Priya	72000

Query Flow: Read vs Write

The internal work is not exactly the same for every SQL statement.

Read Query

For a SELECT:

SELECT
 ↓
PARSE
 ↓
OPTIMIZE
 ↓
EXECUTE
 ↓
READ CACHE / STORAGE
 ↓
RETURN RESULT

Write Query

For an INSERT, UPDATE, or DELETE, the database must also manage transaction and recovery-related operations.

INSERT / UPDATE / DELETE
          ↓
       PARSE
          ↓
      OPTIMIZE
          ↓
       EXECUTE
          ↓
TRANSACTION / LOGGING
          ↓
      DATA UPDATE

This is why database internals are more than simply “SQL goes directly to the table.”

A Simple Real-World Example

Suppose an employee application asks:

“Show all employees from the IT department.”

The application sends:

SELECT Name, Salary
FROM Employees
WHERE Department = 'IT';

The database roughly performs:

1. Receive SQL
       ↓
2. Parse SQL
       ↓
3. Build an execution strategy
       ↓
4. Execute the plan
       ↓
5. Find required data
       ↓
6. Return result

Figure 2: A simplified SQL query flow from the submitted statement to the final result.

Why Query Flow Matters for Performance

Understanding the query flow helps explain why two SQL queries that return the same result can have different performance.

For example, suppose an employee table contains millions of rows.

A query that can use an appropriate index may access fewer data pages than a query that requires a full table scan.

The optimizer considers available access paths and chooses an execution plan based on the database's statistics and cost estimates.

This is the foundation for topics such as:

Indexes
Execution plans
Query optimization
Join strategies
Query performance

Common Beginner Mistakes

✗ Mistake	✓ Correct Understanding
SQL directly reads the disk and returns the result	The database processes the query through multiple internal components
The optimizer executes the query	The optimizer selects an execution strategy; the executor runs it
Every query always reads from disk	Data may already be available in memory/cache
Every database has exactly the same internal architecture	Internal components and terminology differ between database systems
Indexes always make every query faster	Indexes can improve some access patterns but also add storage and write-maintenance costs
Query optimization changes the requested result	The optimizer should choose an execution strategy that produces the requested result according to database semantics

Placement Quick Points

CLIENT
→ SENDS SQL

PARSER
→ CHECKS / INTERPRETS SQL

OPTIMIZER
→ CHOOSES EXECUTION PLAN

EXECUTOR
→ RUNS THE PLAN

CACHE / STORAGE
→ PROVIDES DATA

RESULT
→ RETURNS TO CLIENT

A SQL query passes through multiple internal stages before returning a result.
The parser validates and interprets the SQL statement.
The optimizer selects an execution strategy.
The executor runs the selected plan.
Cache and storage components provide the required data.
Read and write queries can involve different internal operations.
Database products implement these components differently.

Interview Questions

What happens when a SQL query is submitted?

A simplified process is:

Receive → Parse → Optimize → Execute → Access Data → Return Result

What does the query parser do?

The parser checks and interprets the SQL statement and identifies syntax or other problems that prevent the query from being processed.

What is the role of the query optimizer?

The optimizer chooses an efficient execution strategy, usually represented as an execution plan.

What does the query executor do?

The executor runs the selected execution plan and performs operations needed to produce the result.

Why is caching useful?

Caching allows frequently needed database pages to be served from memory instead of repeatedly reading them from persistent storage, which can reduce I/O work.

Does every database use exactly the same query architecture?

No. The overall concepts are similar, but internal architecture, terminology, and implementation vary across database products.

Why is query flow important for performance?

It helps explain how factors such as indexes, execution plans, joins, memory, and storage access influence query execution time.

What is an execution plan?

An execution plan is the strategy chosen by the database system for executing a SQL query.

Practice & Hands-On Exercises

Beginner Practice

Write the basic stages of SQL query processing in order.
What is the role of the parser?
What is the role of the optimizer?
What is the role of the executor?
Why can a database read data from memory instead of storage?

Think and Answer

Why might two queries that return the same result have different performance?
What could happen when a query needs to read millions of rows?
Why are indexes important to query processing?
Why can database architecture differ between PostgreSQL, MySQL, SQL Server, and Oracle?

Query Flow Practice

For this query:

SELECT Name
FROM Employees
WHERE Department = 'IT';

What does the parser do?
What does the optimizer decide?
What does the executor perform?
Where might the database obtain the required data?
What is finally returned to the application?

💡 Tip: Test your queries using the in-browser interactive runner above.

Key Takeaway

SQL QUERY
    ↓
PARSER
    ↓
OPTIMIZER
    ↓
EXECUTOR
    ↓
CACHE / STORAGE
    ↓
RESULT

A SQL query passes through parsing, optimization, execution, and data-access stages before the database returns the final result.

End of Architecture Overview (Query Flow)