In SQLite, a partial index on live contacts and an expression index on lower(surname) are both ignored; what matching rules must the query satisfy?
answer
- no algebra in the planner
- query must imply the index WHERE
- same term, or IS NOT NULL
- bound values checked since 3.20.0
basics
~20 sSQLite uses a partial index only when a query WHERE term exactly matches an index WHERE term, or implies its IS NOT NULL. An expression index needs the indexed expression, as written, in the WHERE or ORDER BY. SQLite does no algebra.
solid answer
~40 sFor `CREATE INDEX contacts_live_surname ON contacts(surname) WHERE deleted = 0`, the query's WHERE must imply the index's WHERE, and SQLite proves that with simple rules. Either a term of the query's AND-connected WHERE appears exactly in the index's OR-connected WHERE, so `AND deleted = 0` works but `AND NOT deleted` or `deleted < 1` do not. Or the index says `c IS NOT NULL` and the query compares `c` with `=`, `<`, `>`, `<=`, `>=`, `<>`, `IN`, `LIKE` or `GLOB`. Since 3.20.0 SQLite also checks a bound parameter's value, so `deleted = ?` bound to 0 can use the index. For `ON contacts(lower(surname))` the query must contain `lower(surname)` itself, in the WHERE or ORDER BY; `upper(surname)` will not match.
code
console · 8 linessqlite> CREATE INDEX contacts_live_surname ON contacts(surname) WHERE deleted = 0;
sqlite> EXPLAIN QUERY PLAN SELECT id FROM contacts WHERE surname = 'Novak' AND NOT deleted;
QUERY PLAN
`--SCAN contacts
sqlite> .parameter set ?2 0
sqlite> EXPLAIN QUERY PLAN SELECT id FROM contacts WHERE surname = 'Novak' AND deleted = ?2;
QUERY PLAN
`--SEARCH contacts USING COVERING INDEX contacts_live_surname (surname=?)go deeper
Recall that SQLite matches partial and expression indexes by the exact terms and expressions written, not by what they mean.
State the two partial-index rules, the IS NOT NULL case and the deterministic-function restriction, and show a query rewrite that makes the index usable.
Debug an ignored index by comparing query text with the index definition, binding the real parameter values before reading the plan, and checking for the planner stability guarantee.
Set conventions so query text and index definitions stay in step, such as one canonical predicate per partial index and review of index changes alongside the queries they serve.
## Two indexes, two literal-minded rules A **partial index** has a WHERE clause and holds entries only for rows where it is true. An **expression index** indexes the result of an expression instead of a bare column. Why each one is useful, and why any optimizer must prove that a query is allowed to use them, is covered by the general material on partial and expression indexes. What is specific to SQLite is how little proving it does: its planner matches text-like structure, not meaning. ```sql CREATE INDEX contacts_live_surname ON contacts(surname) WHERE deleted = 0; CREATE INDEX contacts_surname_lower ON contacts(lower(surname)); ``` ## When SQLite will use a partial index Let X be the index's WHERE and W the query's WHERE. The index is usable only if W implies X, and SQLite's documentation describes two simple rules for recognising that: 1. **A shared term.** If W is a set of AND-connected terms and X a set of OR-connected terms, any term of W that appears exactly as a term of X makes the index usable. 2. **IS NOT NULL.** If X contains `c IS NOT NULL` and W compares `c` with `=`, `<`, `>`, `<=`, `>=`, `<>`, `IN`, `LIKE` or `GLOB`, those match, because such comparisons are true only for non-NULL values. A comparison with `IS` does not count. Two later additions widen this slightly: since 3.9.0 an index WHERE made of AND-connected terms is usable when the query matches each of them, and since 3.20.0 bound values are checked (below). Everything else is treated as not implied. SQLite does no algebra: | `SELECT id FROM contacts` with | Plan in SQLite 3.53 | |---|---| | `WHERE surname = 'Novak' AND deleted = 0` | `SEARCH ... USING COVERING INDEX contacts_live_surname (surname=?)` | | `WHERE surname = 'Novak' AND 0 = deleted` | uses the index: a reversed term in the query still matches | | `WHERE surname = 'Novak' AND NOT deleted` | `SCAN contacts` | | `WHERE surname = 'Novak' AND deleted < 1` | `SCAN contacts` | | `WHERE surname = 'Novak'` | `SCAN contacts`: deleted rows could match but are not in the index | The reversal works in one direction only: a term written `0 = deleted` inside the index definition never matches anything. Note too that the first row reports a **covering** index even though `deleted` is not an indexed column: the WHERE pins it to 0, so SQLite needs nothing from the table. ## Bound parameters - An index's own WHERE may not contain bound parameters, subqueries, references to other tables or non-deterministic functions. - Since 3.20.0 the planner looks at the value bound to a parameter when it decides whether a partial index is usable. `WHERE surname = ?1 AND deleted = ?2` with 0 bound to `?2` uses the index; with 1 bound it scans. SQLite marks such a statement to be prepared again when a different value is bound. - Turning on the **query planner stability guarantee** (`SQLITE_DBCONFIG_ENABLE_QPSG`) disables optimizations that look at bound values, so this matching stops. - For this query, a plan taken in the shell with the parameter unbound shows `SCAN`. Bind the value first (`.parameter set ?2 0` in the `sqlite3` shell) to see the plan the app will get. ## When SQLite will use an expression index - The query must contain **the same expression** as the `CREATE INDEX`, in its WHERE clause or its ORDER BY. Differences such as white space are tolerated; mathematically equal rewrites are not. An index on `x+y` does not serve `WHERE y+x = 22`. - `WHERE lower(surname) = 'novak'` uses `contacts_surname_lower` and the plan shows `(<expr>=?)`. `WHERE upper(surname) = 'NOVAK'` scans. - `ORDER BY lower(surname)` can read the index in order instead of sorting. The expression itself is restricted: - It may refer only to columns of the indexed table. - It may call only **deterministic** functions; `random()` fails with `non-deterministic functions prohibited in index expressions`. An application-defined function counts as deterministic only if it was registered with `SQLITE_DETERMINISTIC`. - It may not contain a subquery, and expressions are allowed only in `CREATE INDEX`, not in a table's UNIQUE or PRIMARY KEY constraint. ## Version and case-folding traps - Partial indexes need 3.8.0 and expression indexes 3.9.0. A database that contains one cannot be read by an older library, a concern only when a file travels to very old software. - The built-in `lower()` folds ASCII letters only, so `lower(surname)` does not match accented capitals unless the ICU extension is loaded. An index declared `surname COLLATE NOCASE` is the other built-in route, and it is used by comparisons that also use `NOCASE`; it shares the ASCII-only limit. - When a plan ignores one of these indexes, first compare the query's text with the index definition term by term, then check what value is bound.
- In SQLite, can one partial index serve queries on either of two conditions, such as `WHERE a = 5 OR b = 6`?Yes, when the query names one of them. With `CREATE INDEX ex1 ON tab1(a, b) WHERE a = 5 OR b = 6`, a query `WHERE b = 6 AND a = 7` can use it, because its AND-connected term `b = 6` appears among the index's OR-connected terms. Terms in the index should be OR-connected and terms in the query AND-connected.
- In SQLite, what happens if an index on lower(surname) uses an app-defined normalisation function instead?SQLite treats application-defined SQL functions as non-deterministic unless they were registered with the `SQLITE_DETERMINISTIC` flag, and CREATE INDEX refuses non-deterministic functions. Register it as deterministic only if it truly returns the same output for the same input, because the index stores its results and would be wrong if they changed.
- SQLite will use a partial index on deleted = 0 for a query saying NOT deleted.
- A partial index's WHERE clause can include a ? parameter.
- SQLite never lets a bound parameter qualify a query for a partial index.
- An index on lower(surname) also serves WHERE upper(surname) = 'NOVAK'.
- Any built-in function, including random(), can appear in an index expression.
Assumes SQLite 3.53; since 3.20.0 the planner checks bound parameter values when deciding whether a partial index is usable.