Why Some Queries Are Fast and Others Are Slow.
How Databases Optimize Performance
You run a query, and it works.
Everything feels fast and responsive. Pages load quickly, dashboards update instantly, and there is no reason to worry.
But as your data grows, things begin to change.
A query that once took milliseconds starts taking seconds. Pages feel slower. Users begin to notice.
At this point, most developers start asking:
Why is this query slow?
The answer often lies in something happening behind the scenes:
query optimization
Series: Database Query Fundamentals
Part 1: What Happens When You Run a Query? → Query part 1
Part 2: Query Optimization Explained (this article)
What Is Query Optimization?
When you write a SQL query, the database does not simply execute it exactly as written.
It first decides how to execute it.
There are usually multiple ways to retrieve the same data. Some are efficient. Others are not.
Query optimization is the process where the database chooses the most efficient way to execute a query.
Instead of asking:
“What does this query do?”
The database asks:
“What is the fastest way to do this?”
Why Optimization Matters
Two queries can return the same result but perform very differently.
For example, to find a user by ID, the database could:
scan every row in a table
use an index to jump directly to the correct record
Both approaches are correct.
Only one is fast.
As your data grows, these differences become more noticeable.
A small inefficiency might not matter with a few thousand rows.
But with millions of records, it can slow down your entire system.
Where Optimization Fits in the Query Process
From the previous article in this series, you already know that a query goes through several stages:
parsing
translation
planning
execution
Query optimization happens during the planning stage.
This is where the database evaluates different ways to execute your query and selects the most efficient one.
The result of this process is called an execution plan.
How the Database Chooses a Plan
The database considers different strategies and compares them.
Some of the decisions it makes include:
whether to use an index or scan the full table
how to join multiple tables
in what order to apply filters
how to sort or group data
Each possible strategy has a cost.
The database estimates these costs and chooses the plan with the lowest expected cost.
Two Main Types of Optimization
Rule Based Optimization
In rule based optimization, the database follows predefined rules to improve a query.
For example:
applying filters early
simplifying conditions
reordering operations based on known patterns
This approach is simple but limited because it does not consider actual data size or distribution.
Cost Based Optimization
Most modern databases use cost based optimization.
Instead of relying only on rules, the database estimates how expensive each possible plan will be.
It considers factors such as:
number of rows in a table
indexes available
data distribution
expected disk and memory operations
Based on these estimates, it chooses the plan that is likely to perform best.
This is why the same query can behave differently as your data grows.
Why Queries Become Slow
Even with optimization, queries can still become slow over time.
Common reasons include:
missing or inefficient indexes
large datasets
complex joins
poor query structure
outdated statistics
When this happens, the optimizer may choose a plan that is no longer efficient.
What This Means for Developers
The optimizer is powerful, but it is not perfect.
It makes decisions based on available information.
As a developer, your role is to help it make better decisions.
This includes:
writing clear and efficient queries
using indexes appropriately
understanding how your data is accessed
Summary
Query optimization is what allows databases to scale from small datasets to millions of records while maintaining performance.
It is the reason a query can remain fast even as your application grows.
Behind every fast query is a good execution plan.
And behind every good execution plan is an optimizer making smart decisions.
What Comes Next
In the next article, we will explore query execution plans in more detail.
You will learn how to read them and understand what the database is actually doing when your query runs.
