SQLite Forum

Query planner fails to use obvious index
> Oh 100% agreed, as I think (or had hoped) my original message conveyed.

And I thought we were arguing a disagreement! If not for my acquired distaste for arguments about arguments, I might try to discern who was most confused.

> not so much regarding the OP's schema ... more about the general misconception that the Index is always better to use

Amusingly, (at least to me), the OP's schema as posted contains two b-trees: (1) a table b-tree keyed by rowid with a "key_id" field tagging along as data; and (2) an index b-tree keyed by the key_id values, with a rowid tagging along in case the index becomes useful for quickly reaching a table row. Those b-trees contain the exact same data, and choosing between them for a query involving both fields, where they both are subject to test that does not strictly match the b-tree ordering, was always going to be a close call. Moreover, being close, it's a call I would be sorry to see over-optimized in a system where 'Lite' is a virtue.

**Warning: Off-topic matter follows.**

> saying "Indexes" as opposed to "Indices"

When I had some responsibility for raising children, I always hesitated to "correct" their application of usual language rules to cases where, for reasons better lost in history, exceptions are made by more learned folks. Language changes, and if it becomes more regular in the process, I am glad to see that.