Query Optimization Basics in SQL
Introduction
SQL query optimization is the disciplined process of reducing the work required to produce a correct result. It can improve response time, CPU consumption, memory use, disk and cache reads, temporary storage, network traffic, lock duration, and total system throughput. The goal is not merely to make one test run faster; it is to improve an important workload without changing its meaning or damaging other operations.
Optimization combines two responsibilities. Developers express the request clearly, retrieve only necessary data, and provide suitable schema and indexes. The database optimizer explores valid access paths, join orders, join methods, aggregations, and transformations, estimates their costs, and selects a plan. Effective tuning works with that process instead of relying on isolated rules.
What Is Query Optimization?
A declarative SQL statement describes the required result rather than a complete physical procedure. The database may satisfy it by scanning a table, navigating an index, combining indexes, changing join order, selecting a nested-loop, hash, or merge join, pushing filters, or transforming subqueries. Query optimization is the selection and improvement of those choices.
SELECT employee_id, employee_name
FROM employees
WHERE department_id = 10;
CREATE INDEX ix_employees_department
ON employees (department_id);
The index gives the optimizer an additional access path, but does not force its use. If most employees belong to department 10, a scan may still have the lower estimated cost.
Why Query Optimization Matters
Applications repeatedly search products, retrieve customer records, post transactions, build dashboards, update inventory, and generate reports. An inefficient statement multiplied by thousands of executions can dominate a database even when each execution looks only moderately slow.
Poor plans can create excessive reads, CPU pressure, memory spills, timeouts, blocking, and replica lag. Good optimization reduces unnecessary work, increases concurrency, delays hardware expansion, and makes response times more predictable. Business priority matters: a checkout query usually deserves attention before a rarely opened administrative report.
A Library Analogy
Finding a book by inspecting every shelf resembles a table scan. Consulting a catalog resembles an index lookup. Choosing whether the catalog helps depends on the request: retrieving one title benefits from it, while counting or inspecting nearly every book may favor a systematic shelf scan. The execution plan is the chosen search procedure, and optimization is the evidence-based choice among procedures.
Conceptual Query Processing Flow
A database first parses SQL and resolves names, types, permissions, and object references. It may then rewrite the logical expression, estimate row counts and costs for candidate operations, select a plan, allocate runtime resources, execute operators, and return rows. Some products cache plans, compile variants, or adapt parts of execution using observed runtime information.
| Phase | Purpose | Common problem |
|---|---|---|
| Parse and bind | Validate syntax, names, types, and permissions | Implicit conversions or ambiguous expressions |
| Transform | Rewrite equivalent logical forms | A construct may block a useful transformation |
| Estimate | Predict rows and resource work | Stale statistics or correlated data |
| Plan | Choose access paths, order, and algorithms | Lowest estimated cost differs from runtime reality |
| Execute | Run operators and produce rows | Spills, waits, blocking, or unexpected volume |
What Is a Query Optimizer?
The optimizer is the database component that compares candidate plans according to its cost model. It considers table and index size, value distribution, estimated cardinality, available access paths, join relationships, ordering, parallelism, memory, CPU, I/O, and product-specific settings.
Optimization is time-bounded because exploring every possible join order for a large statement would itself be expensive. The selected plan is normally the best estimated plan found within that search, not proof of the globally fastest execution under every parameter, cache state, and concurrent workload.
What Is an Execution Plan?
An execution plan is a tree or graph of physical operators. It may show scans, seeks or targeted index access, row lookups, filters, joins, sorts, aggregates, exchanges, materialization, estimated rows, actual rows, costs, memory, and runtime timing. Terminology and detail differ by database.
A table scan is not automatically bad, and an index seek is not automatically good. Reading most rows can favor a sequential scan, while a seek followed by millions of random lookups can be costly. Interpret operators in the context of cardinality and total work.
Sample Database
The examples use departments and employees. This tiny dataset demonstrates SQL shape but cannot prove performance; reliable benchmarking needs representative volume, skew, indexes, cache behavior, and concurrency.
CREATE TABLE departments (
department_id INT PRIMARY KEY,
department_name VARCHAR(100) NOT NULL
);
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
employee_name VARCHAR(100) NOT NULL,
department_id INT,
salary DECIMAL(10,2),
city VARCHAR(100),
joining_date DATE,
employment_status VARCHAR(20),
FOREIGN KEY (department_id)
REFERENCES departments(department_id)
);
INSERT INTO departments VALUES
(10, 'IT'), (20, 'HR'), (30, 'Finance');
Technique 1: Avoid Unnecessary SELECT Star
SELECT * reads and transfers every column even when a caller needs only two. Wider rows increase I/O, network payload, serialization, and memory, and can prevent a narrow covering index from satisfying the query. Name the required columns explicitly and treat new schema columns as an intentional API change.
Technique 2: Filter Rows with Precise Predicates
Push legitimate business filters into SQL rather than fetching broad data and discarding it in application code. Precise predicates reduce downstream join, sort, aggregate, and transfer work. Do not add filters that alter meaning, and verify whether conditions belong in ON, WHERE, or HAVING, especially with outer joins.
Technique 3: Create Workload-Based Indexes
Indexes are useful when their ordering supports important filters, joins, grouping, or sorting. Choose them from actual query patterns and validate with plans. Avoid indexing every column: each structure consumes storage and must be maintained by inserts, updates, and deletes.
Technique 4: Design Composite Indexes
A multicolumn B-tree is ordered by leading keys. For a department equality and salary range, (department_id, salary) provides a natural access path. Reversing it changes which rows are adjacent and may weaken the second predicate after the range begins.
CREATE INDEX ix_employees_department_salary
ON employees (department_id, salary);
SELECT employee_id, employee_name, salary
FROM employees
WHERE department_id = 10
AND salary > 65000;
Technique 5: Understand Selectivity
A selective predicate returns a small fraction of rows. Unique IDs are usually selective; a status matching almost the whole table is not. Distribution matters as much as distinct-value count because one status may be rare and another common. Histograms and product-specific statistics help the optimizer model skew.
Technique 6: Write SARGable Predicates
A SARGable predicate exposes an indexed column to a supported search operation. Wrapping the column in a function commonly prevents ordinary range navigation unless a matching expression index exists.
-- Less index-friendly
WHERE YEAR(joining_date) = 2026
-- Bounded range
WHERE joining_date >= '2026-01-01'
AND joining_date < '2027-01-01'
The range form also handles timestamps throughout the year without assuming a particular time precision.
Technique 7: Avoid Leading Wildcards When Possible
A pattern such as LIKE '%engineer' cannot normally navigate from the beginning of a standard B-tree. A left-anchored pattern such as LIKE 'engineer%' may use an appropriate index, depending on collation and engine rules. Use full-text, trigram, or specialized search when arbitrary substring search is a real requirement.
Technique 8: Optimize Join Predicates
Join on compatible keys with matching data types and suitable indexes. Avoid functions and implicit conversions on join columns. Filter inputs where logically safe, retrieve only required columns, and confirm that every relationship has an intentional predicate.
CREATE INDEX ix_employees_department
ON employees (department_id);
SELECT e.employee_name, d.department_name
FROM employees e
JOIN departments d
ON d.department_id = e.department_id
WHERE e.employment_status = 'ACTIVE';
Technique 9: Understand Join Algorithms
Nested loops work well when an outer input is small and the inner side has efficient lookup. Hash joins often suit larger unsorted equality joins but need memory and can spill. Merge joins consume sorted inputs and can be effective when order already exists. The optimizer selects among supported methods using estimates; do not force one by habit.
Technique 10: Remove Unnecessary Joins
A join that contributes no required column, filter, or existence rule adds work and may multiply rows. Remove it only after checking integrity assumptions and semantics. Sometimes a seemingly unused join intentionally guarantees that a related row exists or applies security through a filtered relation.
Technique 11: Prevent Cartesian Products
A missing join condition can pair every row from one input with every row from another. This may produce huge intermediate sets, spills, and timeouts. Use explicit CROSS JOIN when a Cartesian product is intentional so reviewers can distinguish design from error.
Technique 12: Use EXISTS for Existence Checks
When the requirement is simply whether a related row exists, EXISTS expresses that intent and allows a semijoin strategy. The engine can stop considering matches for one outer row after existence is established.
SELECT d.department_id, d.department_name
FROM departments d
WHERE EXISTS (
SELECT 1
FROM employees e
WHERE e.department_id = d.department_id
);
Technique 13: Understand EXISTS and IN
Modern optimizers can transform equivalent EXISTS and IN forms into similar plans, so neither is universally faster. Choose the clearest semantics and inspect the plan. Be especially careful with NOT IN: a null returned by the subquery can make comparisons unknown, while NOT EXISTS often expresses anti-existence more safely.
Technique 14: Optimize Subqueries by Evidence
Subqueries are not inherently slow. Optimizers may unnest, decorrelate, materialize, or convert them to joins. A correlated subquery becomes problematic when it repeatedly performs expensive work for many outer rows and cannot be transformed. Compare a join, pre-aggregation, window function, or common table expression while preserving duplicate and null behavior.
Technique 15: Prefer UNION ALL When Deduplication Is Unneeded
UNION removes duplicates, normally requiring sorting or hashing. UNION ALL concatenates results and avoids that operation. Use it only when duplicates are acceptable or the branches are proven disjoint; optimization must not change result semantics.
Technique 16: Remove Unnecessary DISTINCT
DISTINCT is appropriate when the requested relation truly requires unique rows. It should not hide a faulty many-to-many join or missing predicate. Fix the row multiplication first, then retain duplicate elimination only when it belongs to the requirement.
Technique 17: Optimize ORDER BY
Sorting can require memory and temporary storage. A matching index may provide order directly when its leading keys align with filters and directions. Limit result size where valid, avoid ordering columns the user does not need, and use a unique tie-breaker for deterministic output.
Technique 18: Limit Rows Intentionally
LIMIT, TOP, and FETCH FIRST are product-specific ways to request fewer rows. Pair them with a deterministic ORDER BY; otherwise the selected subset is not stable. A row limit can let the optimizer choose a plan optimized for early results, but it does not repair an unselective predicate.
Technique 19: Use Keyset Pagination for Deep Pages
Large offsets require reading and discarding earlier rows. Keyset pagination starts after the last key from the previous page and uses a matching ordered index.
CREATE INDEX ix_orders_created_id
ON orders (created_at DESC, order_id DESC);
SELECT order_id, created_at, total_amount
FROM orders
WHERE created_at < :last_created
OR (created_at = :last_created AND order_id < :last_id)
ORDER BY created_at DESC, order_id DESC
FETCH FIRST 50 ROWS ONLY;
Technique 20: Optimize GROUP BY
Reduce rows before grouping when the business logic permits it. Select only group keys and required aggregates, and consider an index whose leading columns match selective filters and grouping order. Hash and ordered aggregation have different memory and sorting behavior; compare plans rather than assuming one form is always best.
Technique 21: Filter Before Expensive Aggregation
A date, tenant, status, or category predicate applied before aggregation can greatly reduce work. Pre-aggregate at the correct grain when several joins would multiply facts. Ensure filters do not incorrectly remove rows needed for outer-join or zero-count reporting.
Technique 22: Distinguish WHERE and HAVING
WHERE filters source rows before grouping. HAVING filters groups after aggregates are calculated. Put ordinary row conditions in WHERE and aggregate conditions in HAVING.
SELECT department_id, COUNT(*) AS active_count
FROM employees
WHERE employment_status = 'ACTIVE'
GROUP BY department_id
HAVING COUNT(*) >= 2;
Technique 23: Avoid Implicit Type Conversion
Bind parameters with the same type, length, precision, scale, and character semantics as the target column. If the engine converts the indexed column for every comparison, it may lose an efficient seek and estimate rows poorly. Plans often reveal converted predicates or warnings.
Technique 24: Maintain Optimizer Statistics
Statistics describe row counts, distinct values, nulls, distributions, and sometimes column correlation. Large loads, mass deletes, changing skew, or stale collection can lead to inaccurate plans. Verify automatic collection and apply targeted analysis when major changes outpace it.
Technique 25: Investigate Cardinality Estimates
Cardinality is the number of rows expected or produced by an operator. A major gap between estimated and actual rows can cause the wrong join order, algorithm, index access, or memory allocation. Causes include stale statistics, skew, correlated columns, expressions, parameters, and data that violates modeling assumptions.
Technique 26: Handle Parameter-Sensitive Workloads
One reusable plan may not suit both a rare parameter and a common parameter. SQL Server commonly describes this as parameter sensitivity or parameter sniffing; other engines have their own planning and bind behavior. Confirm the problem with multiple representative values before using recompilation, plan variants, dynamic SQL, hints, or query separation.
Technique 27: Use Covering Indexes Selectively
A covering index contains all values required by a query and may avoid base-row lookup. Keep predicate and ordering columns as keys, and use nonkey included columns where supported for output-only values. Wide coverage consumes space, reduces cache density, and increases write cost, so target stable, high-value queries.
Technique 28: Use Partial or Filtered Indexes
A small index over actionable rows can optimize pending jobs, unresolved incidents, active subscriptions, or non-null external IDs. SQL Server calls this filtered indexing and PostgreSQL partial indexing. The query predicate must be compatible with the index condition, and parameterization can affect that proof.
CREATE INDEX ix_orders_pending_created
ON orders (created_at)
WHERE status = 'PENDING';
Technique 29: Avoid Excessive Indexing
Every index adds storage, cache pressure, write maintenance, logging, backup size, and operational complexity. Consolidate overlapping designs only after comparing keys, includes, filters, uniqueness, collation, and actual usage. Missing-index recommendations are inputs for analysis, not commands to create every suggested object.
Technique 30: Use Partitioning for Manageability and Pruning
Partitioning can isolate ranges, support retention, and let compatible predicates eliminate partitions. It does not automatically make a query fast and does not replace local access paths. Verify partition pruning in the plan and account for global versus local index behavior in the chosen database.
Technique 31: Understand Data, Plan, and Result Caching
Data pages, index pages, execution plans, intermediate objects, and application results may be cached at different layers. A warm-cache test and cold-cache test answer different questions. Plan reuse saves compilation but can preserve a poor parameter-sensitive choice. Result caching improves repeated reads only when freshness and invalidation rules are correct.
Technique 32: Reduce Network Transfer
Return only required rows and columns, avoid enormous text or binary values unless requested, and choose sensible fetch sizes. Database execution can be fast while serialization and network transfer dominate user latency. Compression and batching may help, but must be measured for CPU and memory impact.
Technique 33: Eliminate the N Plus One Pattern
The N+1 pattern issues one query for a parent list and another query for each parent. Network latency and repeated parsing can overwhelm the database. Replace it with a set-based join, a bounded batch query, appropriate eager loading, or a deliberately shaped API response. Avoid replacing N+1 with one unbounded join that multiplies data excessively.
Technique 34: Optimize INSERT Operations
Use set-based or batched inserts where supported, keep unnecessary indexes off high-volume ingestion tables, and avoid client round trips for each row. Sequence and key choices affect page locality. Measure transaction log, constraints, triggers, replication, and index maintenance because insert cost extends beyond writing the base row.
Technique 35: Optimize UPDATE Operations
Update only changed columns and target rows through selective predicates. Changing indexed keys or included values requires extra index work. Break very large updates into controlled batches when that improves logging, locking, and recovery, but preserve transactional correctness and avoid repeatedly rescanning the same rows.
Technique 36: Optimize DELETE Operations
Index deletion predicates, understand cascading relationships, and batch archival or retention work when operational limits require it. Large deletes generate logs and can hold locks, leave space, update many indexes, and affect replicas. Partition switching or dropping can be superior for range-based retention where supported and correctly designed.
Technique 37: Keep Transactions Appropriately Short
Do not hold a database transaction open while waiting for user input, remote services, or unrelated processing. Access shared objects in a consistent order, commit at the correct business boundary, and keep retry logic aware of idempotency. Shorter transactions often reduce lock duration and improve concurrency.
Technique 38: Diagnose Locking and Blocking
A query can be slow because it is waiting, not because its own plan uses excessive CPU. Measure lock waits, blockers, transaction age, isolation level, and access order. Suitable indexes can shorten locked scans, but changing isolation semantics purely for speed can introduce incorrect behavior.
Technique 39: Use Materialized Results for Repeated Work
Materialized views, indexed views, summary tables, or cached aggregates can precompute expensive repeated reporting. They exchange read speed for refresh cost, storage, and freshness complexity. Define the acceptable staleness, refresh strategy, failure recovery, and concurrency behavior before adoption.
Technique 40: Measure Query Performance
Capture a baseline and compare the same representative workload after each change. Measure logical and physical reads, CPU, elapsed time, rows processed, memory grants, spills, waits, lock duration, network bytes, and write impact. Repeat runs and report variability rather than selecting one unusually fast sample.
| Signal | Question it answers | Typical investigation |
|---|---|---|
| Logical reads | How much cached data was examined? | Access path, coverage, selectivity |
| CPU time | How much processing work occurred? | Joins, expressions, aggregation |
| Elapsed time | What did the caller experience? | Waits, concurrency, network |
| Estimated vs actual rows | Did the optimizer predict volume? | Statistics, skew, parameters |
| Spills | Was granted memory insufficient? | Estimates, sorts, hashes |
Using EXPLAIN Safely
EXPLAIN syntax and output vary. A nonexecuting estimated plan is safer for dangerous data changes but cannot show runtime row counts. Runtime forms execute the statement and add instrumentation overhead. Use transactions, read-only environments, or production-safe tooling when examining statements that modify data.
-- General estimated form
EXPLAIN
SELECT employee_id, employee_name
FROM employees
WHERE department_id = 10;
-- PostgreSQL runtime example
EXPLAIN (ANALYZE, BUFFERS)
SELECT employee_id, employee_name
FROM employees
WHERE department_id = 10;
-- MySQL runtime tree example
EXPLAIN ANALYZE
SELECT employee_id, employee_name
FROM employees
WHERE department_id = 10;
How to Read an Execution Plan
Start with the operators that access base objects, then follow rows through joins, filters, sorts, and aggregates toward the result. Identify the largest row flows, expensive repeated inner operations, lookups, spills, residual predicates, and blocking operators. Costs are internal estimates useful for comparison within the plan, not milliseconds.
Check object names, predicates, join conditions, execution counts, estimated and actual rows, memory, and warnings. One visually expensive operator may be a symptom of an earlier estimation error, so trace row flow rather than tuning only the highest displayed percentage.
Estimated Rows and Actual Rows
Estimated rows guide plan choice; actual rows show what happened during that execution. Large differences can cascade through join order, memory, and parallelism. Investigate statistics freshness, histogram coverage, correlated columns, nulls, expressions, parameter values, and changed data distribution.
Actual values are still one observation. A plan that fits one parameter may fail for another, so test the representative range rather than optimizing only the captured case.
Common Optimization Mistakes
- Tuning before verifying query correctness and business intent.
- Assuming every scan is bad or every seek is good.
- Creating every index suggested by a tool.
- Using hints before fixing estimates, schema, or query shape.
- Benchmarking tiny or uniformly generated data.
- Comparing elapsed time without controlling cache and concurrency.
- Replacing
INwithEXISTSas a universal rule. - Using
DISTINCTto conceal an incorrect join. - Optimizing reads while ignoring writes, locks, and storage.
- Changing isolation or consistency semantics only to gain speed.
Query Optimization Best Practices
Identify important statements from production evidence, reproduce them with representative parameters and data, and capture a baseline. Inspect actual plans where safe, compare estimates with reality, and determine whether the dominant issue is excessive work, bad estimation, waiting, network transfer, or repeated execution.
Make one justified change at a time: query shape, index, statistics, schema, batching, materialization, or application access pattern. Verify result equivalence and all relevant metrics, test concurrency and parameter variation, document the reason, deploy safely, and monitor for regression.
Real-World E-Commerce Order History
An order-history page filters one customer, orders by creation time, and returns a bounded page. A composite index such as (customer_id, created_at DESC, order_id DESC) supports filtering, deterministic ordering, and keyset pagination. Output-only status and amount can be included where supported if measured lookup savings justify the width.
Real-World Banking Transaction History
A banking query commonly scopes by account, filters a time interval, and sorts newest first. Optimization must preserve authorization, consistency, and audit requirements. Test skew between active and inactive accounts, parameter-sensitive plans, partition pruning, lock behavior, and the write cost of any added covering index.
Software Testing Dashboard
A dashboard may filter project, environment, release, execution status, and date while aggregating pass and failure counts. Push row filters before aggregation, avoid joining verbose logs into the counting query, and consider a composite operational index or refreshed summary for repeated reports.
Employee Management System
Employee screens often combine department, active status, manager, city, and joining date. Use selective combinations observed in real screens instead of one index per field. Retrieve profile details only when opened rather than widening every search result.
Website Article Search
A publishing platform typically uses ordinary indexes for site, publication state, category, and date, while a full-text subsystem handles linguistic title and body search. Apply tenant and authorization filters to every result and use deterministic ordering when relevance scores tie.
Interview Questions and Answers
What is query optimization?
It is the process of reducing the resources and time needed to execute correct SQL, using query design, schema, indexes, statistics, plans, and measured runtime evidence.
Why can a table scan be optimal?
When a table is small or a query needs a large fraction of rows, sequential reads can cost less than navigating an index and performing many row lookups.
What is cardinality estimation?
It is the optimizer's prediction of row counts at plan operators. Those predictions influence join order, algorithms, memory, access paths, and parallelism.
Are subqueries slower than joins?
Not inherently. Optimizers can transform many equivalent forms. Performance depends on semantics, transformation opportunities, indexes, estimates, and actual data.
What is parameter sensitivity?
It occurs when parameter values need materially different plans but plan compilation or reuse chooses one shape that performs poorly for part of the distribution.
How do you validate an optimization?
Verify identical intended results, compare plans and runtime metrics against a baseline, test representative parameter values and concurrency, and monitor after deployment.
Practical Exercises
- Compare
SELECT *with a narrow projection. - Create single and composite indexes and inspect plan changes.
- Rewrite a function-wrapped date predicate as a range.
- Compare valid
EXISTSandINforms. - Measure
UNIONagainstUNION ALL. - Find and fix an accidental Cartesian product.
- Compare offset and keyset pagination at a deep page.
- Test a covering index and record its write cost.
- Create a filtered or partial index where supported.
- Compare estimated and actual rows after changing data skew.
- Observe a lock wait caused by a deliberately long transaction.
- Design and benchmark an order-history query end to end.
Quick Reference
-- Retrieve only required data
SELECT employee_id, employee_name
FROM employees
WHERE department_id = :department_id;
-- Equality before range candidate
CREATE INDEX ix_orders_customer_date
ON orders (customer_id, order_date);
-- Prefer a range over a column function
WHERE order_date >= :start_date
AND order_date < :end_date
-- Existence semantics
WHERE EXISTS (
SELECT 1
FROM order_items i
WHERE i.order_id = o.order_id
)
-- Inspect the selected plan
EXPLAIN SELECT ...;
Step-by-Step Optimization Workflow
- Confirm the statement and required result are correct.
- Define the latency, throughput, and consistency target.
- Collect production frequency, waits, and resource evidence.
- Reproduce representative data, parameters, and concurrency.
- Capture plans and baseline runtime metrics.
- Locate excess row flow, bad estimates, spills, waits, or round trips.
- Choose the smallest justified query, index, statistics, or application change.
- Verify correctness and compare all relevant costs.
- Test alternate parameters and write-side impact.
- Deploy reversibly, monitor, document, and revisit after workload changes.
Key Takeaway
Query optimization begins with correct SQL and measured workload evidence. The optimizer chooses among access paths, join orders, algorithms, sorting, aggregation, and transformations using statistics and a cost model. Clear predicates, suitable indexes, accurate cardinality, bounded results, efficient joins, and set-based application access give it better options.
No single rewrite or operator label proves success. Preserve semantics, compare estimated and actual behavior, include waits and network cost, test representative parameters and concurrency, and account for write overhead. The reliable cycle is measure, analyze, change one justified factor, verify, deploy safely, and monitor continuously.