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.
Even when indexes exist, choosing the wrong one can slow down a query significantly.
The optimizer’s job here is not just to use an index, but to use the right index.
One Table, One Index (Mostly)
For each table in a query, SQLite can typically use only one index.
There is one exception.
In OR-based queries, SQLite may use multiple indexes, but in most cases, it selects a single index per table.
Because of this limitation, index selection becomes a critical decision.
SQLite tries to ensure that at least one useful index is applied to each table whenever possible.
When Multiple Indexes Exist
Consider a table with multiple indexes:
```sql id="9j9h8h"
CREATE TABLE table1(x, y, z);
CREATE INDEX i1 ON table1(x);
CREATE INDEX i2 ON table1(y);
Now look at this query:
```sql id="d1km7x"
SELECT z FROM table1 WHERE x = 5 AND y = 6;
SQLite has two choices:
- Use index
i1to find rows wherex = 5, then filtery = 6 - Use index
i2to find rows wherey = 6, then filterx = 5
Both approaches are valid, but they may have very different costs.
How SQLite Chooses the Best Index
SQLite estimates how much work each option will require and chooses the one with the lowest cost.
The decision is based on heuristics such as:
- How many rows are expected to match
- How selective the index is
- How much filtering is needed after lookup
If statistical data is available, SQLite makes better decisions.
This is where the sqlite_stat1 table comes into play.
It stores information about how many rows are typically associated with a given column value.
Using this data, SQLite can estimate which index will reduce the result set the most.
The index that is expected to return fewer rows is usually preferred.
Forcing SQLite to Ignore an Index
Sometimes, you may want to override SQLite’s choice.
SQLite provides a subtle mechanism for this using the unary + operator.
Example:
```sql id="u4cmnf"
SELECT z FROM table1 WHERE +x = 5 AND y = 6;
The `+` operator does nothing functionally, but it prevents SQLite from using the index on column `x`.
This forces the optimizer to consider other indexes, such as `i2` on column `y`.
This is a lightweight way to guide the optimizer without changing query semantics.
## Balancing WHERE and ORDER BY
Index selection is not only about filtering rows.
SQLite also considers sorting requirements.
For example:
```sql id="z2t3r8"
SELECT * FROM table1 WHERE x = 5 ORDER BY y;
Now SQLite has a trade-off:
- Use an index on
xto filter efficiently - Use an index on
yto avoid sorting
SQLite evaluates both options and chooses the one that results in the fastest overall execution.
This means sometimes SQLite may sacrifice a slightly less efficient filter to avoid an expensive sort operation.
When Sorting Cannot Use an Index
If no suitable index can satisfy the ORDER BY clause, SQLite must sort the results manually.
This is done using a temporary sorter, which behaves like a transient index.
The process looks like this:
open sorter
where-begin
extract required columns
build a record
generate sort key
insert into sorter
where-end
sort
for each sorted entry
extract data
return result
close sorter
This approach works, but it comes with a cost.
- Additional memory usage
- Extra processing for sorting
- Potential performance overhead for large datasets
That is why SQLite always tries to use an index for ORDER BY whenever possible.
Why Index Selection Is So Important
At this stage, multiple layers of optimization come together:
- WHERE clause determines candidate indexes
- Join ordering decides when a table is accessed
- Index selection decides how it is accessed
- ORDER BY may influence which index is preferred
A single decision here can affect:
- Number of rows scanned
- Need for sorting
- Overall execution time
Wrapping Up
Index selection is one of the most critical decisions in SQLite’s optimizer.
Even when multiple indexes are available, SQLite carefully evaluates which one minimizes total work.
It also balances filtering efficiency with sorting requirements to produce the fastest possible execution plan.
Understanding how SQLite makes this decision helps you design better indexes and write queries that align with the optimizer.
In the next part, we will explore GROUP BY and MIN/MAX optimizations, where SQLite applies additional strategies to aggregate and summarize data efficiently.
*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…
Top comments (0)