Master MySQL CTE, Window Functions, and Query Optimization with Real‑World Examples

Introduction

MySQL 8 includes advanced SQL features such as Common Table Expressions (CTE) and window functions. These constructs make complex aggregations, hierarchical queries, and ranking operations easier to write and maintain. In production environments, applying systematic query‑optimization techniques is essential to achieve low latency and minimal resource consumption.

Practical Cases

2.1 Using CTE for Readable SQL

A CTE defines a named temporary result set that can be referenced multiple times within the same statement, improving readability and avoiding duplicated subqueries.

WITH department_avg AS (
    SELECT department, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department
)
SELECT e.name, e.department, e.salary
FROM employees e
JOIN department_avg d ON e.department = d.department
WHERE e.salary > d.avg_salary;

2.2 Analyzing Data with Window Functions

Window functions compute a value across a set of rows while preserving each row’s identity. They are useful for ranking, running totals, and moving averages. The following example uses ROW_NUMBER() to assign two different row numbers per country.

SELECT
  year,
  country,
  product,
  profit,
  ROW_NUMBER() OVER (PARTITION BY country) AS row_num1,
  ROW_NUMBER() OVER (PARTITION BY country ORDER BY year, product) AS row_num2
FROM sales;

2.3 Combining CTE and Window Functions

CTE and window functions can be combined to solve typical “top‑N per group” problems. The example returns the three highest‑salary employees in each department.

WITH ranked_employees AS (
  SELECT name, department, salary,
         RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rnk
  FROM employees
)
SELECT name, department, salary
FROM ranked_employees
WHERE rnk <= 3;

2.4 Query Optimization Techniques

Index creation – create indexes on columns that appear frequently in WHERE, JOIN or ORDER BY clauses. Example: CREATE INDEX idx_salary ON employees(salary); Avoid SELECT * – select only the columns you need to reduce I/O and memory usage. SELECT name, salary FROM employees; Prefer EXISTS over IN for large subqueries – EXISTS stops scanning after the first match.

-- slower
SELECT name FROM employees e
WHERE department_id IN (SELECT id FROM departments WHERE active = 1);

-- faster
SELECT name FROM employees e
WHERE EXISTS (
    SELECT 1 FROM departments d
    WHERE e.department_id = d.id AND d.active = 1
);

Use EXPLAIN ANALYZE to inspect the execution plan and verify index usage.

EXPLAIN ANALYZE SELECT year, country, product, profit,
    ROW_NUMBER() OVER (PARTITION BY country) AS row_num1,
    ROW_NUMBER() OVER (PARTITION BY country ORDER BY year, product) AS row_num2
FROM sales;

Limit result sets with WHERE or LIMIT to avoid full‑table scans.

SELECT name, department, salary
FROM employees
WHERE department = '研发部'
LIMIT 3;

Materialized views for repeated heavy aggregations – store the result of a complex query and refresh it on demand. The following Oracle example creates a materialized view that aggregates employee counts and average salaries per department.

CREATE MATERIALIZED VIEW mv_employee_department_summary
REFRESH FORCE ON DEMAND AS
SELECT DEPTNO,
       COUNT(*) AS total_employees,
       AVG(SAL) AS avg_salary
FROM SCOTT.EMP
GROUP BY DEPTNO;

Refresh the view to keep it in sync with the base tables:

BEGIN
  DBMS_MVIEW.REFRESH('mv_employee_department_summary', 'C'); -- full refresh
  -- or 'F' for fast refresh if supported
END;

These examples demonstrate how CTEs, window functions, and systematic optimization can make MySQL queries more readable, maintainable, and performant.