SQLite Forum

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

My reasonning is that scanning the index is going to involve at worst the same number of rows, and at best a much lower number of rows.

And even when you are scanning the same number of rows, scanning the index would involve less blocks, as the index is going to be smaller than the table (in terms of storage size) in most circumstances.

FWIW I first experimented with that query as a way to reduce block I/O, the table (which I simplified here) has a few other fields, making it about 10x larger in bytes than the index (measured by comparing file sizes after vacuum, once before and once after dropping the index)