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.5 Using EXPLAIN to Analyse Queries
MySQL's EXPLAIN statement shows how MySQL plans to execute a query — what indexes it will use, how many rows it estimates to scan, and the join method.
EXPLAIN SELECT s.FirstName, s.LastName, d.DeptName
FROM STUDENT s INNER JOIN DEPARTMENT d ON s.DeptCode = d.DeptCode
WHERE s.Level = 300;
KEY EXPLAIN OUTPUT COLUMNS:
|
Column |
What It Tells You |
|
type |
Access method: ALL (full scan — bad!), index, range, ref, eq_ref, const (fast!) |
|
key |
Which index MySQL chose to use (NULL means no index used) |
|
rows |
Estimated number of rows MySQL will examine |
|
Extra |
Additional info: 'Using filesort' (bad), 'Using index' (good — covering index) |
|
filtered |
Percentage of rows that pass the WHERE filter after the access method |
|
Performance Example: A Nigerian bank's TRANSACTION table has 50 million rows. Without an index, SELECT * FROM TRANSACTION WHERE AccountNumber='1234567890' scans all 50M rows (type=ALL). With an index on AccountNumber, MySQL jumps directly to the matching rows (type=ref, rows=15). The index makes this query thousands of times faster. |
Reading List / References
1. Ramakrishnan, R. & Gehrke, J. (2003). Database Management Systems, Chapters 12-15: Query Optimisation. McGraw-Hill.
2. Silberschatz et al. (2020). Database System Concepts, Chapters 15 & 16: Query Processing and Optimisation. McGraw-Hill.
3. MySQL 8.0 Reference Manual. Chapter 8: Optimisation. dev.mysql.com/doc/refman/8.0/en/optimization.html
Activities
|
Self-Assessment Quiz: 1. Name the five stages in the query processing pipeline. 2. What is the role of the query optimiser? What factors does it consider? 3. What is a B-Tree index? When should you create one on a column? 4. You run EXPLAIN on a query and see 'type=ALL' and 'rows=5000000'. What does this mean and how would you fix it? |