Which SQLite queries quietly read the whole table

We put 30 query shapes through the SQLite planner. A prefix LIKE scanned a normal index, and an OR we had blamed for full scans was fine on its own.

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.

Read the full report on AISkills402: https://aiskills402.com/blog/sqlite-queries-that-scan