week 9
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? |