week 9
Teaching and Learning Methods to be Employed
1. Illustrated lecture on query processing pipeline
2. Lab: Using EXPLAIN in MySQL to analyse query execution plans
3. Index creation and performance comparison exercise
4. Collection of Assignment 3
Learning Outcomes / Objectives
By the end of this week, students should be able to:
1. Describe the steps in query processing from SQL statement to query result.
2. Explain the role of the query optimiser in a DBMS.
3. Understand relational algebra as the basis for query evaluation.
4. Describe the structure and benefits of database indexes.
5. Use MySQL's EXPLAIN statement to interpret a query execution plan.
6. Apply basic query optimisation techniques.
9.1 Overview of Query Processing
When you submit an SQL query, the DBMS does not simply 'read' and execute it directly. It goes through a sophisticated multi-stage process to determine the most efficient way to retrieve the requested data. This is query processing.
THE QUERY PROCESSING PIPELINE:
|
Stage |
Description |
|
1. Parsing and Translation |
SQL query is parsed for syntax errors and translated into an internal algebraic representation (relational algebra expression tree). |
|
2. Query Optimisation |
The query optimiser generates multiple equivalent execution plans and selects the one with the lowest estimated cost (I/O, CPU, memory). |
|
3. Code Generation |
The chosen plan is compiled into executable code. |
|
4. Query Execution |
The execution engine runs the plan, accessing data from disk/memory as needed. |
|
5. Result Return |
The final result set is returned to the user or application. |