Index with calculated value not covering?

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Index with calculated value not covering?

wmertens
sqlite> CREATE TABLE "history"("v" INTEGER PRIMARY KEY AUTOINCREMENT,
"type" TEXT, "data" JSON);
sqlite> CREATE INDEX "type_size" on history(type, length(data));
sqlite> explain query plan select type from history group by type;
QUERY PLAN
`--SCAN TABLE history USING COVERING INDEX type_size
sqlite> explain query plan select type, length(data) from history group by
type;
QUERY PLAN
`--SCAN TABLE history USING INDEX type_size

I would expect the latter query to use COVERING index. Is there a way to
hint this?

(I checked the actual opcodes and indeed it reads the table column and runs
length again)

Wout.
_______________________________________________
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: Index with calculated value not covering?

Richard Hipp-3
On 12/12/18, Wout Mertens <[hidden email]> wrote:

> sqlite> CREATE TABLE "history"("v" INTEGER PRIMARY KEY AUTOINCREMENT,
> "type" TEXT, "data" JSON);
> sqlite> CREATE INDEX "type_size" on history(type, length(data));
> sqlite> explain query plan select type from history group by type;
> QUERY PLAN
> `--SCAN TABLE history USING COVERING INDEX type_size
> sqlite> explain query plan select type, length(data) from history group by
> type;
> QUERY PLAN
> `--SCAN TABLE history USING INDEX type_size
>
> I would expect the latter query to use COVERING index. Is there a way to
> hint this?
>

The query planner does not currently implement that optimization.

--
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: Index with calculated value not covering?

wmertens
So the missing optimization is to use a covering index vs just the index,
right? Are there any plans in that direction? Or maybe a way to hint it?

And, to replace this functionality, would it be best to add a column and a
trigger that calculates the length on insert or update? Or are there better
ways

Wout.


On Wed, Dec 12, 2018 at 4:54 PM Richard Hipp <[hidden email]> wrote:

> On 12/12/18, Wout Mertens <[hidden email]> wrote:
> > sqlite> CREATE TABLE "history"("v" INTEGER PRIMARY KEY AUTOINCREMENT,
> > "type" TEXT, "data" JSON);
> > sqlite> CREATE INDEX "type_size" on history(type, length(data));
> > sqlite> explain query plan select type from history group by type;
> > QUERY PLAN
> > `--SCAN TABLE history USING COVERING INDEX type_size
> > sqlite> explain query plan select type, length(data) from history group
> by
> > type;
> > QUERY PLAN
> > `--SCAN TABLE history USING INDEX type_size
> >
> > I would expect the latter query to use COVERING index. Is there a way to
> > hint this?
> >
>
> The query planner does not currently implement that optimization.
>
> --
> D. Richard Hipp
> [hidden email]
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users