On 3/3/16, David Raymond <David.Raymond at tomtom.com> wrote: > > create table problemTable > ( > pk text primary key, > A text not null, > n int not null > --other fields here > ); > create index typicalFullIndex on problemTable (A); > create index unusedPartial on problemTable (A) where n > 1; > > sqlite> explain query plan select count(*) from problemTable where n > 1; > 0|0|0|SCAN TABLE problemTable >
Workaround: SELECT count(*) FROM problemTable WHERE n>1 AND a>''; The a>'' term will always be true (since column A is of type TEXT) so the extra term in the WHERE clause does not change the outcome. But it does trick the query planner into trying to use an index on A, which gives the result you desire. -- D. Richard Hipp drh at sqlite.org
