skip to content

In SQLite, a join of contacts c and call_log l shows `SEARCH l USING AUTOMATIC COVERING INDEX (phone=?)`; what does that mean, and what should you change?

level: seniorimportance: nice to knowfreq 28%

answer

  1. built per statement, then thrown away
  2. cheaper than nested full scans
  3. SQLITE_WARNING_AUTOINDEX in the log
  4. create the persistent index

basics

~20 s

SQLite found no index on call_log.phone, so for this one statement it builds a transient index, uses it for the join, and discards it. The plan works but pays the build cost on every run; a persistent index on call_log(phone) is the fix.

solid answer

~40 s

An **automatic index**, also called a query-time index, exists only while one statement runs, is never written to the file and is visible to one connection. SQLite may build one when no index helps a join and it expects the inner table to be probed more than about log N times, since building costs O(N log N) and each probe is then cheap. Without ANALYZE it assumes tables hold about a million rows, so it reaches for this often. It is SQLite's stand-in for a hash join. The fix is `CREATE INDEX call_log_phone ON call_log(phone)`, after which the line reads `SEARCH l USING INDEX call_log_phone (phone=?)`. Turning `PRAGMA automatic_index` off is not a fix: the inner table would then be scanned once per outer row.

code

console · 9 lines
console
sqlite> EXPLAIN QUERY PLAN SELECT c.surname, l.started_at FROM contacts c JOIN call_log l ON l.phone = c.phone;
QUERY PLAN
|--SCAN c
`--SEARCH l USING AUTOMATIC COVERING INDEX (phone=?)
sqlite> CREATE INDEX call_log_phone ON call_log(phone);
sqlite> EXPLAIN QUERY PLAN SELECT c.surname, l.started_at FROM contacts c JOIN call_log l ON l.phone = c.phone;
QUERY PLAN
|--SCAN c
`--SEARCH l USING INDEX call_log_phone (phone=?)

go deeper

for a junior

Know that AUTOMATIC in a plan means SQLite built a temporary index for that one statement because a useful permanent index was missing.

for a middle

Explain the cost logic, a build of about N log N weighed against repeated full scans, and why the planner's one-million-row default guess makes it common.

for a senior

Find automatic indexes in production through SQLITE_WARNING_AUTOINDEX or the AUTOINDEX statement counter, add the persistent index, and confirm the plan changed.

for a principal

Decide how the team surfaces SQLite's own warnings, such as routing the error log into crash and performance reporting, so missing indexes are caught before users notice.

