SQL Tutorial 12: Best Practices and Optimization

Slow SQL rarely starts with a villainous database. It starts with a SELECT that was fine on 2,000 rows, then quietly met 20 million. Or with a join that multiplied grain. Or with a filter written in a way that cannot use an index even though a perfect index exists. Optimization is less about secret hints and more about clear intent, honest grain, and an EXPLAIN mindset: look at how the engine plans to work, then change the query (or the schema) with evidence.

This is Part 12 of the SQL series. You already write joins, subqueries, CTEs, and windows. Now you make them kinder to production systems and future maintainers. The path lives on the SQL series page, with more skills on the Learn hub.

What you will learn

An EXPLAIN mindset: plans before folklore
Why select lists and grain discipline beat SELECT *
Sargable filters that keep columns visible to indexes
Index awareness without “index everything”
Join and CTE habits that avoid accidental explosions
How to avoid premature complexity while still shipping better SQL

Optimization is a loop, not a spell

Example output:

Healthy loop:

State the question and required grain.
Write a correct query on a sample.
Measure (runtime, rows touched, plan).
Change one thing at a time.
Re-measure and keep the simpler option when speeds match.

Wrong loop: paste three blog tricks, add five indexes, wrap everything in nested subqueries, and ship a result you cannot explain. Correctness first. Then clarity. Then speed. Speed without correctness is just a faster way to look wrong in a meeting.

The EXPLAIN mindset

Most engines can show a query plan: the steps chosen to filter, join, aggregate, and sort. Commands differ (EXPLAIN, EXPLAIN ANALYZE, visual tools in GUIs). You do not need to memorize every node type on day one. You do need the habit of asking:

Does this plan scan an entire large table when I expected a selective lookup?
Are joins in a sensible order for the filters I have?
Is the engine sorting a huge intermediate set?
Do row estimates look wildly different from reality (stale stats are a Part 13 topic)?

-- Shape varies by engine; idea is universal
EXPLAIN
SELECT
  c.region,
  SUM(o.amount) AS paid_revenue
FROM orders AS o
INNER JOIN customers AS c
  ON c.customer_id = o.customer_id
WHERE o.status = 'paid'
  AND o.order_date >= '2024-05-01'
GROUP BY c.region;

When you change a filter or add an index, run EXPLAIN again. Plans are evidence. Opinions are optional. On tiny toy tables the plan may always say “just scan everything,” which is correct. Practice the habit now so it is muscle memory when tables grow.

Select lists: ask only for what you need

SELECT * is fine for exploration on small tables. It is a bad default for production queries, views that feed dashboards, and any path that moves data across a network.

-- Exploration (ok in a sandbox)
SELECT *
FROM orders
WHERE order_id = 101;

-- Production-shaped (better)
SELECT
  order_id,
  customer_id,
  order_date,
  amount,
  status
FROM orders
WHERE order_id = 101;

Why explicit lists win:

Less data shipped to the client or BI tool
Fewer breakages when someone adds a wide column later
Clearer contracts for people reading your SQL
Better chance to use covering indexes in some engines

Same idea for joins: do not select every column from both sides “just in case.” Project early when intermediate CTE results are wide and only a few columns feed the next step.

Filter early, filter honestly

Put selective filters as early as readability allows. In CTEs, a first step that keeps only paid orders for the date range is easier to reason about and often cheaper than joining everything then filtering at the end. Engines can push predicates around, but humans still benefit from obvious early filters.

WITH paid_recent AS (
  SELECT
    order_id,
    customer_id,
    amount,
    order_date
  FROM orders
  WHERE status = 'paid'
    AND order_date >= '2024-05-01'
)
SELECT
  c.region,
  SUM(p.amount) AS paid_revenue
FROM paid_recent AS p
INNER JOIN customers AS c
  ON c.customer_id = p.customer_id
GROUP BY c.region
ORDER BY paid_revenue DESC;

Sargable filters: keep columns usable

Sargable roughly means the filter is written so the engine can seek into an index on that column. Exact rules vary, but a durable habit is: avoid wrapping the filtered column in a transforming function when a range or equality on the raw column works.

Prefer range on the date column:

SELECT order_id, amount
FROM orders
WHERE order_date >= '2024-05-01'
  AND order_date < '2024-06-01';

Be careful with patterns like transforming every row’s column just to compare to a constant, when you could express the same logic as a range. Leading wildcards in LIKE '%smith' often prevent simple index seeks. LIKE 'smith%' can be friendlier. Null logic, OR chains across many columns, and mismatched types (comparing text to numbers with silent casts) also cause pain.

When you must use a function for correctness, accept the cost or consider a persisted generated column/index strategy with a DBA. Do not “optimize” by removing necessary business logic.

Index awareness (from Part 9, applied)

Recall: indexes help selective lookups and joins, cost write overhead, and must match real filters. For the toy schema at scale you would watch:

orders.customer_id for joins to customers
orders.order_date for range reports
composite (status, order_date) if almost every query filters paid plus a date window
primary keys for point lookups

Example output:

Analyst-friendly moves when you cannot CREATE INDEX yourself:

Write filters that could use existing indexes.
Avoid selecting huge row sets into BI tools when a warehouse aggregate would do.
File an index request with the slow query text, runtime, and EXPLAIN output attached.
Prefer curated aggregates (daily revenue tables) for dashboards that do not need raw lines.

Joins, fanout, and counting disasters

Performance and correctness meet at fanout. If one customer has 50 orders and you join before counting customers, you can count 50. That is not only wrong; it is also more work. Aggregate to the grain you need, or use EXISTS when you only need membership.

-- Dangerous if you later COUNT(c.customer_id) expecting unique customers
SELECT c.customer_id, c.name, o.order_id, o.amount
FROM customers AS c
INNER JOIN orders AS o
  ON o.customer_id = c.customer_id
