Which index should SQLite use?

Source: Dev.to

Example: Choosing Between Two Indexes

Consider a table with multiple indexes:

-- Example table definition
CREATE TABLE table1 (
    id INTEGER PRIMARY KEY,
    x  INTEGER,
    y  INTEGER,
    z  TEXT
);

-- Indexes on separate columns
CREATE INDEX i1 ON table1(x);
CREATE INDEX i2 ON table1(y);

Now look at this query:

SELECT z FROM table1 WHERE x = 5 AND y = 6;

SQLite has two choices:

1. Use index i1 to find rows where x = 5, then filter y = 6.
2. Use index i2 to find rows where y = 6, then filter x = 5.

Both approaches are valid, but they may have very different costs. 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 the 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, allowing SQLite to estimate which index will reduce the result set the most. The index expected to return fewer rows is usually preferred.

Overriding the Choice

Sometimes you may want to guide SQLite away from a particular index. SQLite provides a subtle mechanism using the unary + operator:

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. It’s a lightweight way to influence the optimizer without changing query semantics.

Balancing WHERE and ORDER BY

Index selection is not only about filtering rows; SQLite also considers sorting requirements.

Example with ORDER BY

SELECT * FROM table1 WHERE x = 5 ORDER BY y;

Now SQLite faces a trade‑off:

• Use an index on x to filter efficiently, then sort the result set on y.
• Use an index on y to satisfy the ORDER BY clause directly, possibly at the cost of a less selective filter.

SQLite evaluates both options and chooses the one that results in the fastest overall execution. Sometimes SQLite may sacrifice a slightly less efficient filter to avoid an expensive sort operation. If no suitable index can satisfy the ORDER BY clause, SQLite must sort the results manually using a temporary sorter (a transient in‑memory structure). The process looks roughly 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

While this approach works, it incurs additional memory usage, extra processing for sorting, and potential performance overhead for large datasets. Therefore, SQLite always tries to use an index for ORDER BY whenever possible.

How the Decisions Interact

• 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 can affect:

• Number of rows scanned
• Need for sorting
• Overall execution time

Understanding how SQLite makes these decisions helps you design better indexes and write queries that align with the optimizer.

The next part will explore GROUP BY and MIN/MAX optimizations, where SQLite applies additional strategies to aggregate and summarize data efficiently.