On 1/29/15, Filip Navara <filip.nav...@gmail.com> wrote:
> Hello,
>
> I tried really hard to get partial indexes working, but SQLite refuses to
> use them:
>
>> create table "t" ("id" INTEGER NOT NULL PRIMARY KEY, "flags" INTEGER,
> "uniqueId", "syncFolder" INTEGER);
>> create index "i1" on "t" ("id") where "uniqueId" IS NULL;
>> explain query plan select * from "t" where "uniqueId" IS NULL;
> 0|0|0|SCAN TABLE t
>> explain query plan select * from "t" indexed by "i1" where "uniqueId" IS
> NULL;
> Error: no query solution
>
> Any advice what am I doing wrong?
>I seem to recall adding a rule to the query planner that refuses to use an IS NULL constraint with an index unless you have first run ANALYZE. It might also require compiling with SQLITE_ENABLE_STAT4. The usual case with partial indexes is WHERE field IS NOT NULL --- with a "NOT". You are taking partial indexes into an area for which they were not optimized. -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
