ADVANCED DATABASE CONCEPTS
QUERY PROCESSING
QUERY PROCESSING
How DBMS takes your SQL query, understands it, finds
the best way to run it, and gives you the result.
Example query:
SELECT name
FROM Students
WHERE department = 'CS';
2
QUERY PROCESSING
Step 1
User writes:
SELECT name
FROM Students
WHERE department = 'CS';
Meaning: “Show me names of students whose
department is CS.”
3
QUERY PROCESSING
Step 2 : Parser checks the query
DBMS checks:
Is SQL syntax correct?
Table exists?
Column names exist?
Example:
SELECT nam FROM Students;
If nam column does not exist, DBMS gives an error.
4
QUERY PROCESSING
In Step 3, the SQL query is usually converted into:
Relational Algebra Expression
πname(σdepartment=′CS′(Students))
Where:
𝜎= Selection → filter rows
𝜋= Projection → choose columns
5
QUERY PROCESSING
Step 4 = Query Optimization. Relational Algebra Expression
↓
This is the step where DBMS Check table statistics
decides: ↓
“What is the fastest and Check indexes/access methods
cheapest way to execute this ↓
Rewrite query if possible
query?”
↓
Push selection and projection down
↓
Choose join order
↓
Choose join algorithm
↓
Estimate cost of plans
↓ 6
Select cheapest plan
QUERY PROCESSING
Step What DBMS Does Simple Meaning
Convert SQL into internal
1 Relational Algebra Expression
logical form
2 Check Table Statistics Check table size, rows, indexes
3 Choose Access Method Decide table scan or index scan
Rearrange operations for better
4 Rewrite Query
performance
Push Selection & Projection
5 Filter rows/columns early
Down
6 Choose Join Order Decide which tables join first
Select Hash Join, Merge Join,
7 Choose Join Algorithm
etc.
Calculate time, memory, disk
8 Estimate Cost
usage
7
Choose fastest and cheapest
9 Select Best Execution Plan
plan
QUERY PROCESSING
Step 5: Execution Plan
At this stage, DBMS has already decided:
best access method
best join order
best algorithms
cheapest way to execute query
Now it creates a final roadmap called:
Execution Plan (or Query Plan)
8
QUERY PROCESSING
Example Query St
Action
SELECT name ep
FROM Students Use index on
1
WHERE department = 'CS'; department
Suppose: Find rows where
2
department = 'CS'
Students table has 1 million Fetch matching
3
rows records
Index exists on department Return only name
4
column
9
QUERY PROCESSING
Step 6: Query Execution
Now DBMS actually starts doing the real work on data.
Before this:
parser checked query
optimizer selected best plan
execution plan was created
Now:
Query Executor follows the execution plan step by step.
10
QUERY PROCESSING
Step 7: Return Result to User
Now DBMS sends the final processed data back to:
user
application
website
software system
11
BASIC STEPS IN QUERY PROCESSING
1. Parsing and translation
2. Optimization
3. Evaluation
Thank You