Query Optimization Techniques
What you'll learn: How to apply practical strategies that make your SQL queries run faster and more efficiently.
The Core Idea
Now that you can read execution plans and understand indexes, it's time to actively rewrite queries for better performance. Query optimization isn't about making queries work—it's about making them work smart. Small changes in how you structure SQL can dramatically reduce execution time and resource usage.
Practical Optimization Strategies
Rewrite Subqueries as Joins
Subqueries in the SELECT or WHERE clause often execute repeatedly. Convert them to joins when possible:
-- Slower: subquery runs for each row
SELECT name, (SELECT COUNT(*) FROM orders WHERE customer_id = c.id)
FROM customers c;
-- Faster: join runs once
SELECT c.name, COUNT(o.id)
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name;
Avoid SELECT *
Only retrieve columns you actually need. SELECT * pulls unnecessary data, wastes network bandwidth, and prevents index-only scans:
-- Wasteful
SELECT * FROM customers WHERE country = 'USA';
-- Efficient
SELECT id, name, email FROM customers WHERE country = 'USA';
Limit Result Sets
Always use LIMIT when you don't need all rows—especially during testing or pagination:
SELECT name, signup_date FROM customers ORDER BY signup_date DESC LIMIT 100;
Use Appropriate Join Types
Choose the right join for your needs. INNER JOIN is faster than LEFT JOIN when you don't need unmatched rows. Avoid CROSS JOIN unless absolutely necessary.
Key Takeaway: Query optimization is about conscious choices—rewrite subqueries as joins, select only necessary columns, limit result sets, and pick the right join type to minimize the database's workload.