As databases grow from thousands to millions—or even billions—of rows, SQL query performance becomes increasingly important.
A query that runs in a fraction of a second on a small table can become painfully slow when executed against a large production dataset. The good news is that many performance problems can be addressed with a simple principle:
Do less work, as early as possible.
This article explores some of the most useful techniques for improving SQL query performance, particularly when working with large datasets.
Filter Data as Early as Possible
One of the simplest ways to improve query performance is to reduce the number of rows that need to be processed.
Suppose you have a large events table containing years of time-series data, but you only need events from January 2026:
SELECT *
FROM events
WHERE event_date >= '2026-01-01'
AND event_date < '2026-02-01';
Instead of processing the entire table, the database can work with a much smaller subset of data.
This becomes especially valuable when the filtered data is later:
Joined with other tables
Aggregated
Sorted
Deduplicated
Transformed
The fewer rows entering these operations, the less work the database has to perform.
A useful development strategy
When developing a query against a very large table, start with a small subset of the data.
For example:
Explore a small time range.
Develop and test your query.
Verify that the results are correct.
Remove the restriction.
Run the final query against the full dataset.
This makes development faster and can help you identify logical problems without repeatedly processing millions of rows.
Don't Assume LIMIT Makes Aggregations Faster
LIMIT is useful for restricting the number of rows returned, but it does not necessarily reduce the amount of work required by an aggregation.
Consider:
SELECT COUNT(*)
FROM events
LIMIT 10;
The result of COUNT(*) is already a single row. The database still has to calculate the count before it can apply the LIMIT.
The same principle applies to grouping:
SELECT customer_id, COUNT(*)
FROM events
GROUP BY customer_id
LIMIT 10;
Conceptually, the database needs to perform the grouping before it knows which aggregated rows can be returned.
If your goal is to reduce the amount of input data during testing, you can put the LIMIT inside a subquery:
SELECT COUNT(*)
FROM (
SELECT *
FROM events
LIMIT 1000
) AS sample;
This genuinely reduces the number of rows being aggregated.
However, there is an important warning: the result now represents only the sample of 1,000 rows, not the complete dataset.
This technique is excellent for testing query logic, but it should not be confused with an optimization that preserves the original result.
Reduce Table Sizes Before Joining
Joins can become expensive when they involve large datasets.
Imagine two tables:
Suppose web_events contains hundreds of millions of records, but your final result only needs the number of events per account.
Instead of joining every event to the accounts table first, you can aggregate the events before performing the join:
SELECT account_id, COUNT(*) AS event_count
FROM web_events
GROUP BY account_id;
This produces a much smaller result.
You can then join that result with accounts:
SELECT
a.name,
e.event_count
FROM accounts AS a
JOIN (
SELECT
account_id,
COUNT(*) AS event_count
FROM web_events
GROUP BY account_id
) AS e
ON a.id = e.account_id;
The key idea is:
Reduce the number of rows before expensive operations such as joins.
If web_events contains 500 million rows but only 100,000 accounts, the aggregation can potentially reduce the data involved in the subsequent join dramatically.
Be Careful When Joining Multiple Detail Tables
One of the most important SQL performance and correctness issues occurs when joining multiple tables that contain several rows for the same entity.
Consider two tables containing records by date:
| Date | Table A | Table B |
|---|
| Jan 1 | 100 rows | 200 rows |
| Jan 2 | 50 rows | 100 rows |
If you join these tables directly on the date, the database can produce a multiplicative result.
For January 1:
100 × 200 = 20,000 rows
For January 2:
50 × 100 = 5,000 rows
This can quickly become a serious performance problem.
Even more importantly, it can produce incorrect aggregate results.
The better approach: aggregate first
Instead of joining the raw tables, aggregate each one independently:
SELECT
date,
COUNT(*) AS a_count
FROM table_a
GROUP BY date;
And:
SELECT
date,
COUNT(*) AS b_count
FROM table_b
GROUP BY date;
You can then join these much smaller datasets:
SELECT
COALESCE(a.date, b.date) AS date,
a.a_count,
b.b_count
FROM (
SELECT
date,
COUNT(*) AS a_count
FROM table_a
GROUP BY date
) AS a
FULL JOIN (
SELECT
date,
COUNT(*) AS b_count
FROM table_b
GROUP BY date
) AS b
ON a.date = b.date;
Instead of joining potentially millions of raw records, you're joining a result containing approximately one row per date.
This pattern is particularly powerful when working with large event, transaction, or log tables.
Understand Why COUNT(DISTINCT ...) Can Matter
Join multiplication can also lead to incorrect counts.
Imagine a sales representative has:
If you join the two detail tables on the sales representative, you could end up with:
5 × 20 = 100 rows
A simple:
COUNT(sales_rep_id)
could therefore count the same sales representative many times.
In some situations, you may need:
COUNT(DISTINCT sales_rep_id)
to count each representative only once.
However, COUNT(DISTINCT ...) should not automatically be considered the solution to every join problem.
If the underlying issue is that you're joining large detail tables unnecessarily, pre-aggregation is often a much better approach.
For example:
SELECT sales_rep_id, COUNT(*) AS order_count
FROM orders
GROUP BY sales_rep_id;
And separately:
SELECT sales_rep_id, COUNT(*) AS event_count
FROM web_events
GROUP BY sales_rep_id;
You can then join these compact results.
This avoids creating the potentially enormous intermediate dataset in the first place.
Use EXPLAIN to Understand What the Database Is Doing
When a query is slow, guessing is rarely the best strategy.
Most SQL databases provide an EXPLAIN command that allows you to inspect the query execution plan.
For example:
EXPLAIN
SELECT ...
FROM ...
WHERE ...;
Depending on the database system, the execution plan can provide information about:
The exact syntax and information available vary between database systems, but the underlying idea is the same: understand where the database expects to spend its work.
A practical optimization workflow
A useful process looks like this:
Write query
↓
EXPLAIN
↓
Identify expensive operations
↓
Modify query
↓
EXPLAIN again
↓
Compare the plans
↓
Measure the actual performance
The cost shown by EXPLAIN is generally not a direct measurement of execution time. Think of it as an estimate that helps the optimizer and developer compare alternative execution strategies.
Where supported, commands such as EXPLAIN ANALYZE can provide actual execution information as well.
The Core Principle: Do Less Work Earlier
Most of the techniques discussed above are different applications of the same idea:
The best optimization is often to prevent unnecessary work from happening in the first place.
Think about a query as a pipeline.
If you start with 100 million rows and filter it down to 1 million rows before performing a join, that join has dramatically less data to process.
If you aggregate those 1 million rows into 50,000 groups before joining another table, the next operation becomes even smaller.
In general:
Filter → Aggregate → Join → Transform
is often more efficient than:
Join everything → Transform everything → Aggregate at the end
Of course, SQL optimizers can rewrite queries and choose their own execution strategies, so the database engine may already perform some of these optimizations automatically. Nevertheless, writing queries that clearly express the intended reduction of data can make both performance and correctness easier to reason about.
A Simple Before-and-After Example
Consider a query that joins two large tables directly:
SELECT
...
FROM large_table_a AS a
JOIN large_table_b AS b
ON a.date = b.date
GROUP BY ...;
If both tables contain many records per date, this join can generate a huge intermediate result.
A more efficient pattern is often:
SELECT
...
FROM (
SELECT
date,
...
FROM large_table_a
GROUP BY date
) AS a
JOIN (
SELECT
date,
...
FROM large_table_b
GROUP BY date
) AS b
ON a.date = b.date;
Now each table has been reduced before the join.
Instead of joining potentially millions of detail records, the database may only need to join a relatively small number of aggregated rows.
The result is not only potentially faster—it can also be much easier to reason about.
Performance Is Important, but Correctness Comes First
There is one rule that should always come before performance:
Never optimize a query by accidentally changing what it means.
For example, adding:
LIMIT 1000
may make a query run faster, but it also changes the dataset being analyzed.
Likewise, moving an aggregation before a join can improve performance, but only if the new aggregation still produces the intended result.
Good SQL optimization therefore involves two questions:
Does this query produce the correct result?
Can it produce that result with less work?
Only after correctness has been established should you optimize aggressively.
When SQL queries become slow on large datasets, the solution is often not a complicated trick. It is about reducing unnecessary work.
The most important techniques are:
Filter early to reduce the number of rows.
Don't rely on LIMIT to speed up aggregations.
Aggregate before joining when it is logically correct.
Avoid joining multiple large detail tables unnecessarily.
Use COUNT(DISTINCT ...) when duplicate rows created by joins would otherwise produce incorrect counts.
Use EXPLAIN and execution plans to identify expensive operations.
Compare query plans and measure performance after making changes.
Always preserve correctness.
The principle worth remembering is simple:
Do less work, as early as possible.
When working with millions or billions of rows, reducing the amount of data before expensive operations such as joins, sorting, and aggregation can make an enormous difference.
Good SQL isn't just about getting the right answer. It's about getting the right answer efficiently.