Actually running ANALYZE didn't seem to help. There are other partial
indexes I tried and none of them were used:
sqlite> create index "i2" on "t" ("id") where "flags" & 1;
sqlite> explain query plan select * from "t" where "flags" & 1;
0|0|0|SCAN TABLE t
sqlite> create index "i3" on "t" ("id") where "syncFolder" <> 0;
sqlite> explain query plan select * from "t" where "syncFolder" <> 0;
0|0|0|SCAN TABLE t
It is an oversimplification of my actual database, where all of these
queries are used together in one condition
("flags" & 1) AND ("uniqueId" IS NULL OR "syncFolder" <> 0)
that I was hoping to cover with a partial index. Currently I use a bunch of
triggers to basically create the index myself, but I was hoping to replace
it with the partial indexes and avoid having the complex triggers. The
table "t" usually has thousands to millions of rows, while the index itself
should cover only few rows in most cases, typically none.
Best regards,
Filip Navara
On Thu, Jan 29, 2015 at 9:29 PM, Richard Hipp <d...@sqlite.org> wrote:
> 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
>
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
