SQL Query Optimization Techniques | Learn Computer Academy

The earlier Beyond the Basics lesson introduced EXPLAIN and indexes briefly. This lesson is a fuller, practical set of techniques for actually making a slow query fast.

Reading an EXPLAIN Plan

EXPLAIN shows how the database actually plans to run a query — which indexes it'll use, in what order it'll touch each table, and roughly how many rows it expects at each step.

EXPLAIN SELECT * FROM orders WHERE customer_id = 42;

-- A "Seq Scan" (sequential scan) means it's reading every row —
-- usually a sign a useful index is missing on customer_id
-- An "Index Scan" means it found and used an index — the goal

Indexing the Right Columns

An index speeds up finding rows by a column's value, at the cost of some extra storage and slightly slower writes. The columns worth indexing are the ones actually used to filter or join — not every column.

-- If this WHERE clause runs constantly and is slow, this index likely helps:
CREATE INDEX idx_orders_customer_id ON orders(customer_id);

SELECT * FROM orders WHERE customer_id = 42;

Common Mistake

Indexing every column "just in case" backfires — each index slows down every INSERT/UPDATE/DELETE on that table, since the index itself needs updating too. Index what queries actually filter or join on, not everything.

Optimizing Joins

Join on indexed columns — an unindexed join column forces the database to scan one side of the join row by row.
Filter before joining where possible — a WHERE clause that reduces one table's rows before the join means less data to combine.
Join only the tables actually needed — an unnecessary JOIN still costs work even if its columns are never selected.

Reducing Subqueries

A subquery re-run for every row of the outer query (a correlated subquery, from the earlier Subqueries lesson) can often be rewritten as a JOIN, which the database can usually plan more efficiently.

-- Slower — the subquery runs once per row of products
SELECT name FROM products p
WHERE (SELECT COUNT(*) FROM order_items oi WHERE oi.product_id = p.id) > 0;

-- Often faster — rewritten as a join
SELECT DISTINCT p.name
FROM products p
JOIN order_items oi ON oi.product_id = p.id;

Selective Projection — Don't SELECT *

Requesting only the columns actually needed, instead of every column, reduces the data the database has to read and send — a small habit that adds up significantly on a large table.

-- Wasteful if only the name is needed
SELECT * FROM products WHERE category = 'electronics';

-- Better
SELECT name FROM products WHERE category = 'electronics';

Technique	What it saves
Index the right columns	Avoids scanning every row to find a match
Join on indexed columns	Avoids a row-by-row scan on one side of a join
Filter before joining	Reduces how much data gets combined
Rewrite correlated subqueries as joins	Avoids re-running the same subquery once per outer row
Select only needed columns	Reduces data read and transferred