Convincing SQLITE to use alternate index for count(*)

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

Convincing SQLITE to use alternate index for count(*)

deon
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Convincing SQLITE to use alternate index for count(*)

Richard Hipp-3
On 1/26/18, Deon Brewis <[hidden email]> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Convincing SQLITE to use alternate index for count(*)

Deon Brewis
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:[hidden email]] On Behalf Of Richard Hipp
Sent: Friday, January 26, 2018 1:23 PM
To: SQLite mailing list <[hidden email]>
Subject: Re: [sqlite] Convincing SQLITE to use alternate index for count(*)

On 1/26/18, Deon Brewis <[hidden email]> 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
> [hidden email]
> 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
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Convincing SQLITE to use alternate index for count(*)

Richard Hipp-3
On 1/28/18, Deon Brewis <[hidden email]> wrote:
> 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);

Can you send the output of sqlite3_analyzer for your database so that
we can compare the actual table and index sizes?

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users