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.3 Query Optimisation
The query optimiser is one of the most sophisticated components of a DBMS. It generates a query execution plan — the step-by-step instructions for retrieving data — and chooses the plan with the lowest estimated cost.
COST FACTORS the optimiser considers:
1. Number of disk I/O operations (the most expensive operation)
2. Amount of data transferred over the network
3. CPU processing cost
4. Size of intermediate results
5. Available indexes
6. Statistics about data distribution (maintained in the catalogue)
OPTIMISATION STRATEGIES:
1. Perform selections as early as possible (reduces rows early)
2. Perform projections as early as possible (reduces columns)
3. Reorder joins to start with the smallest result sets
4. Use indexes to avoid full table scans
5. Use join methods appropriate to data size (Nested Loop, Hash Join, Sort-Merge Join)