Subquery Pitfalls: Why Your MySQL Query Might Be Slow

Address issues like nested loops and Cartesian products.

When working with subqueries in MySQL, nested loops and Cartesian products are common culprits behind sluggish query performance. Here’s a breakdown of these issues and practical solutions:

1. Nested Loop Inefficiency

MySQL defaults to nested-loop joins, which process one row at a time from the outer table against all matching rows in the inner table. While efficient for small datasets, this becomes problematic with:

Large tables: A 50,000-row table joined to another 50,000-row table creates 2.5 billion iterations.

Suboptimal index usage: Missing indexes on join columns force full table scans.

Example:

SELECT * FROM orders
WHERE id IN (
  SELECT order_id FROM order_items 
  WHERE product_id = 100
);        

This might execute as:

For each row in orders → Full scan of order_items        

Fix:

• Add indexes on order_items(order_id, product_id)
• Rewrite with JOIN syntax to leverage index optimizations.

2. Cartesian Product Explosions

Unintended Cartesian products occur when:

• JOIN conditions are missing
• Implicit joins via comma-separated tables lack WHERE clauses

Impact:

• A 10,000-row table joined to another 10,000-row table produces 100 million rows.

Example:

SELECT * FROM users, log_entries; -- No join condition        

Fix:

• Always use explicit JOIN syntax
• Validate query logic with EXPLAIN to spot “Using join buffer (Block Nested Loop)” warnings.

3. Optimizer Misjudgments

The query optimizer might:

• Choose block nested loops (BNL) when hash joins would be better (not natively supported in MySQL)
• Misestimate row counts due to outdated statistics

Workarounds:

SET SESSION optimizer_switch='block_nested_loop=off'; -- For per-query tuning
ANALYZE TABLE orders; -- Refresh statistics        

4. Recursive Query Pitfalls

While recursive CTEs solve hierarchical queries, they:

• Process each hierarchy level sequentially
• Risk infinite loops without termination checks

Optimization:

• Set cte_max_recursion_depth
• Materialize intermediate results:

WITH RECURSIVE org_chart AS (
  SELECT id FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id FROM employees e
  JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT * FROM org_chart;        

Key Diagnostic Tools

EXPLAIN ANALYZE:

• Identifies “Block Nested Loop” in Extra column
• Shows actual vs estimated rows

Slow Query Log:

• Look for high Rows_examined vs Rows_sent ratios

Index Tuning:

• Use composite indexes covering WHERE and JOIN columns
• Avoid over-indexing to prevent write penalties

By addressing these pitfalls through query restructuring, index optimization, and optimizer hints, developers can mitigate subquery-related slowdowns while maintaining MySQL’s strengths in OLTP workloads. For analytical queries, consider complementing MySQL with columnar store like ClickHouse