## What an automatic index is When a statement joins two tables and no index exists on the inner table's join column, the simplest plan is a nested loop: for every outer row, scan the whole inner table. For two tables of N rows that is O(N*N) work. SQLite's alternative is an **automatic index** (also called a query-time index): - It is built while the statement runs, before the first probe, from the inner table and keyed on the join column. - It lasts only for that single statement, is never written to disk and is visible only to the connection running it. - Building costs about O(N log N). SQLite builds one only when it expects to look rows up more than about log N times during the statement, so the build pays for itself. The documentation points out that a query using an automatic index is essentially a **hash join** built on a B-tree, which SQLite already has, instead of a separate hash table that would enlarge the library. ## Reading the plan ```sql SELECT c.surname, l.started_at FROM contacts c JOIN call_log l ON l.phone = c.phone; ``` With no index on `call_log.phone`, SQLite 3.53 prints: ```text QUERY PLAN |--SCAN c `--SEARCH l USING AUTOMATIC COVERING INDEX (phone=?) ``` | Line | Meaning | |---|---| | `SCAN c` | Outer loop: every contact is read once | | `SEARCH l USING AUTOMATIC COVERING INDEX (phone=?)` | Inner loop: a transient index on `phone` (holding the columns the query needs) is probed per contact | | `... AUTOMATIC PARTIAL COVERING INDEX ...` | The same idea, but the transient index holds only the rows that pass other WHERE conditions on that table | After `ANALYZE` the planner may swap the loops and build the automatic index on `contacts` instead, so the table named on that line is whichever one it judged cheaper to index. Three related details show up around these lines: - A line such as `BLOOM FILTER ON l (phone=?)` can appear just before the automatic-index search (SQLite 3.38.0 and later). It is a compact membership test SQLite builds alongside, so outer rows that cannot have a match skip the probe. - A join written with the `CROSS JOIN` keyword keeps the table order you wrote, because SQLite never reorders a CROSS JOIN. The planner then cannot avoid the missing index by swapping loops, so the inner table is the one that gets the transient index. - The documentation also describes automatic indexes for correlated subqueries, but whether one is built depends on the cost estimate: in 3.53 a simple scalar subquery on an unindexed column can show a plain `SCAN` instead. Read the plan rather than assume. ## Why it shows up so often on small databases When it weighs an automatic index without `ANALYZE` statistics, SQLite guesses that a table holds about **one million rows**. With that guess, almost any unindexed join looks expensive enough to justify a transient index, even on a phone database of a few thousand rows. That is usually still the better plan, but the index is rebuilt on every execution of the statement. A screen that runs this query each time it opens pays the build cost each time. ## Finding them in a running app - Since 3.8.0, every time a statement that uses an automatic index is prepared, SQLite sends an `SQLITE_WARNING_AUTOINDEX` message to its **error log**, the callback an app installs through `sqlite3_config(SQLITE_CONFIG_LOG, ...)`. The documentation says developers should use these warnings to spot missing persistent indexes. - After a statement runs, `sqlite3_stmt_status()` with `SQLITE_STMTSTATUS_AUTOINDEX` reports how many rows were inserted into automatic indexes. A non-zero value is another sign of a missing index. - Do not confuse these with indexes named `sqlite_autoindex_<table>_N`. Those are permanent internal indexes that implement PRIMARY KEY and UNIQUE constraints, are stored in the file and are shared by all connections. The similar name is a historical accident. ## What to change 1. Create the persistent index the planner was improvising: `CREATE INDEX call_log_phone ON call_log(phone);`. 2. Run `PRAGMA optimize;` so statistics cover the new index. 3. Re-read the plan: the inner line should now be `SEARCH l USING INDEX call_log_phone (phone=?)`. Two tempting alternatives are worse: - `PRAGMA automatic_index = OFF` (it is on by default, since 3.7.17) does not add an index. It removes the improvised one, and the plan falls back to `SCAN l` nested inside `SCAN c`, the O(N*M) case. Builds can also change the default with `SQLITE_DEFAULT_AUTOMATIC_INDEX` or remove the feature with `SQLITE_OMIT_AUTOMATIC_INDEX`. - `INDEXED BY` cannot name an automatic index, and SQLite's documentation reserves it for locking down a finished design, not for tuning.

  • In SQLite, could you pin this join to an index with INDEXED BY instead of trusting the planner?
    `INDEXED BY` is a requirement, not a hint: if the planner cannot use the named index, preparing the statement fails with `no query solution`. The documentation says it exists to make an unexpected plan change fail loudly in regression testing, to be added only when locking down a finished design, and calls it a last resort. A prefix `+` on a WHERE term is the gentler way to stop that term from using an index.
  • In SQLite, what does `NOT INDEXED` on a table in the FROM clause do?
    It forbids the planner from using any index on that table, including the internal indexes behind PRIMARY KEY and UNIQUE constraints, although rowid lookups remain possible. It is useful for testing how a query behaves without its indexes, or for ruling out an index you suspect is chosen badly.

A clerk asked to match a stack of 5,000 contact cards against an unsorted call ledger first copies the ledger onto sorted index cards, uses them for this one job and bins them. Doing that daily is wasteful; keeping a permanent card index is the real fix.

  • An automatic index is saved in the file and reused by later queries.
  • Indexes named sqlite_autoindex_contacts_1 are the same as automatic indexes.
  • Setting PRAGMA automatic_index = OFF makes the join faster.
  • INDEXED BY is a hint the planner may quietly ignore.
  • SQLite only builds automatic indexes after ANALYZE has run.