Which SQLite queries quietly read the whole table

More of them than the schema suggests. An index on a table does not mean a given query uses it, and when it does not, SQLite walks every row to answer, however few rows come back. In our run the usual suspects did exactly that: a GROUP BY that picks the latest row per key, a NOT IN on the leading column of an index, an indexed column passed through a function. Two results surprised us. A plain prefix search, LIKE 'anna%', scanned a normal index too. And an OR that our own notes listed as an index killer turned out to be harmless when it stands alone.

This matters most on databases that charge by rows read, such as Cloudflare D1 and Turso. There a scan is not only slow, it is billed, and the bill rises as rows pile up even though nobody touched the code.

Method

We wrote the schemas and queries that kept showing up in our own projects and in the reviews we do: price histories, event logs, job queues, order tables, posts waiting to be shared. We asked SQLite 3.53 for the plan of each one with EXPLAIN QUERY PLAN, through the built-in node:sqlite module, on empty tables and without running ANALYZE. With no statistics the planner decides from the schema alone, which is also what it does on a fresh database.

The plan says either SEARCH (the engine jumps into an index and reads a range) or SCAN (it walks through all of a table, or all of an index). A third phrase, USE TEMP B-TREE, tells you it gathered every matching row and sorted them before LIMIT could cut anything. We recorded 18 query shapes on the schema as written, and 12 fixes on a second database that holds the same schema plus the new indexes. Keeping the two apart mattered: in a first draft we added the fix indexes to the same database, and one of the "bad" queries quietly switched to a fixed plan, which would have hidden the problem we wanted to show.

Results

The prefix LIKE was the finding we did not expect. SQLite's LIKE ignores letter case by default, and a normal SQLite index sorts by exact bytes, so the planner cannot turn the pattern into a range and gives up. The SQLite optimizer overview lists the conditions: with case-insensitive LIKE the column has to be indexed with the NOCASE collation. GLOB 'anna*', which is case-sensitive, used the normal index at once, and so did the explicit range email >= 'anna' AND email < 'annb'.

The OR result corrected one of our own notes. On its own, social_posted_at IS NULL OR social_posted_at = 0 produced a MULTI-INDEX OR: two separate index searches, merged. The trouble starts when the same OR shares a WHERE with a second condition that the index also covers. Given the two-column index (status, social_posted_at), the query status = 'published' AND (social_posted_at IS NULL OR social_posted_at = 0) searched by status alone and then read every published post. Once the flag column was declared NOT NULL DEFAULT 0, the query could ask for = 0 only, and the search used both columns again.

One thing that looks expensive was cheap, and one that looks cheap was not. Asking for the largest created_at across all rows, when that column has its own index, became a single lookup at the end of the index. COUNT(*), on the other hand, walked an entire index, so a page total computed on every load pays for every row, every time.

One more limit belongs on the list because it breaks code that worked at launch. D1 will not run a query that binds over 100 parameters, per its published limits. An ORM binds one parameter per element of an IN list, and inserting many rows in one go binds one per column of every row, so a list that grows with your data fails on its own one day.

A review skill built on this

We turned these patterns into a skill for reviewing SQL on D1, SQLite and Turso and tested it on 12 small cases: 11 with one planted problem each and one clean schema. Claude Sonnet named every planted problem and said there was nothing to fix on the clean one. Claude Haiku found all 11 problems too, but for the schema with nothing wrong in it, Haiku made up a problem and proposed DELETE ... LIMIT 200, which stock SQLite rejects as a syntax error. Our planner run confirms that error.

Our first scoring was wrong in two places, and both mistakes were ours. Sonnet picked a valid fix that our pattern did not accept: once a small table kept up to date by the writer, once day boundaries computed in SQL. We checked both with the planner, widened the two patterns, and rescored the same stored answers without running the models again.

What we did not measure

• Real data. Every plan here comes from empty tables without ANALYZE. With statistics SQLite may choose differently, for better or worse, and it can prefer a scan on a small table, where a scan is fine.
• Billing. We read plans, not invoices. We did not measure rows_read on a live D1 database for these shapes, so the step from "SCAN" to a bill is reasoning, not a number we took.
• Other engines. PostgreSQL and MySQL have their own planners. Some patterns carry over, but nothing here was tested on them.
• The review test is small. Twelve cases, written by us, one run per model. A different schema, or a second run, could find a miss we did not see.
• One SQLite version. We used 3.53; the planner changes between releases, and the hosted services may run a different one.