The Fastest Query Is the One That Throws Away Information First

A database can return the right answer and still waste nearly all its effort getting there.

That sounds paradoxical. If two queries produce the same rows, how can one be fundamentally better than the other? The answer is that a query is not merely a question. It is also a plan for reducing uncertainty. Every table scan, sort, grouping operation, and calculation handles information. Good SQL discards irrelevant information as early as possible. Bad SQL carries unnecessary information through every later stage, then throws it away at the end.

This creates a deeper connection between two ideas that are often taught separately: the logical meaning of SQL and the physical mechanics of query optimization. Clauses such as WHERE, DISTINCT, and ORDER BY describe what result we want. Indexes, sargability, filtering, and result limits determine how much work the database must do to obtain it.

The central lesson is simple:

Query performance is largely the art of controlling how long irrelevant information remains alive.

Once you see SQL this way, execution order stops being a memorization exercise. It becomes a mental model for deciding where information should be eliminated, where it must be preserved, and which operations are expensive because they require the database to see everything before it can answer anything.

SQL Has Two Orders, and Confusing Them Creates Slow Queries

When people read a query, they usually read it from top to bottom. A typical statement begins with SELECT, names a table with FROM, filters rows using WHERE, and perhaps finishes with ORDER BY or DISTINCT.

But the database does not necessarily reason about the statement in that same order. A useful conceptual sequence is:

1. FROM identifies the source rows.
2. WHERE removes rows that do not satisfy a Boolean condition.
3. SELECT determines which expressions and columns appear in the result.
4. DISTINCT removes duplicate result rows.
5. ORDER BY arranges the surviving rows.

This is a logical order, not a complete description of the physical work performed by a database engine. The engine may use an index to avoid reading many rows, reorder operations, or choose a different strategy altogether. Yet the logical sequence remains essential because it explains what each operation is allowed to know.

Consider this query:

SELECT DISTINCT genre
FROM simple_books
WHERE publication_year >= 2000
ORDER BY genre;

The query does not first sort every book and then inspect its year. Conceptually, it begins with the table, keeps books published from 2000 onward, projects the genre, removes repeated genres, and orders the final set.

That sequence matters because every stage changes the size or shape of the data passed to the next stage. Suppose the table contains ten million books, but only fifty thousand were published after 2000, and those books belong to twelve genres. The operations are not equally expensive when applied to ten million rows, fifty thousand rows, or twelve rows.

Filtering early is therefore more than a stylistic preference. It changes the amount of data that later operations must process. Sorting twelve values is trivial. Sorting ten million values may require substantial memory, temporary storage, and comparison work. Deduplicating a small set is cheap. Deduplicating a massive set can become a major part of the query.

The visible shape of SQL encourages a misleading intuition: because SELECT appears first, perhaps selection happens first. In reality, the database must establish the row source before it can decide which rows qualify, and it must know the surviving values before it can remove duplicates or sort them. The order of clauses is a language interface. The order of useful work is a question of information flow.

Filtering Is Not Just a Condition. It Is a Compression Strategy

A WHERE clause is often described as a test that returns true or false for each row. That is correct, but incomplete. It is also a compression mechanism.

Imagine a warehouse containing ten million boxes. Each box has labels for category, date, location, and value. You need to find the distinct categories of boxes worth more than $1,000 that arrived this year, listed alphabetically. One approach is to carry every box to a central room, inspect every label, remove duplicates, and sort the categories. Another approach is to use the warehouse labels and routes to visit only plausible boxes, then perform the small final operations on the survivors.

An index is valuable because it can make the second approach possible. But the condition must be expressed in a form that the index can use efficiently. This is the meaning of sargability, short for searched argument able. A sargable predicate gives the database a searchable structure instead of forcing it to calculate a transformation for every row.

Suppose a table has an index on publication_year. Compare these conditions:

WHERE publication_year >= 2000

and:

WHERE publication_year + 1 > 2000

They may be logically equivalent, but they do not necessarily offer the same physical opportunity. The first condition points directly to a range of indexed values. The second asks the database to perform arithmetic on the column before deciding whether the row qualifies. Depending on the engine and its optimizer, that calculation may prevent efficient use of the index.

The same issue appears with negation:

WHERE NOT publication_year < 2000

This may be rewritten more usefully as:

