SQLite Forum

Query planner fails to use obvious index
>Would not scanning the whole index always be beneficial in that particular case ?

That is not quite correct.

I haven't tested the example at hand in this thread, but just adding a note on the idea that scanning an index is more useful/speedy than scanning a table.

It is not.

A table is (at least in some storage engines, like SQLite) itself simply a covering Index with its INDEXED key being its rowid (unless it's a WITHOUT ROWID table, then the PK is the indexed key).

Scanning the Table *IS* in fact scanning the best possible Index. Any other Index used as a lookup has the immediate deficit that once you find a value in there, it is found with a reference to the main table key (rowid etc.) which in turn requires *another* indexed lookup to find the value row in the main table.

The only saving grace for using another Index, is if using the other index saves so many cycles (thanks to the BTREE look-ups) that it overshadows the cycles wasted on the re-lookup into the main table Index. The Query planner has to cleverly try and calculate and weigh these costs against each other and try to make informed but applicable-in-the-general-sense guesses on when to use the Index and when to go straight for the table lookup. On occasion, a particular combination of schema+data forms a structure that does not play to the Query-planner's guesses and is in fact faster if another method is used.[1]

The best general-case has to be served, but there are tools to help. The ANALYZE method for instance builds a table that looks at the actual shape of the data and better informs the query planner (which is why someone asked if you ran Analyze). There are other tools like query planner hints or forcing the outer loops, which all falls outside of this alreadt-too-long post - so let me end by just saying, no, it is not better to scan the index rather than the table. That is only true if the Index significantly reduces lookup cost, and unless that difference is made obvious to the query planner, it rightfully won't choose the index.

[1] Of course if the Index is itself a covering index and all the referenced fields are contained within the cover, then it becomes a more useful scan.