SQL Optimization Examples

Explore these examples to understand common SQL performance issues and their solutions.

Common Basic Issues

1. SELECT * (Wildcard Select)
❌ Problematic Query:
SELECT * FROM users 
WHERE age > 25 
ORDER BY name;
✅ Optimized Query:
SELECT id, name, email, age 
FROM users 
WHERE age > 25 
ORDER BY name;
Issue: SELECT * retrieves all columns, including unnecessary ones.
Solution: Specify only the columns you need.
2. Missing LIMIT Clause
❌ Problematic Query:
SELECT id, name, email 
FROM users 
ORDER BY created_at DESC;
✅ Optimized Query:
SELECT id, name, email 
FROM users 
ORDER BY created_at DESC 
LIMIT 100;
Issue: No LIMIT clause may return large result sets.
Solution: Add LIMIT to control result size.
3. Functions in WHERE Clause
❌ Problematic Query:
SELECT * FROM users 
WHERE UPPER(name) = 'JOHN';
✅ Optimized Query:
SELECT * FROM users 
WHERE name = 'john';
Issue: Functions in WHERE clause prevent index usage.
Solution: Avoid functions or create function-based indexes.

Join Optimization Examples

1. CROSS JOIN Issues
❌ Problematic Query:
SELECT * FROM users 
CROSS JOIN orders;
✅ Optimized Query:
SELECT u.name, o.order_date 
FROM users u 
JOIN orders o ON u.id = o.user_id;
Issue: CROSS JOIN creates Cartesian product.
Solution: Use explicit JOIN conditions.
2. Multiple Joins
❌ Complex Query:
SELECT u.name, o.order_date, p.name 
FROM users u 
JOIN orders o ON u.id = o.user_id 
JOIN order_items oi ON o.id = oi.order_id 
JOIN products p ON oi.product_id = p.id 
JOIN categories c ON p.category_id = c.id;
✅ Optimized with CTE:
WITH user_orders AS (
    SELECT u.name, o.order_date, o.id 
    FROM users u 
    JOIN orders o ON u.id = o.user_id
)
SELECT uo.name, uo.order_date, p.name 
FROM user_orders uo 
JOIN order_items oi ON uo.id = oi.order_id 
JOIN products p ON oi.product_id = p.id;
Issue: Too many joins can be hard to optimize.
Solution: Use CTEs or temporary tables.

Index Optimization Examples

1. Missing Indexes on WHERE Columns
❌ Query without Index:
SELECT * FROM users 
WHERE email = 'john@example.com';
✅ Recommended Index:
CREATE INDEX idx_users_email 
ON users(email);
Issue: No index on email column causes full table scan.
Solution: Create index on frequently queried columns.
2. Composite Indexes
❌ Multiple Single Indexes:
SELECT * FROM orders 
WHERE user_id = 123 
AND status = 'pending' 
AND created_at > '2024-01-01';
✅ Composite Index:
CREATE INDEX idx_orders_user_status_date 
ON orders(user_id, status, created_at);
Issue: Multiple single indexes are less efficient.
Solution: Create composite indexes for multi-column queries.

Subquery Optimization Examples

1. IN Subquery vs JOIN
❌ Subquery Approach:
SELECT * FROM users 
WHERE id IN (
    SELECT user_id FROM orders 
    WHERE total_amount > 1000
);
✅ JOIN Approach:
SELECT DISTINCT u.* 
FROM users u 
JOIN orders o ON u.id = o.user_id 
WHERE o.total_amount > 1000;
Issue: Subqueries can be less efficient than JOINs.
Solution: Use JOINs when possible.
2. Correlated Subquery
❌ Correlated Subquery:
SELECT u.name, 
    (SELECT COUNT(*) FROM orders o 
     WHERE o.user_id = u.id) as order_count 
FROM users u;
✅ JOIN with Aggregation:
SELECT u.name, 
    COUNT(o.id) as order_count 
FROM users u 
LEFT JOIN orders o ON u.id = o.user_id 
GROUP BY u.id, u.name;
Issue: Correlated subqueries execute for each row.
Solution: Use JOINs with GROUP BY.

Aggregation Optimization Examples

1. GROUP BY Optimization
❌ Without Index:
SELECT category_id, 
    COUNT(*) as product_count 
FROM products 
GROUP BY category_id;
✅ With Index:
CREATE INDEX idx_products_category 
ON products(category_id);

SELECT category_id, 
    COUNT(*) as product_count 
FROM products 
GROUP BY category_id;
Issue: GROUP BY without index requires sorting.
Solution: Create index on GROUP BY columns.
2. HAVING vs WHERE
❌ Using HAVING:
SELECT category_id, 
    COUNT(*) as product_count 
FROM products 
GROUP BY category_id 
HAVING COUNT(*) > 10;
✅ Using WHERE (if possible):
SELECT category_id, 
    COUNT(*) as product_count 
FROM products 
WHERE category_id IS NOT NULL 
GROUP BY category_id 
HAVING COUNT(*) > 10;
Issue: HAVING filters after aggregation.
Solution: Use WHERE to filter before aggregation when possible.

Try These Examples

Copy any of the examples above and paste them into the SQL analyzer to see the optimization suggestions!

Back to Analyzer