Query Optimization Techniques

Writing a query that returns the right answer is the first step; writing one that returns it quickly, even as the underlying tables grow, is a separate skill. Most real-world query optimization comes down to a small set of recurring techniques — giving the optimizer indexes it can actually use, avoiding patterns that accidentally defeat those indexes, and being deliberate about how much data is fetched and processed.

Technique	Why it helps
Index columns that are frequently filtered, joined, or sorted on	Lets the database avoid scanning the whole table for common access patterns
Avoid SELECT *	Fetches only the columns actually needed, reducing I/O and enabling index-only scans
Avoid wrapping indexed columns in functions inside WHERE	Lets the optimizer use an index instead of forcing a full scan to evaluate the function on every row
Prefer EXISTS over IN for large subqueries	Lets the database stop as soon as one match is found, instead of materializing a full subquery result
Batch large INSERT/UPDATE/DELETE operations	Avoids long-held locks and huge transaction logs from a single massive statement
Filter as early as possible	Reduces the number of rows carried through subsequent joins and aggregations

Avoid SELECT *

Requesting every column, even ones the application never uses, forces the database to read and transfer more data than necessary, and can prevent an index-only scan that would otherwise satisfy the query entirely from the index.

Naming only the columns actually needed

SQL

-- Fetches every column, even unused ones
SELECT * FROM orders WHERE customer_id = 42;

-- Fetches only what the application actually uses
SELECT order_id, order_date, order_total
FROM orders
WHERE customer_id = 42;

Avoid wrapping indexed columns in functions

Applying a function to an indexed column inside WHERE usually forces the database to evaluate that function on every row before it can compare the result, which defeats a plain index on the column — the index is sorted by the raw column value, not by the function's output.

Sargable vs non-sargable filtering on an indexed date column

SQL

-- Non-sargable: wraps order_date in a function, index on order_date can't be used directly
SELECT * FROM orders
WHERE YEAR(order_date) = 2026;

-- Sargable: compares order_date directly against a range, index can be used
SELECT * FROM orders
WHERE order_date >= '2026-01-01' AND order_date < '2027-01-01';

Prefer EXISTS over IN for large subqueries

For large subqueries, EXISTS can stop as soon as it finds the first matching row for a given outer row, whereas some databases handle a large IN list by fully materializing the subquery's result first. On small, well-indexed subqueries the two are often optimized identically, but EXISTS tends to remain efficient as the subquery grows.

EXISTS vs IN for a large subquery

SQL

-- IN: may require the full subquery result to be built first
SELECT customer_name
FROM customers c
WHERE c.customer_id IN (
  SELECT o.customer_id FROM orders o WHERE o.order_total > 1000
);

-- EXISTS: can stop at the first match per outer row
SELECT customer_name
FROM customers c
WHERE EXISTS (
  SELECT 1 FROM orders o
  WHERE o.customer_id = c.customer_id AND o.order_total > 1000
);

Batch large operations

A single UPDATE or DELETE touching millions of rows can hold locks for a long time and generate a huge amount of transaction log activity in one go, which can block other queries and make rollback expensive if something goes wrong. Splitting the operation into smaller batches, each in its own transaction, keeps individual locks short-lived.

Batching a large DELETE instead of running it all at once

SQL

-- Risky as a single statement against a huge table
DELETE FROM logs WHERE created_at < '2020-01-01';

-- Batched: repeat this in a loop until zero rows are affected
DELETE FROM logs
WHERE created_at < '2020-01-01'
LIMIT 10000;

Always verify with EXPLAIN

These techniques are strong general guidelines, but the only way to know whether a specific change actually helped on a specific database, with its specific data distribution, is to check the query plan before and after with EXPLAIN or EXPLAIN ANALYZE. An index that looks correct on paper can still go unused if the optimizer decides a full scan is cheaper for the current data.

Index the columns that are actually filtered, joined, or sorted on in real queries.
Select only the columns needed instead of SELECT *.
Keep indexed columns free of wrapping functions in WHERE clauses so the optimizer can use the index.
Prefer EXISTS over IN for large subqueries.
Batch large write operations to avoid long-held locks and huge transactions.
Confirm every optimization actually changes the query plan with EXPLAIN.