SQL Query Optimization: Best Practices for Performance

We've all been there: a feature works perfectly with test data, but the moment it hits production with millions of rows, the API response time spikes. Optimizing SQL isn't just about making things "faster"; it's about efficient resource utilization and ensuring your application can scale.

1. Stop Using SELECT *

It is tempting to just grab everything, but SELECT * is a major anti-pattern. Every additional column you fetch requires more I/O, more memory, and more network bandwidth. If you only need the user_id and email, fetch only those.

-- ❌ Avoid
SELECT * FROM users WHERE status = 'active';

-- ✅ Preferred
SELECT user_id, email, full_name FROM users WHERE status = 'active';

2. The Power of Indexing (and Its Pitfalls)

Indexes are like a book's table of contents. Without them, the database has to perform a "Full Table Scan"—reading every single row to find a match. However, indexes aren't free. Each index slows down INSERT, UPDATE, and DELETE operations because the index itself must be updated.

Tip: Index the columns you frequently use in WHERE clauses and JOIN conditions.

3. Understand JOIN vs. Subqueries

While modern optimizers handle subqueries better than they used to, a JOIN is often more performant. Subqueries can sometimes lead to uncorrelated executions that run for every row in the outer query.

-- Use JOIN for better execution plans
SELECT o.order_id, c.customer_name 
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id;

4. Use EXPLAIN to Trace Query Plans

Don't guess—measure. Every major SQL engine (PostgreSQL, MySQL, SQL Server) has an EXPLAIN command. It shows you exactly how the database intends to run your query. Look for keywords like Sequential Scan (bad for large tables) vs. Index Scan (good).

5. Filter Early with WHERE

Reduce the result set as early as possible. If you use HAVING to filter data, remember that HAVING filters *after* aggregation. Use WHERE to filter rows before they are grouped or aggregated.

6. Batch Your Updates

Updating 10,000 rows in 10,000 separate transactions is thousands of times slower than updating them in a single batch. Wrap bulk operations in a single transaction to minimize the overhead of log commits.