SQLite Forum

Hi,

I have found a case in SQLite 3.32.2 where the query planner does not seem to use an obvious index when a comparison operator is used, minimal case below

    create table test (id integer primary key, key_id integer);
    create index test_key_idx on test(key_id);

with that schema

    explain query plan
    select min(id) from test where key_id >= 1

returns "SEARCH TABLE test", which does not use the index (and is very slow in my real world case), while the following "workaround" query 

    explain query plan
    select min(id) from test where key_id between 1 and 999999

returns "SEARCH TABLE test USING COVERING INDEX test_key_idx (key_id>? AND key_id<?)", which uses the index and is fast.

And even the brute force workaround query is fast

    select min(id) from test where key_id between 1 and (select max(key_id) from test)

as it uses the index in both the main and the sub-select.

Thanks