Understanding Correlated Subquery Execution Plans in MySQL

Analyze Performance of a Correlated Subquery with EXPLAIN

Write a MySQL query that uses EXPLAIN to analyze a query with a correlated subquery that finds orders with a TotalAmount greater than the average order amount for each customer.

Solution:

-- Analyze a correlated subquery using EXPLAIN.
EXPLAIN
SELECT OrderID, TotalAmount
FROM Orders o
WHERE TotalAmount > (
    SELECT AVG(TotalAmount) FROM Orders
    WHERE CustomerID = o.CustomerID
);

Explanation:

• Purpose of the Query:
• To examine the execution plan for a query involving a correlated subquery.
• Helps in identifying performance issues when subqueries execute per row.
• Key Components:
• Correlated Subquery: Computes the average TotalAmount for each customer.
• WHERE TotalAmount > (subquery) : Filters orders exceeding the calculated average.
• Real-World Application:
• Useful in analytical queries where individual performance metrics are compared to averages.

Notes:

• Correlated subqueries can be slow on large datasets; consider alternative strategies such as JOINs if needed.
• EXPLAIN assists in determining if query refactoring is necessary.

For more Practice: Solve these Related Problems:

• Write a MySQL query that uses EXPLAIN to analyze a query with a correlated subquery that finds products with a price greater than the average price for each category.
• Write a MySQL query that uses EXPLAIN to analyze a query with a correlated subquery that finds projects with a budget greater than the average budget for each department.
• Write a MySQL query that uses EXPLAIN to analyze a query with a correlated subquery that finds customers with a total order amount greater than the average order amount for each city.
• Write a MySQL query that uses EXPLAIN to analyze a query with a correlated subquery that finds transactions with an amount greater than the average transaction amount for each account.

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous MySQL Exercise: Optimize Query with Covering Index for Aggregation.
Next MySQL Exercise: Optimize ORDER BY Query using Indexed Column.