10 Real-World MySQL Subquery Optimizations to Boost Performance

During a technical discussion, the author highlights that subqueries in MySQL often lead to performance problems and proposes ten practical alternatives.

Why Subqueries Are Discouraged

Performance issues : MySQL creates temporary tables for subquery results, increasing CPU and I/O and causing slow queries. JOINs can also become inefficient on large data sets.

Index loss : Subqueries may force the optimizer to rewrite the query as a join, preventing effective index usage.

Optimizer complexity : Subqueries make it harder for the optimizer to choose an optimal execution plan.

Data‑transfer overhead : Each subquery returns its result to the outer query, adding unnecessary data movement.

Maintenance cost : JOIN‑based SQL becomes harder to maintain when schemas evolve, especially in large systems.

General Remedies

Application‑level association : Retrieve data with single‑table queries sequentially and combine results in application code.

Replace small subqueries with IN when the result set is tiny.

Use WHERE EXISTS instead of IN for better performance.

Rewrite as JOIN to avoid temporary tables and let the optimizer use indexes.

Case 1: Query All Products with Stock

Original query (subquery) :

SELECT * FROM products WHERE id IN (SELECT product_id FROM inventory WHERE stock > 0);

Optimized query (EXISTS) :

SELECT * FROM products WHERE EXISTS (SELECT 1 FROM inventory WHERE inventory.product_id = products.id AND inventory.stock > 0);

Case 2: Customers from USA

Original query (subquery) :

SELECT * FROM orders WHERE customer_id IN (SELECT customer_id FROM customers WHERE country = 'USA');

Optimized query (EXISTS) :

SELECT * FROM orders WHERE EXISTS (SELECT 1 FROM customers WHERE orders.customer_id = customers.customer_id AND customers.country = 'USA');

Case 3: Replace Subquery with JOIN

Original query (subquery) :

SELECT * FROM orders WHERE customer_id IN (SELECT customer_id FROM customers WHERE country = 'USA');

Optimized query (JOIN) :

SELECT o.* FROM orders o JOIN customers c ON o.customer_id = c.customer_id WHERE c.country = 'USA';

Case 4: Reduce Data Volume in Subquery

Original query (subquery) :

SELECT * FROM orders WHERE customer_id IN (SELECT customer_id FROM customers);

Optimized query (add filter) :

SELECT * FROM orders WHERE customer_id IN (SELECT customer_id FROM customers WHERE active = 1);

Case 5: Index Coverage

Original query (subquery) :

SELECT customer_id FROM customers WHERE country = 'USA';

Optimized steps :

CREATE INDEX idx_country ON customers(country);
SELECT customer_id FROM customers WHERE country = 'USA';

Case 6: Use Temporary Table for Complex Subquery

Original query (subquery) :

SELECT * FROM orders WHERE customer_id IN (SELECT customer_id FROM customers WHERE last_order_date > '2023-01-01');

Optimized query (temporary table) :

CREATE TEMPORARY TABLE temp_customers AS SELECT customer_id FROM customers WHERE last_order_date > '2023-01-01';
SELECT * FROM orders WHERE customer_id IN (SELECT customer_id FROM temp_customers);

Case 7: Window Function Instead of Scalar Subquery

Original query (scalar subquery) :

SELECT employee_id, salary, (SELECT AVG(salary) FROM employees WHERE department_id = e.department_id) AS avg_salary FROM employees e;

Optimized query (window function) :

SELECT employee_id, salary, AVG(salary) OVER (PARTITION BY department_id) AS avg_salary FROM employees;

Case 8: Avoid Full Table Scan

Original query (subquery) :

SELECT * FROM users WHERE username IN (SELECT username FROM orders WHERE order_date = '2024-01-01');

Optimized steps :

CREATE INDEX idx_order_date ON orders(order_date);
SELECT * FROM users WHERE username IN (SELECT username FROM orders WHERE order_date = '2024-01-01');

Case 9: Limit Subquery Result Set

Original query (subquery) :

SELECT * FROM orders WHERE customer_id IN (SELECT customer_id FROM customers WHERE country = 'USA');

Optimized query (LIMIT) :

SELECT * FROM orders WHERE customer_id IN (SELECT customer_id FROM customers WHERE country = 'USA' LIMIT 100);

Case 10: JOIN to Leverage Index

Original query (subquery) :

SELECT * FROM transactions WHERE product_id IN (SELECT product_id FROM products WHERE category = 'Equity');

Optimized query (JOIN) :

SELECT t.* FROM transactions t JOIN products p ON t.product_id = p.product_id WHERE p.category = 'Equity';

These ten cases demonstrate how replacing subqueries with more efficient constructs—IN, EXISTS, JOIN, indexes, temporary tables, window functions, and LIMIT—can significantly improve MySQL query performance. The appropriate technique depends on the specific data distribution and business requirements.