How I Learned to Optimize SQL Queries

Why I Started Digging Into This

Like a lot of developers, I learned SQL well enough to get data in and out — but not well enough to know why a query that worked fine on my local machine crawled in production once the table hit a few million rows. That gap is what pushed me to actually learn optimization instead of just writing queries that "worked."

1. Start With EXPLAIN (or EXPLAIN ANALYZE)

The single biggest shift in how I write SQL was learning to read query execution plans.

sql

EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 42;

This tells you how the database is actually satisfying the query — whether it's doing a full table scan, using an index, sorting in memory, etc. If you see Seq Scan on a large table where you expected an index lookup, that's your first clue something's off.

2. Indexes Are Not Free — But They're Usually Worth It

Adding an index on frequently filtered or joined columns was the highest-leverage change I made early on.

sql

CREATE INDEX idx_orders_customer_id ON orders(customer_id);

A few things I learned the hard way:

• Indexes speed up reads but slow down writes (inserts/updates), so don't index everything.
• A composite index's column order matters — (customer_id, created_at) is not interchangeable with (created_at, customer_id).
• An index is only useful if the query planner can actually use it — functions wrapped around a column (WHERE LOWER(email) = ...) often block index usage unless you index the expression itself.

3. Avoid SELECT *

It seems minor, but pulling every column when you only need three does real damage at scale — more I/O, more memory, and it can prevent "covering index" optimizations where the database could've answered the query from the index alone.

sql

-- Instead of
SELECT * FROM orders WHERE customer_id = 42;

-- Do
SELECT id, status, total FROM orders WHERE customer_id = 42;

4. Watch Out for N+1 Queries

This one isn't about a single query — it's about a pattern. Looping over results and firing off a new query per row (common in ORMs) quietly turns one query into hundreds.

sql

-- Instead of looping and querying per customer_id
SELECT * FROM orders WHERE customer_id IN (1, 2, 3, 4, 5);

Batching like this turned one of my slowest endpoints from ~40 queries per request into 1.

5. Be Careful With JOINs on Unindexed Columns

Joining large tables without indexes on the join keys is one of the fastest ways to tank performance. Before optimizing anything else, I check that every column used in a JOIN ... ON clause is indexed.

6. Measure, Don't Guess

The habit that made the biggest difference wasn't a technique — it was discipline: always measure before and after.

• Time the query (EXPLAIN ANALYZE gives actual execution time, not just a plan)
• Compare row counts scanned vs. rows returned — a huge gap usually means a missing or unused index
• Re-run under realistic data volume, not a mostly-empty dev database

Key Takeaway

Query optimization isn't about memorizing tricks — it's about understanding what the database is actually doing under the hood and giving it what it needs (the right indexes, the right shape of query) to do less work. EXPLAIN ANALYZE became my most-used tool almost overnight.