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 SQLite breaks the WHERE clause into terms and uses strict rules to decide whether indexes can be applied.
Now we go deeper into specific operators that appear frequently in real queries and how SQLite optimizes them.
These operators may look simple at the SQL level, but internally SQLite often rewrites them or applies special strategies to make them efficient.
How SQLite Handles the BETWEEN Clause
The BETWEEN clause is commonly used for range queries.
For example:
SELECT * FROM users WHERE age BETWEEN 18 AND 30;
SQLite does not treat this as a special standalone operation. Instead, it rewrites it internally into two conditions:
age >= 18 AND age <= 30
This transformation introduces two virtual terms. These terms are not explicitly written by the user, but they are used by the optimizer to reason about the query.
If both of these conditions can be satisfied using an index, SQLite performs a range scan on that index. It sets:
- The lower bound as
18
- The upper bound as
30
In this case, the original BETWEEN condition is completely eliminated during execution.
No additional checks are required for each row because the index guarantees correctness.
If the index cannot fully satisfy both conditions, SQLite still uses these virtual terms as hints for optimization.
However, the original BETWEEN condition is then evaluated for each row, with the important detail that the expression on the left side is computed only once.
How SQLite Optimizes OR Conditions
The OR operator is tricky because it often prevents straightforward index usage.
Consider this query:
SELECT * FROM users WHERE age = 20 OR age = 25 OR age = 30;
SQLite first checks whether all OR-connected conditions refer to the same column. If they do, it rewrites the query into an IN clause:
SELECT * FROM users WHERE age IN (20, 25, 30);
This transformation allows SQLite to use an index efficiently.
There are two important rules here:
- The same column must appear in every OR term
- The column can appear on either side of the equality operator
If an index exists on that column, SQLite uses it just like any other IN query.
When OR Cannot Be Rewritten
If the OR conditions involve different columns, SQLite cannot rewrite them into an IN clause.
Example:
SELECT * FROM users WHERE age = 20 OR salary = 50000;
In this case, SQLite applies a different strategy.
It analyzes each OR term separately, almost as if each one is its own WHERE clause.
If each term can use an index, SQLite executes them independently and then combines the results, removing duplicates if necessary.
If some terms cannot use indexes, performance degrades.
In the worst case, SQLite may fall back to a full table scan.
Pattern Matching with LIKE and GLOB
The LIKE and GLOB operators are used for pattern matching.
Example:
SELECT * FROM users WHERE name LIKE 'A%';
or
SELECT * FROM users WHERE name GLOB 'A*';
Although these look simple, their optimization depends on several conditions.
Case Sensitivity Differences
GLOB is always case sensitive.
LIKE is more flexible.
By default, LIKE is case-insensitive for basic ASCII characters. This means:
'a' LIKE 'A'
evaluates to true.
You can change this behavior by enabling case-sensitive mode using a pragma or build configuration.
However, for non-ASCII characters, comparisons are always case sensitive unless a custom collation is used.
When LIKE and GLOB Can Use Indexes
SQLite can use indexes for LIKE and GLOB, but only if strict conditions are met.
First, the left-hand side must be an indexed column with text affinity.
Second, the pattern on the right-hand side must be a string literal or a bound parameter that behaves like a literal.
Most importantly, the pattern must not start with a wildcard.
Example that can use an index:
SELECT * FROM users WHERE name LIKE 'A%';
Example that cannot use an index:
SELECT * FROM users WHERE name LIKE '%A';
In the second case, SQLite cannot determine a starting point in the index, so it must scan rows.
Additional Conditions
There are several other requirements for index usage:
- The ESCAPE clause must not be used
- The built-in LIKE or GLOB functions must not be overridden
- For GLOB, the column must use the default BINARY collation
- For LIKE:
- If case-sensitive mode is enabled, BINARY collation is required
- If case-insensitive mode is used, NOCASE collation is required
These constraints ensure that the index ordering matches how comparisons are performed.
In the next part, we will look at join table ordering, where SQLite decides the sequence in which tables are processed, which can have a major impact on 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:
| 🇩🇰 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…
SOCIAL SHARE CARD GENERATOR