Inside SQLite’s Frontend: How the WHERE Clause Drives Optimization

Hello, I'm Maneshwar. I'm building git-lrc, an AI code reviewer that runs on every commit. It is free, unlimited, and source-available on Github. Star Us to help devs discover the project. Do give it a try and share your feedback for improving the product.

In the previous part, you saw how the query optimizer chooses efficient plans using indexes and access paths.

Now we go deeper into the most important part of almost every SQL query.

The WHERE clause.

This is where most of the filtering happens, and in practice, this is where most performance gains or losses come from.

Why the WHERE Clause Matters So Much

Almost every real-world query includes a WHERE clause. It determines which rows should be retrieved, updated, or deleted.

From SQLite’s perspective, the WHERE clause is not just a condition. It is a set of constraints that can be used to reduce the amount of data scanned.

The optimizer’s main goal here is simple.

Use the WHERE clause to avoid touching unnecessary rows.

Breaking the WHERE Clause into Terms

SQLite does not treat the WHERE clause as one big expression. Instead, it breaks it into smaller pieces called terms.

These terms are connected using the AND operator.

For example:

SELECT * FROM t1 WHERE a > 0 AND b < 0;

SQLite splits this into two terms:

a > 0
b < 0

This format is called conjunctive normal form, where conditions are separated into independent AND-connected components.

Each term is then analyzed individually to see if it can use an index.

What Happens with OR Conditions

Things change when OR appears.

SELECT * FROM t1 WHERE a = 5 OR b = 7;

In this case, SQLite treats the entire WHERE clause as a single term.

This makes index usage more complex and often disables straightforward index optimization.

A common workaround is to rewrite such queries using UNION:

SELECT * FROM t1 WHERE a = 5
UNION
SELECT * FROM t1 WHERE b = 7;

If the two result sets do not overlap, using UNION ALL is even faster:

SELECT * FROM t1 WHERE a = 5
UNION ALL
SELECT * FROM t1 WHERE b = 7;

This allows SQLite to optimize each part separately.

How SQLite Evaluates Each Term

For every term in the WHERE clause, SQLite tries to answer one question.

Can this term be satisfied using an index?

If yes, SQLite uses the index to directly filter rows. In this case, the condition does not need to be checked again because the index lookup already guarantees it.

If not, SQLite evaluates the condition manually for each row retrieved.

This means:

• Index-supported terms reduce the number of rows scanned
• Non-indexed terms act as filters after rows are retrieved

Sometimes, a term partially helps with index selection but still needs to be evaluated later. In such cases, it contributes to narrowing down candidates but is not fully handled by the index.

Virtual Terms: Hidden Optimization

While analyzing conditions, SQLite may introduce additional virtual terms.

These are not written in your query, but they are derived from existing conditions.

Virtual terms are typically used for index optimization and are not explicitly evaluated against rows. They help SQLite improve access paths without changing query semantics.

When Indexes Can Be Used

SQLite only uses indexes under certain conditions.

First, the WHERE clause must be structured using AND-connected terms.

Second, specific operators and patterns must be used.

Indexes are usable when terms look like:

• column = expression
• column > expression (and similar inequalities)
• column IN (value list or subquery)
• column IS NULL

If a term does not match these patterns, it cannot directly drive index usage.

IN clauses are particularly useful because they allow multiple values to be searched efficiently using indexes.

When Indexes Are Not Used

Indexes are often not used in these situations:

• When conditions are connected using OR
• When no index exists on the relevant column
• When the condition does not match indexable patterns

In such cases, SQLite falls back to scanning rows and applying filters afterward.

Understanding Multi-Column Indexes

Single-column indexes are simple. Multi-column indexes are where things get interesting.

Consider an index like:

CREATE INDEX idx1 ON table1(a, b, c, d);

This index is ordered first by a, then by b, then c, and so on.

SQLite can only use this index from left to right.

That means:

• It starts with column a
• Then moves to b
• Then c, and so on

If the leftmost column is not used, the index cannot be used at all.

The Left-to-Right Rule

For a multi-column index to be usable:

• The leftmost column must appear in the WHERE clause
• Columns must be used consecutively without gaps

Example:

WHERE a = 5 AND b = 10

This uses the index efficiently.

But:

WHERE b = 10 AND c = 20

This cannot use the index because a is missing.

Equality vs Inequality

There is another important rule.

All columns in the index must use:

• Equality (=) or IN

Except for the rightmost column, which can use inequalities like >, <, >=, <=.

Example:

WHERE a = 5 AND b IN (1,2,3) AND c > 100

Here:

• a and b use equality/IN
• c uses inequality

This is valid, and the index can still be used.

What Happens After an Inequality

Once an inequality appears, SQLite stops using further columns in the index.

Example:

WHERE a = 5 AND b IN (1,2,3) AND c > 100 AND d = 'hello'

In this case:

• a, b, and c are used for indexing
• d is not used for indexing

Even though d has an equality condition, it appears after an inequality (c > 100), so it cannot be part of the index search.

Instead, d becomes a filter applied after rows are retrieved.

No Gaps Allowed

Another key rule is that there cannot be gaps in index usage.

If a column in the sequence is missing, all columns to the right become unusable for indexing.

Example:

WHERE a = 5 AND b = 10 AND d = 20

Since c is missing:

• a and b can be used
• d cannot be used

SQLite enforces strict left-to-right continuity.

The WHERE clause is the heart of query optimization in SQLite.

By breaking conditions into terms, analyzing each one, and applying strict rules for index usage, SQLite ensures that queries are executed efficiently.

In the next part, we will look at specific operators like BETWEEN, LIKE, and GLOB, and how they influence index usage and query performance.

*AI agents write code fast. They also silently remove logic, change behavior, and introduce bugs -- without telling you. You often find out in production.

git-lrc fixes this. It hooks into git commit and reviews every diff before it lands. 60-second setup. Completely free.*

Any feedback or contributors are welcome! It's online, source-available, and ready for anyone to use.

⭐ Star it on GitHub:

HexmosTech / git-lrc

Free, Unlimited AI Code Reviews That Run on Commit

| 🇩🇰 Dansk | 🇪🇸 Español | 🇮🇷 Farsi | 🇫🇮 Suomi | 🇯🇵 日本語 | 🇳🇴 Norsk | 🇵🇹 Português | 🇷🇺 Русский | 🇦🇱 Shqip | 🇨🇳 中文 |

git-lrc

Free, Unlimited AI Code Reviews That Run on Commit

 

     

---

AI agents write code fast. They also silently remove logic, change behavior, and introduce bugs -- without telling you. You often find out in production.

git-lrc fixes this. It hooks into git commit and reviews every diff before it lands. 60-second setup. Completely free.

See It In Action

See git-lrc catch serious security issues such as leaked credentials, expensive cloud operations, and sensitive material in log statements

git-lrc-intro-60s.mp4

Why

• 🤖 AI agents silently break things. Code removed. Logic changed. Edge cases gone. You won't notice until production.
• 🔍 Catch it before it ships. AI-powered inline comments show you exactly what changed and what looks wrong.
• 🔁 Build a…

View on GitHub