Search⌘ K
AI Features
Home
Courses
SQL Interview Preparation – Advanced Level

Answer: Execution Plans

Explore how to interpret MySQL execution plans by comparing different query strategies with EXPLAIN and EXPLAIN ANALYZE. Learn to identify join types, index usage, and performance metrics to optimize SQL queries effectively.

We'll cover the following...

Solution

The solution is given below:

MySQL 8.0
-- Version A: EXISTS based plan
EXPLAIN ANALYZE
SELECT  p.ProductID, p.ProductName
FROM    Products AS p
WHERE   EXISTS (
          SELECT  1
          FROM    Order_Details AS od
          WHERE   od.ProductID = p.ProductID
        );
-- Version B: JOIN based plan (distinct products that appear in Order_Details)
EXPLAIN ANALYZE
SELECT  DISTINCT p.ProductID, p.ProductName
FROM    Products AS p
JOIN    Order_Details AS od
        ON p.ProductID = od.ProductID;
-- Optional: Run each SELECT without EXPLAIN to confirm they return the same set of products

Run each SELECT without EXPLAIN ANALYZE to confirm both versions return the same product set before choosing between their execution plans based on performance.

Explanation

The explanation of the solution code is given below:

  • Lines 2–9: Define Version A of the query using EXISTS to return products that appear in Order_Details and request its execution plan with timing.

    • Line 2: Prepend EXPLAIN ANALYZE so MySQL executes the query and reports the plan with actual timing and row counts.

    • Lines 3–4: Select p.ProductID and p.ProductName from Products AS p, setting up the outer table that drives the semijoin.

    • Lines 5–9: Use a correlated EXISTS subquery on Order_Details AS od filtered by od.ProductID = p.ProductID, so each product is kept only if at least one matching order detail exists, which ...