-- Correlated subquery without index - VERY SLOW!

SELECT

c.name,

(SELECT COUNT(*)

FROM orders

WHERE customer_id = c.id) AS order_count

FROM customers c;

-- For 5000 customers with no index on orders(customer_id):

-- 5000 full table scans of orders table

-- Query time: 12000ms

-- Add index first

CREATE INDEX idx_orders_customer ON orders(customer_id);

-- Now query is fast

SELECT

c.name,

(SELECT COUNT(*)

FROM orders

WHERE customer_id = c.id) AS order_count

FROM customers c;

-- Query time: 280ms (43x faster!)

-- Or use JOIN (even faster)

SELECT

c.name,

COUNT(o.id) AS order_count

FROM customers c

LEFT JOIN orders o ON o.customer_id = c.id

GROUP BY c.id, c.name;

-- Query time: 95ms

Correlated subquery executes once per customer. Without index, each execution scans entire orders table. For 5000 customers and 50,000 orders, that is 250 million row scans! Add index on foreign key column. Or use JOIN which is usually faster.

Always index columns used in correlated subquery WHERE clause. Test with EXPLAIN to verify index usage.

Critical

Extremely slow query due to missing index

-- Multiple correlated subqueries on same table

SELECT

c.name,

(SELECT COUNT(*) FROM orders WHERE customer_id = c.id) AS order_count,

(SELECT SUM(total) FROM orders WHERE customer_id = c.id) AS total_spent,

(SELECT MAX(order_date) FROM orders WHERE customer_id = c.id) AS last_order

FROM customers c;

-- 3 subqueries × 5000 customers = 15,000 executions