WHERE publication_year >= 2000

The rewritten form describes a direct range. It gives the engine a clearer path through the index and expresses the intended search in a way that more closely matches the data structure.

Text searches reveal another boundary. A condition such as:

WHERE title LIKE 'Data%'

may allow an index to locate titles beginning with Data, because the beginning of the value is known. A condition such as:

WHERE title LIKE '%Data%'

asks for the text to appear anywhere. A conventional ordered index has little leverage when the initial characters are unknown. The database may need to inspect a far larger portion of the table.

These examples point to a broader principle: a predicate is fast when it preserves the structure the database already has. Arithmetic, functions, leading wildcards, and unnecessary negation can hide that structure. The problem is not that the condition is mathematically difficult. The problem is that the condition changes the question from “where is the matching region?” to “calculate something for every candidate and then check it.”

This distinction also explains why “use an index” is not a complete optimization strategy. An index is not magic acceleration. It is a representation of data ordered or organized for particular kinds of questions. If the query asks a question that does not align with that representation, the index may offer little help.

The Cost of Waiting to Discard Information

The most useful way to understand query optimization is to track data volume across stages.

Suppose a query starts with one million rows. A filter reduces that to ten thousand. A projection keeps only two columns. A distinct operation reduces those ten thousand rows to fifty values. Finally, an order operation sorts the fifty values.

The query becomes cheaper when the reduction happens early, but there is an important qualification: the database may not always be free to move operations wherever it wants. Some operations depend on information that has not yet been established. A filter referring to a base table column can often be applied early. A filter referring to an aggregate or an alias created later may have to wait. Sorting may be avoidable only if an index already provides the required order. Duplicate elimination may require seeing enough rows to know which values repeat.

This gives us a useful framework: classify each operation by how much information it needs and how much information it destroys.

Local operations inspect one row at a time. Simple comparisons such as publication_year >= 2000 are local. They are excellent candidates for early execution because they can reject rows without waiting for other rows.

Relational operations combine or compare many rows. DISTINCT, grouping, and sorting are global operations. They usually need a broader view of the input and can become expensive when applied to large intermediate results.

Transformational operations calculate new values. Functions and arithmetic may be harmless after filtering, but expensive when applied to every row. If a transformation is necessary, it is often better to reduce the candidate set first.

This classification turns vague advice into a practical design rule:

Push cheap, selective, local decisions toward the beginning. Delay expensive, global decisions until the dataset is small.

Consider a library application that needs the unique genres of books published after 2010. A sensible query is:

SELECT DISTINCT genre
FROM simple_books
WHERE publication_year >= 2010;

The WHERE clause reduces the candidate books before DISTINCT has to compare genre values. If the application then needs those genres alphabetically, adding ORDER BY genre is reasonable because ordering occurs after duplicate values have been removed.

Could the database execute this more cleverly? Certainly. An index involving publication year and genre might help locate the relevant region and produce values in a useful order. But the query’s semantic structure still matters. It tells the optimizer what the result means and exposes the opportunity to reduce the search space.

The opposite pattern is a warning sign:

SELECT DISTINCT genre
FROM simple_books
ORDER BY genre;

This asks the database to consider all books before it can determine the unique genres. That might be correct if every book is relevant. But if the application actually needs only recent books, omitting the filter is not merely a performance mistake. It changes the meaning of the answer.

Performance and correctness are often treated as separate concerns. In database work, they are frequently connected by the same structural question: when does the system know enough to discard something? Discarding too early can produce a wrong answer. Discarding too late can produce an expensive one.

The Hidden Tradeoff Between Expressiveness and Searchability

Readable SQL allows us to state a question in a form that resembles ordinary reasoning. But the most natural wording is not always the most searchable representation.

Imagine asking for customers whose account age is more than five years. A human might write a function that calculates each customer’s age and compares it with five. A more index friendly approach calculates the cutoff date once and compares the stored date directly:

WHERE signup_date < '2021-01-01'

The second form is not necessarily more meaningful to a human reader. It is more useful to the database because the stored column remains visible as the thing being searched. The calculation has moved from every row to a single boundary value.

This suggests a valuable mental model called calculation placement. Whenever a query contains a function or arithmetic expression, ask two questions:

