SQL query optimisation
You read execution plans, understand how B-tree indexes work, the join algorithms and statistics, and speed up slow queries by measuring.
Level 401 · 18 lessons · 15 practice labs
Lessons
Reading the plan
- A query’s journey: parsing, planning, execution
- EXPLAIN and EXPLAIN ANALYZE
- Scan types: sequential, index, index-only
- Join algorithms: nested loop, hash and merge
- Statistics and row estimates
- Short task: interpret five plans
Indexes
- How a B-tree works
- Column order in a composite index
- Covering indexes
- Index-friendly predicates
- The write cost of indexes and too many indexes
- Short task: design an index for a report query
Rewriting queries
- Rewriting subqueries
- Keyset pagination
- The N+1 query problem
- Bulk operations and batch size
- Plan regressions and parameterised queries
- Capstone: the ten worst queries of a slow application
Practice labs
- Spot the full scan in a plan: Find the full table scan in an EXPLAIN output and explain why it was chosen.
- Compare estimated and actual rows: Find the step in EXPLAIN ANALYZE where the row estimate is a hundred times off and fix the plan by refreshing statistics.
- Catch the wrong join algorithm: Find a plan that picks a nested loop for two large tables and make the change that leads to a hash join.
- Add the right index to a slow query: Add an index to a slow lookup by customer email and measure the time before and after.
- Fix a composite index's column order: Find why a report filtering by status and date ignores its index and change the column order.
- Speed up a report with a covering index: Design a covering index so the report never touches the table, and measure the extra write cost.
- Make a function-wrapped column index-friendly: Rewrite a YEAR(date) condition as a range so the index gets used.
- Deal with a leading-wildcard search: Measure a %word% product search and replace it with an indexable alternative.
- Find a hidden type conversion: Use the plan to find a query that skips a text column's index because of a numeric comparison, and fix it.
- Replace OFFSET paging with keyset paging: Convert OFFSET paging that slows down on later pages to keyset paging and compare timings.
- Collapse an N+1 query pattern into one: Replace a script that runs a product query per order with a single joined query.
- Turn a correlated subquery into a join: Rewrite a per-row correlated subquery with a derived table and compare the plans.
- Speed up inserts by batching: Convert a script inserting 50 thousand rows one by one into batched inserts and find the best batch size.
- Break a huge delete into chunks: Make an old-record purge that locks the table for minutes run in small chunks.
- Solve a plan that degrades by parameter: Find and fix the plan problem of a prepared query that is fast for a rare customer and very slow for a big one.
About this course
You read execution plans, understand how B-tree indexes work, the join algorithms and statistics, and speed up slow queries by measuring.
What you will learn
- Reading the plan
- Indexes
- Rewriting queries
Prerequisites
You should have practised the topics of the same path up to level 301.
Lab environment
Lessons run in your own Kali Linux terminal in the browser, connected to a private lab network. Nothing to install.
Frequently asked questions
- Is this course right for my level?
- The course is at level 401; it assumes you know the topics of the lower-level courses.
- Is there anything to install?
- No. Every lesson runs in your own lab environment that opens in the browser.
- How do lessons work?
- Each lesson has 8 to 10 steps and a short quiz. Early steps show the command to type, later steps give less help; if two attempts fail, the solution opens.
- Can I try it for free?
- Yes. The first three lessons of every course are free.