SQLite Forum

Query planner fails to use obvious index
Login
>  But if we substitute "a table's information" with "that subset of the table's information which is necessarily contained in an index"...

Oh 100% agreed, as I think (or had hoped) my original message conveyed. If the looked-up Index itself already contains all the fields required in the look-up, filter, data required for the query, etc. then there is no need to read the table at all, and in that case the index always wins. (The very reason why covering indexes are useful).

I'll admit that my post was not so much regarding the OP's schema, I think I mentioned it at the top, but more about the general misconception that the Index is always better to use. It isn't and there's a reason the QP favours a table-scan over an index sometimes, which may help explain on occasion seeing a table-scan proposed in a query plan when one intuitively feels it shouldn't.

This may well not apply to the OP's case specifically.


PS: I still feel so dirty saying "Indexes" as opposed to "Indices"... but it's the way of the sea now.