1. Can the calculation be performed once on a constant or parameter?
2. Can the indexed column remain structurally unchanged in the predicate?

If the answer to both is yes, the query may become more searchable without becoming less clear.

There is also a limit to early filtering. A condition that removes almost no rows may not justify a complex index or elaborate rewrite. A condition that removes 99 percent of rows is much more valuable as an early gate. This is the idea of selectivity: how sharply a predicate reduces the candidate set.

A filter on a column where nearly every row has the same value may provide little benefit. A filter on a date range, customer identifier, or status with strong concentration may reduce the workload dramatically. Query optimization is therefore not about applying every possible condition as early as possible. It is about finding the earliest conditions that are both legally movable and materially selective.

The same reasoning applies to result size. If an interface needs only the first twenty matching books, returning thousands of rows and letting the application discard the rest wastes network bandwidth, memory, and processing time. A limit is not merely a user interface feature. It is a declaration that the consumer does not need the rest of the information.

Likewise, SELECT * often communicates a lack of discipline about information requirements. Retrieving columns that will not be displayed or processed increases transfer costs and can prevent certain index only strategies. Asking for exactly what the application needs is a form of early reduction at the column level.

A Practical Method for Designing Better Queries

Before tuning a query, do not begin by adding indexes at random. Begin by drawing the information path.

Write down the table or tables involved, then estimate the number of rows at each stage. Identify which predicates can reject rows independently. Mark any arithmetic, functions, negation, leading wildcard, sorting, grouping, or duplicate elimination. Finally, ask whether the database can use an existing index to perform the earliest selective reduction.

For example, suppose the requirement is: find the distinct genres of books published after 2015, sort them alphabetically, and show at most ten results.

A disciplined version might be:

SELECT DISTINCT genre
FROM simple_books
WHERE publication_year > 2015
ORDER BY genre
LIMIT 10;

The exact limit syntax varies among database systems, but the structure communicates the important facts. The date condition narrows the rows. The projection keeps only the needed attribute. Duplicate elimination reduces repeated genres. Ordering applies to the result that remains. The limit states that the consumer needs only a small prefix of the final answer.

Then inspect the physical plan. Query optimization tools can reveal whether the database is scanning the whole table, using an index, sorting a large intermediate set, or creating temporary structures. The plan is not a judgment on the SQL’s appearance. It is evidence about the work actually performed.

A useful checklist is:

• Is the most selective practical filter expressed directly on the stored column?
• Does the predicate preserve index searchability?
• Are calculations and functions being applied to the indexed column unnecessarily?
• Are leading wildcards forcing broad searches?
• Is the query sorting or grouping more rows than necessary?
• Is DISTINCT required, or is duplication being created by an earlier join or design choice?
• Are all selected columns genuinely needed?
• Can the result set be limited safely?
• Does the execution plan confirm the intended strategy?

These questions shift optimization from superstition to diagnosis. They also prevent a common mistake: treating every slow query as an indexing problem. Sometimes the real issue is that the query asks for too much data, sorts too late, calculates per row, or returns columns the application never uses.

Key Takeaways

• Think in terms of information reduction. A query becomes more efficient when irrelevant rows and columns disappear before expensive operations handle them.
• Separate logical order from physical execution. SQL clauses describe meaning, while the optimizer chooses a strategy. Understanding both prevents incorrect assumptions about what happens first.
• Write searchable predicates. Keep indexed columns structurally visible. Move calculations to constants or parameters when possible, and avoid unnecessary functions, negation, and leading wildcards.
• Delay global operations. Sorting, grouping, and duplicate elimination often become cheaper after selective filtering and projection.
• Use the execution plan as evidence. Confirm whether the database is actually using the intended index and reducing the workload early.

The deepest lesson is not that WHERE should appear before ORDER BY, or that indexes are useful, although both facts matter. It is that every query creates a temporary world of candidate rows, and every operation determines how long those candidates remain in existence.

A well designed query is a carefully managed funnel. It begins with a broad source, applies precise gates, carries only necessary attributes, and postpones expensive judgments until the crowd has become a shortlist. The fastest answer is rarely produced by doing the same work more aggressively. It is produced by making sure the database never does unnecessary work at all.

So the next time a query feels slow, ask a more revealing question than “Which index am I missing?” Ask: What information am I carrying farther than I need to?