Filip,
I don't suppose it would fit your needs to index on the column you're
comparing rather than on "id" would it? That would cause the query
planner to use your indexes, I believe.
create index "i1" on "t" (uniqueID) where UniqueId ==55;
Alternatively, you can create an "unnecessary" compound index such as
create index "i1" on "t" (uniqueID, id) where UniqueId ==55;
Even though the documentation seems to explicitly allow creating partial
indexes on columns other than the indexed column:
"The columns referenced in the WHERE clause of a partial index can be
any of the columns in the table, not just columns that happen to be indexed.
"
(It may well be that I'm missing something myself.)
I tried experimenting with 3.8.8, removing references to NULL and using "==
55" as a simple test condition; results follow. Your problem did not go
away when I got rid of NULL checking, but creation of a partial index on
column "id" which tests instead on column "uniqueID" DID cause the index to
go unused. (Whether sqlite is working properly or not -- I'll leave that
for others to say.)
sqlite>
sqlite> /* Test comparing UniqueID to 55 rather than to null */
sqlite> /* Note that index is on column "id" and not "uniqueID" */
sqlite> /* EXPLAIN Q. Plan shows partial index would not be invoked*/
sqlite> drop table if exists t;
sqlite> create table "t" ("id" INTEGER NOT NULL PRIMARY KEY, "flags"
INTEGER,
...> "uniqueId", "syncFolder" INTEGER);
sqlite> ANALYZE;
sqlite> create index "i1" on "t" (uniqueID, id) where UniqueId ==55;
sqlite> ANALYZE;
sqlite> explain query plan select * from "t" where UniqueID ==55;
0|0|0|SEARCH TABLE t USING INDEX i1 (uniqueId=?)
sqlite>
sqlite>
sqlite>
sqlite> /* Try to see if it fails only on INTEGER PRIMARY KEY.*/
sqlite> /*Don't use variable "id" at all, use "flags" instead.*/
sqlite> /* Query planner would still not use index */
sqlite> drop table if exists t;
sqlite> create table "t" ("id" INTEGER NOT NULL PRIMARY KEY, "flags"
INTEGER,
...> "uniqueId", "syncFolder" INTEGER);
sqlite>
sqlite> ANALYZE;
sqlite> create index "i1" on "t" (flags) where UniqueId ==55;
sqlite> ANALYZE;
sqlite> explain query plan select * from "t" where UniqueID ==55;
0|0|0|SCAN TABLE t
sqlite>
sqlite>
sqlite>
sqlite>
sqlite> /* Try making index compound, with "id" secondary*/
sqlite> /* This DOES provoke the query planner into using the index*/
sqlite> /* (at the expense of some efficiency, I suppose)*/
sqlite> drop table if exists t;
sqlite> create table "t" ("id" INTEGER NOT NULL PRIMARY KEY, "flags"
INTEGER,
...> "uniqueId", "syncFolder" INTEGER);
sqlite> ANALYZE;
sqlite> create index "i1" on "t" (uniqueID, id) where UniqueId ==55;
sqlite> ANALYZE;
sqlite> explain query plan select * from "t" where UniqueID ==55;
0|0|0|SEARCH TABLE t USING INDEX i1 (uniqueId=?)
sqlite>
sqlite>
sqlite> /* Of course it also works if you don't index on column "id" */
sqlite> drop table if exists t;
sqlite> create table "t" ("id" INTEGER NOT NULL PRIMARY KEY, "flags"
INTEGER,
...> "uniqueId", "syncFolder" INTEGER);
sqlite>
sqlite> create index "i1" on "t" (uniqueId) where UniqueId ==55;
sqlite> explain query plan select * from "t" where UniqueID ==55;
0|0|0|SEARCH TABLE t USING INDEX i1 (uniqueId=?)
sqlite>
sqlite> select sqlite_version();
3.8.8
sqlite> pragma compile_options;
ENABLE_FTS3
ENABLE_RTREE
SYSTEM_MALLOC
THREADSAFE=0
sqlite>
*I used windoze pre-compiled exe withOUT STAT4, btw.*
