On 1/26/18, Deon Brewis <d...@mylio.com> wrote: > 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));
I think you mistyped something on that last line, and as a consequence, I have having difficulty decoding your problem. > > 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 > -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
