Sorry, wrong table, should be: CREATE INDEX FooBar ON Foo(Bar(GuidId));
It's an expression-based index. But I doubt that has anything to do with it. Can probably just be: CREATE INDEX FooBar ON Foo(GuidId); - Deon -----Original Message----- From: sqlite-users [mailto:[email protected]] On Behalf Of Richard Hipp Sent: Friday, January 26, 2018 1:23 PM To: SQLite mailing list <[email protected]> Subject: Re: [sqlite] Convincing SQLITE to use alternate index for count(*) On 1/26/18, Deon Brewis <[email protected]> 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 > [email protected] > https://eur01.safelinks.protection.outlook.com/?url=http%3A%2F%2Fmaili > nglists.sqlite.org%2Fcgi-bin%2Fmailman%2Flistinfo%2Fsqlite-users&data= > 02%7C01%7C%7C72a5754a223d4a7add1e08d565030b31%7C84df9e7fe9f640afb435aa > aaaaaaaaaa%7C1%7C0%7C636525986095881144&sdata=SOWM0RP1KQbSzYC4LPl8Trku > MRE9ZGtc8jcijYZpVSo%3D&reserved=0 > -- D. Richard Hipp [email protected] _______________________________________________ sqlite-users mailing list [email protected] https://eur01.safelinks.protection.outlook.com/?url=http%3A%2F%2Fmailinglists.sqlite.org%2Fcgi-bin%2Fmailman%2Flistinfo%2Fsqlite-users&data=02%7C01%7C%7C72a5754a223d4a7add1e08d565030b31%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C636525986095881144&sdata=SOWM0RP1KQbSzYC4LPl8TrkuMRE9ZGtc8jcijYZpVSo%3D&reserved=0 _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
