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
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