WHERE o.status = 'paid';

-- Safer unique customer count with paid orders
SELECT COUNT(*) AS customers_with_paid_orders
FROM customers AS c
WHERE EXISTS (
  SELECT 1
  FROM orders AS o
  WHERE o.customer_id = c.customer_id
    AND o.status = 'paid'
);

When you need both detail and totals, window functions from Part 10 often beat join-then-group gymnastics that duplicate logic.

CTEs, subqueries, and “readable vs fast”

Write CTEs for humans. If a plan shows a CTE being materializing expensively, you might inline, combine steps, or temp-stage a result for a heavy batch. Do not avoid CTEs on principle because a social media thread said they are slow. Measure on your engine and data. Premature micro-optimization of readable SQL is how teams inherit uneditable monsters.

Same story for window functions: they can be expensive on huge partitions, and they can also replace multiple self-joins cleanly. Compare plans when it matters. Prefer the version a teammate can modify under pressure.

Avoid premature complexity

Complexity tax examples:

Six nested layers when two CTEs would do
Dynamic SQL generation for a static weekly report
Hints and forced join orders copied from another product
Caching layers for a query that runs once a month
UDF forests for simple CASE logic (Part 11)

Simple checklist before adding cleverness:

Is the query correct on a known sample?
Is the grain documented?
Is the select list tight?
Are filters selective and sargable where possible?
Did EXPLAIN show a real problem?
Is there already a curated table or view that answers this?

Rule of thumb: Make the query obviously right. Then make the plan cheap enough. Stop when the next trick would make either worse.

Worked story: from wide scan to intentional report

Version A (common first draft):

SELECT *
FROM customers AS c
LEFT JOIN orders AS o
  ON o.customer_id = c.customer_id
WHERE c.region = 'East';

Problems: wide select list, possible huge order history, unclear grain for a revenue metric, filter only on customers so every East customer’s entire order history may stream out.

Version B (report-shaped):

SELECT
  c.customer_id,
  c.name,
  COUNT(o.order_id) AS paid_orders,
  COALESCE(SUM(o.amount), 0) AS paid_revenue
FROM customers AS c
LEFT JOIN orders AS o
  ON o.customer_id = c.customer_id
 AND o.status = 'paid'
 AND o.order_date >= '2024-05-01'
WHERE c.region = 'East'
GROUP BY c.customer_id, c.name
ORDER BY paid_revenue DESC;

Improvements: explicit columns, paid and date predicates on the join to keep the left-customer grain, aggregation to the grain the meeting needs. On large data you would confirm indexes support status, order_date, and customer_id, and you would EXPLAIN. On toy data you still practice the shape.

Partner tools: warehouses, BI, and Python

Push heavy filtering and aggregation toward the database or warehouse. Pull skinny results into BI or Python. That is the partnership theme from the Python series. Do not extract ten million rows to compute a sum pandas could have skipped if SQL had done it. Conversely, do not force SQL to do awkward reshaping that is clearer in a small Python step after a tight extract.

Quality still bounds performance work. A fast pipeline that double-counts refunds is a liability. Keep data quality checks in the loop when you change query logic for speed.

Pagination, LIMIT, and “just give me top 10”

Analysts often slap LIMIT 10 on a heavy query to make exploration snappy. That is fine for sampling when you understand whether the engine can stop early. It is not a substitute for a correct top-N definition. Without ORDER BY, “top 10” means “some 10.” With ORDER BY on a non-selective expression, the engine may still sort a huge set before limiting.

For true top-N per group, prefer the window pattern from Part 10 (ROW_NUMBER then filter) or engine-specific quality-of-life features you have measured. For pagination in applications, deep OFFSET pages get expensive as offsets grow, because many engines still walk prior rows. Keyset pagination (continue after the last seen ordered key) scales better when you control the app. Analysts building one-off reports rarely need deep offsets; they need honest ordering and a tight filter.

-- Exploration sample with explicit order (still not a business "top customers" rule)
SELECT
  order_id,
  customer_id,
  amount
FROM orders
WHERE status = 'paid'
ORDER BY amount DESC, order_id
LIMIT 10;

Combine LIMIT with the rest of this part’s habits: select only needed columns, filter early, and check the plan if even the limited query is slow. Sometimes the limit does not save you because the planner builds a large intermediate result first. EXPLAIN tells that story faster than guessing.

Common mistakes

Tuning before defining grain and correctness tests.
SELECT * in production paths.
Non-sargable filters that disable perfectly good indexes.
Join fanout mistaken for “the database is slow.”
Adding indexes without measuring, then wondering why writes slowed down.
Copying vendor hints into engines that ignore or punish them.
Optimizing a monthly query as if it were a hot path on every page load.

How to practice

Take one of your Part 10 CTE queries, tighten the select lists, and add early filters.
Run EXPLAIN before and after a filter change on whatever engine you use.
Rewrite a non-sargable date filter into a range and compare plans on a larger sample if you have one.
Create a short “query review” checklist for your team and apply it to a real dashboard SQL string.
Find one SELECT * in a shared repo (or your notes) and replace it with explicit columns.

Quick recap

Use EXPLAIN and measurements, not folklore, to guide changes.
Select only needed columns; define grain before tuning.
Write sargable filters and index-aware joins when data is large.
Watch fanout: wrong counts often look like “performance issues.”
Prefer simple correct SQL; add complexity only when evidence demands it.

Part 13 closes the series with maintenance: backups, slow query hygiene, statistics, vacuum/analyze concepts, grants, and on-call habits for analysts who inherit databases. Continue on the SQL series.

Sources

Research and further reading used for this article: