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.

I was thinking the same thing, but after reading Keith's post closer I think his point is that the best case for the _table_ scan makes it a very attractive option.

Just to remind myself of the query: select min(id) from test where key_id >= 1;

If you decide use the index to look for key_id >= 1 and then find the minimum id amongst those rows, you are obliged to search the whole index to satisfy the query (well, you can skip the key_id < 1 portion).

OTOH, if you start scanning the table in order of the id column and check for a key_id >= 1, you can abort the scan as soon as you find a matching row because you've found the correct answer. So at best, the table scan actually needs to read less rows.

That tradeoff gets less desirable as the table grows, and obviously in your case the query planner hasn't made a great choice (or you wouldn't be here). So there may be potential here for some heuristic improvements, or it may be that sqlite is able to make a better decision if it has more information about the data (via ANALYZE as Keith suggested).