Learn SQL Query Optimization Tips & Tricks
Window function partitioning, recursive CTEs, optimizing LEFT JOIN vs EXISTS, and eliminating non-SARGable bottlenecks.
Ameer Abdullah
Data Science Graduate · AI/ML & Data Science
Concept in Simple Words: Mastering SQL queries requires understanding the database query engine's logical execution pipeline. Rather than writing queries through trial and error, professional data engineers and backend developers reason about how the query planner processes table relations, builds hash tables, navigates B-Tree indexes, and computes analytical partitions.
Deep Walkthrough & Code: Consider an analytical reporting requirement to find the top 2 highest-paid employees in every department, while also calculating the percentage difference from their department average:
WITH RankedSalaries AS (
SELECT
employee_id,
department_id,
salary,
DENSE_RANK() OVER (
PARTITION BY department_id
ORDER BY salary DESC
) AS rank_in_dept,
AVG(salary) OVER (
PARTITION BY department_id
) AS dept_avg_salary
FROM employees
)
SELECT
employee_id,
department_id,
salary,
dept_avg_salary,
ROUND(((salary - dept_avg_salary) / dept_avg_salary) * 100.0, 2) AS pct_above_avg
FROM RankedSalaries
WHERE rank_in_dept <= 2
ORDER BY department_id, rank_in_dept;Step-by-Step Dry Run: Let us trace how the database executes this query: - Phase 1 (FROM & PARTITION): The engine scans `employees` and groups rows into departmental partitions in memory. - Phase 2 (Window Computation): For each department partition, `DENSE_RANK()` calculates dense rankings ordered by salary descending without skipping ranks on ties. Simultaneously, `AVG(salary)` computes the partition average without collapsing the rows. - Phase 3 (CTE Filter): The outer query references `RankedSalaries` and applies `WHERE rank_in_dept <= 2`, filtering out lower-ranked rows. - Phase 4 (Projection & Sort): The arithmetic expression computes `pct_above_avg`, and rows are sorted by department and rank.
Production Level Issue & Fix: A frequent performance disaster in production queries is using non-SARGable functions in WHERE clauses, such as `WHERE YEAR(created_at) = 2026`. This forces the engine to perform an exhaustive full table scan on millions of rows because it must evaluate the `YEAR()` function for every row. The optimized fix is rewriting the predicate as an indexed range scan: `WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01'`, enabling sub-millisecond B-Tree index seeks.
Practice what you just learned
Apply these concepts in PyCodeIt's interactive sandbox with real problems.
Open SQL Sandbox