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

Answer: Optimizer Hints

Explore how optimizer hints influence MySQL's query planning to enhance performance. Understand the use of hints like SEMIJOIN(FIRSTMATCH) to optimize semijoin execution, enabling efficient early row matching and reducing unnecessary scans. This lesson helps you apply targeted query optimizations for better execution plans and runtime behavior.

We'll cover the following...

Solution

The solution is given below:

MySQL 8.0
SELECT   /*+ SEMIJOIN(FIRSTMATCH) */
         s.SupplierID, s.SupplierName
FROM     Suppliers AS s
WHERE    EXISTS (
         SELECT 1
         FROM   Product_Suppliers AS ps
         JOIN   Order_Details AS od
                ON od.ProductID = ps.ProductID
         JOIN   Orders AS o
                ON o.OrderID = od.OrderID
         WHERE  ps.SupplierID = s.SupplierID
           AND  o.LateDelivery = TRUE
)
ORDER BY s.SupplierName;

Explanation

The explanation of the solution code is given below:

  • Lines 1–2: Add the optimizer hint /*+ SEMIJOIN(FIRSTMATCH) */ to suggest an efficient semijoin execution strategy for the correlated EXISTS subquery. Select SupplierID and SupplierName which represent the suppliers that will be returned when at least one late delivery exists.

  • Line 3: This reads from Suppliers as the outer table, assigning it the alias s.

  • Lines 4–13: These use a correlated EXISTS subquery to check if each supplier has at least one order marked as late.

    • Line 5: This returns a constant value (SELECT 1) because EXISTS only checks whether a row is found.

    • Line 6: This starts from Product_Suppliers ...