I have a table with 2 indexes: CREATE TABLE Foo ( Id Integer, GuidId blob PRIMARY KEY ) WITHOUT ROWID;
CREATE UNIQUE INDEX FooId ON Foo(Id); CREATE INDEX FooBar ON Resource(Bar(GuidId)); When I do: SELECT COUNT(*) FROM Foo; The query plan always uses the FooBar index. But the FooBar index is physically bigger on disk than the FooId index. I'd like it to count FooId instead. How can I coerce SQLITE to count FooId instead of FooBar? I've tried the following: SELECT COUNT(*) FROM Foo INDEXED BY FooId; -- ignores the INDEXED BY clause SELECT COUNT(Id) FROM Foo INDEXED BY FooId; -- uses the right index, but filters nulls, so slower SELECT COUNT(1) FROM Foo INDEXED BY FooId; -- uses the right index, but slower for some unknown reason?? It seems the "COUNT(*) FROM Foo INDEXED BY FooId" fails to use FooId, because FooId is a UNIQUE index. If I make FooId not unique, it correctly uses it. But surely UNIQUE shouldn't make any difference to this query? Either way, that doesn't matter specifically, but I can't seem to find the syntax to make it use the smaller index. Any ideas? - Deon _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users